Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ).
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
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
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.
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
Hi tresesco, thank you for your response. I think your points on brevity and a working QVW example are valid
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.
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
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