VBScript placing string values in incorrect cells in Excel workbook

[#1]

Good Day

I require help with a macro that I've setup via QlikView's VBScript functionality. I got the original code from Stefan Walther's excellent blog QlikTip #32: Exporting multiple QV objects to a single Excel document.

Problem Statement

After extending the code to loop through Stefan's code depending on the type of report that needs to be generated and then loops through a list of names and generates the report based on the data tied to that person.The first person's report works perfectly, but I ran into problems with the second and subsequent reports having issues where string values being pasted to multiple rows (even though only one specific cell is specified) but only for certain strings.

What I've Tried

1) I've tried removing my use of Range().Value to Range().Value2 and later Cell().Value.

2) Stefan already has the following in to handle timing issues with screen refresh rates when pasting in objects and dealing with warning messages:

     Set objExcelApp = CreateObject("Excel.Application")

          objExcelApp.Visible = False           'suppresses the display of the Excel workbook

          objExcelApp.DisplayAlerts = False 'suppresses warning popups

and I brought in

          objExcelApp.ScreenUpdating = False 'just to see if it had a affect...but I would have thought Visible = False already handles this issue.

3) I have also brought in

     ActiveDocument.GetApplication.WaitForIdle

     ActiveDocument.GetApplication.SLEEP 3000 at different point in the script to try to avoid timing issues with the amount of content copied over thinking that the processor might be to quick (or slow) or something like that.

4) And then lastly I've gone the route of adding

          objExcelApp.CutCopyMode = False in the hopes that it is just a full clipboard issue causing delays in pasting.

Conclusion

I've attached the script that I'm using (minus a company details and the code for CommProcessNum = 3 which does not require looping through the names list in j). My specific issue is with CommProcessNum = 1 or 2.Please excuse the code I don't really code in VBScript often (if you have any suggestions, I would more than welcome it ).

[#2]

Anything that I can do to help you help me?

The particular point where things go wonky withedata applied to rows 38:41 where apply a bold and a border:

summarySH.Cells(41,2).Value = "COMMISSION"

and rows 72:82 with following section where I add hyperlinks with email adress details:

summarySH.Cells(75,2).Value = "Queries"

set rng = summarySH.Cells(77,2) 'commission payment email

    With summarySH

        .Hyperlinks.Add .Range("B77:H77"), "mailto:x@company.co.za"

        .Hyperlinks(2).EmailSubject = "Monthly Solutions Commission Payment for "& sAdviserName &" on "& sMonthYear.GetContent.String

        .Hyperlinks(2).TextToDisplay= "For queries regarding the payment of this commission please email X or phone (000) 000 0000"

    End With

    With rng.Font

        .ColorIndex = 0

        .Underline = xlUnderlineStyleNone

    End With

    With rng.Characters(61,5).Font

        .Underline = xlUnderlineStyleSingle

        .Color = RGB(59,181,245)

    End With  

set rng = summarySH.Range("B79")    'commission calc email

    With summarySH

        .Hyperlinks.Add .Range("B79:H79"), "mailto:y@company.co.za"

        .Hyperlinks(3).EmailSubject = "Monthly Solutions Commission Calculation for "& sAdviserName &" on "& sMonthYear.GetContent.String

        .Hyperlinks(3).TextToDisplay= "For queries regarding the calculation of this commission please email Y or phone (000) 000 0000"

    End With

    With rng.Font

        .ColorIndex = 0

        .Underline = xlUnderlineStyleNone

    End With

    With rng.Characters(65,5).Font

        .Underline = xlUnderlineStyleSingle

        .Color = RGB(59,181,245)

    End With

[tresesco]

Probably, you can help creating a sample qvw and being more specific to the issue (as you have already explained in sufficient detail). Sometimes, to-the-point and precise issue description could help generating more interests in lazy people like me.

[marcus_sommer]

Maybe you runs into problems with the merging of cells then afterwards you couldn't set any values to them without a message and a user-depending decision. Generally you should at first export/writing all objects/text and make the formatting afterwards. So you need independent routines for this or any kind of counter in your script.

Further, to find errors in such code could be very difficult so I suggest to include some logging-logic maybe per msgbox or in an extra sheet or textfile to count your loops and return the variable-values and so on.

- Marcus

[#5]

Hi tresesco, thank you for your response. I think your points on brevity and a working QVW example are valid

[#6]

Thank you Marcus

I'll try splitting the two processed as you suggest, it sounds like a solid lead.

I might need some guidance on doing as you instructed in the last part. It sounds like you are referring to something like Trace in QlikView scripting. I've used message boxes to act as break-points in the VBScript, but besides slowing down the process it did not change the outcome (text being pasted to the incorrect script and formatting being applied to cells other than the ones stated in the script). Do you perhaps have some practical tips you to do this sort of debugging? Perhaps I'm just not doing it right.

I'll try splitting the code between exporting and formatting processes as you have suggested.

[marcus_sommer]

Here the main-logic. Instead of using two separate loops for each sheet you could also use a counter which counts the objects and is it the last one start the formatting. Actually I do so but I'm not sure if I used it again by the next release of my exporting-routines.

for ... qv applications

     for ... xls application

          for ... xls-sheet

               for ... qv objects exporting

                    ...

               next

               for ... qv objects formatting

                    ...

               next

          next

     next

next

Generally it needs a lot of effort to build and maintain such unique single-solutions and if you have the possibilities to use the publisher or third-party tools like NPrinting you should use them.

To trace your routines you could use in simply cases the msgbox but if it's bigger or more complicated it's better to use a text-file (another use-case here - Re: export all objects to ppt - but an example with writing in txt-files) or if you operate with xls I would use a separate sheet to write all my loop-countings and variable-values and so on (you need only set the xls-application visibilty to true and you could use them directly).

- Marcus

[#8 · Accepted solution]

Hi Marcus

Thank you again for you help and advice!

What ended up working for me was to change how I was looping through the code. Instead of (in pseudo code):

creating the workbook

For i=1 To Advisor.Count

     export objects

     add text to cells

     format cells, rows & columns

Next

saving and closing workbook

I brought the creating and closing of the Excel application and workbook inside the loop

For i=1 To Advisor.Count

     create the workbook

     export objects

     add text to cells

     format cells, rows & columns

     save & close workbook

Next

Doing it this way removed my 'smudging issue'. I initially avoided going this route because I thought it would be faster to keep the Excel Application open and rather add and close workbooks as I move through the loop, but closing and opening the Excel Application does not take noticeably longer.

Best Regards

Charles