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, http://www.qlikblog.at/971/qliktip-32-exporting-multiple-objects-single-excel-document/comment-page-1/#comments.
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 an 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'm not quite sure how to attach the script, but will gladly provide the code.
Amendment Not sure about attaching all of the script as a text file, but here is the code snippet that export the text elements and then formats the workbook:
Private Sub Excel_PopulateAdviserCommTextElements(objExcelDoc,sAdviserName,sAdviserIDNum,sAdviserEmail,sYear,sMonth,sMonthYear)
sWorkbookName = sAdviserIDNum &"_"& sYear.GetContent.String &"_"& sMonth.GetContent.String &"_"& sAdviserName
Set summarySH = objExcelDoc.Sheets("Summary")
'transform the pasted objects to remove null rows and columns
summarySH.Columns("V:V").Delete xlToLeft
summarySH.Rows("37:37").Delete xlUp
summarySH.Rows("71:71").Delete xlUp
'replace "-" with zeroes for calculations
summarySH.Range("D9:V71").Replace "-", "0", xlPart, xlByRows, False, False, False 'What, Replacement, LookAt, SearchOrder, MatchCase, SearchFormat, ReplaceFormat
'calc table totals for asset table
summarySH.Range("V9:V37").FormulaR1C1 = "=SUM(RC[-18]:RC[-1])"
summarySH.Range("D37:U37").FormulaR1C1 = "=SUM(R[-28]C:R[-1]C)"
'calc table totals for copmmission table
summarySH.Range("V43:V71").FormulaR1C1 = "=SUM(RC[-18]:RC[-1])"
summarySH.Range("D71:U71").FormulaR1C1 = "=SUM(R[-28]C:R[-1]C)"
'add text elements
summarySH.Cells(2,3).Value = "Wealth Solutions"
summarySH.Cells(3,3).Value = "Monthly Commission Report: End of " & sMonthYear.GetContent.String
summarySH.Cells(7,2).Value = "ASSETS UNDER MANAGEMENT"
summarySH.Cells(2,19).Value = "Commission (Ex-VAT)"
summarySH.Cells(3,19).Value = "VAT @ 14%"
summarySH.Cells(4,19).Value = "Amount due"
summarySH.Cells(2,22).Value = summarySH.Cells(71,22).Value
summarySH.Cells(3,22).Value = summarySH.Cells(71,22).Value*0.14
summarySH.Cells(4,22).Value = "=Sum(V2:V3)"
summarySH.Cells(41,2).Value = "COMMISSION"
'format
summarySH.Range("V2:V4").Style = "Comma"
summarySH.Range("D9:V37").Style = "Comma"
summarySH.Range("D43:V71").Style = "Comma"
'emails hyperlinks
With summarySH 'advisor email
.Hyperlinks.Add .Cells(4,4), "mailto:"&sAdviserEmail
' .Hyperlinks.Add .Range("D4"), "mailto:"&sAdviserEmail
.Hyperlinks(1).EmailSubject = "Monthly Solutions Commission: " & sMonthYear.GetContent.String
.Hyperlinks(1).TextToDisplay= sAdviserName
End With
summarySH.Cells(75,2).Value = "Queries"
set rng = summarySH.Cells(77,2) 'commission payment email
With summarySH
.Hyperlinks.Add .Range("B77:H77"), "mailto:[email protected]"
.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:[email protected]"
.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
'alter column width and row height
summarySH.Columns("A").ColumnWidth = 4.57
summarySH.Columns("B").ColumnWidth = 34.00
summarySH.Columns("C").ColumnWidth = 0.08
summarySH.Columns("D:U").ColumnWidth = 13.28
summarySH.Columns("V").ColumnWidth = 16.00
summarySH.Columns("W").ColumnWidth = 4.57
summarySH.Rows("1:1").RowHeight = 33.00
summarySH.Rows("7:7").RowHeight = 23.25
summarySH.Rows("39:39").RowHeight = 23.25
summarySH.Rows("76:76").RowHeight = 5.25
summarySH.Rows("78:78").RowHeight = 5.25
'alignment
summarySH.Range("V2:V4").HorizontalAlignment = xlRight
with summarySH.Range("B7:U7")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Merge
end with
with summarySH.Range("B41:U41")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Merge
end with
'hide unused columns and rows and then fill with white
summarySH.Columns("X:XFD").EntireColumn.Hidden = True
summarySH.Rows("83:1048576").EntireRow.Hidden = True
summarySH.Range("A1:W82").Interior.ColorIndex = 2 'white
'bold
summarySH.Range("C2:C3,R2:U4,B7,B41,B75").Font.Bold = True
'lines
With summarySH.Range("B7:V7").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlThin
End With
With summarySH.Range("B41:V41").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlThin
End With
'adjust focus
'summarySH.Range("A1").Select
'add filter to Consolidated sheet for Provider
objExcelDoc.Sheets("Consolidated").Range("A1:T1").AutoFilter
'adjust focus
'objExcelDoc.Sheets("Consolidated").Range("A1").Select
End SubThe 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:[email protected]"
.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:[email protected]"
.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
Thank You
What ended up working for me was rather closing the Excel workbook after producing each report and then opening up the application and then exporting and formatting the workbook for the next person, saving and closing.
Opening the workbook once, exporting, formatting, saving and closing the workbook and then looping to the next report while the application was open seemed to cause the strange exporting of elements across mutliple (or incorrect) rows