topic VBScript placing string values in incorrect cells in Excel workbook in QlikView

VBScript placing string values in incorrect cells in Excel workbook

Tue, 18 Nov 2014 08:00:00 GMT

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 ).

Re: VBScript placing string values in incorrect cells in Excel workbook

Mon, 24 Nov 2014 12:00:21 GMT

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

Re: VBScript placing string values in incorrect cells in Excel workbook

tresB — Mon, 24 Nov 2014 12:06:16 GMT

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.

Re: VBScript placing string values in incorrect cells in Excel workbook

marcus_sommer — Mon, 24 Nov 2014 12:26:58 GMT

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

Re: VBScript placing string values in incorrect cells in Excel workbook

Mon, 24 Nov 2014 17:37:28 GMT

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

Re: VBScript placing string values in incorrect cells in Excel workbook

Mon, 24 Nov 2014 17:46:04 GMT

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.

Re: VBScript placing string values in incorrect cells in Excel workbook

marcus_sommer — Tue, 25 Nov 2014 08:49:16 GMT

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

...

for ... qv objects formatting

...

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

Re: VBScript placing string values in incorrect cells in Excel workbook

Tue, 02 Dec 2014 18:52:28 GMT

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

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

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