Hi,
I'm currently working on copying and pasting straight charts from Qlikview to an Excel spreadsheet. This is automated using a macro from within Qlikview.
Originally I was simply copying and pasting the charts via macro which worked fine but then I started coming across issues with copying and pasting data with leading zeros, the zeros were not carried over. This is most likely down to Excel interpreting these as numbers.
A workaround I've programmed is to format the spreadsheet template as text and then copy and paste into that which in theory should keep the leading zeros as long as I paste as values from Qlikview to Excel.
The first issue I encountered with this is the macro falls over every time it comes across a chart with no data. I thought I had gotten around this issue by placing a check to only perform the copy and paste special if the row count is greater than 0 for a particular chart.
Oddly, this works for some of the charts but not all. Here is an example of the code:
'-- This bit activates the correct spreadsheet template and worksheet to use and then copies the data held in a particular straight chart to the clipboard. WS1 is determined by an earlier process, it's basically the name of the worksheet and that works fine.
XlApp.Workbooks(Template).activate
XlApp.Worksheets(WS1).select
SET v17 = ActiveDocument.Variables("vMacroChartId7")
var17 = v17.GetContent.STRING
Doc.Fields(fname).Clear
Doc.Fields(fname).SELECT Field.Item(i).Text
Doc.GetApplication.WaitForIdle
Doc.GetSheetObject(var17).CopyTableToClipBoard TRUE
'-- Next a check is performed to see if the chart contains rows of data
Set RowCount = ActiveDocument.GetSheetObject(var17)
ActiveDocument.Variables("vRows").SetContent RowCount.GetNoOfRows, true
RowsChart = ActiveDocument.Variables("vRows").GetContent.STRING
'--Now if there are rows of data present then the paste special is performed. xlPasteValues is set further up in my script as
(CONST xlPasteValues = -4163).
If RowsChart > 0 then
xlApp.ActiveSheet.Range("A2").Select
xlApp.ActiveSheet.PasteSpecial xlPasteValues
Else
End If
I guess my question is how can I overcome this seemingly random occurrence of the macro falling over for some charts and not others?
Can I avoid using the check for data in charts before I can copy and paste values all together?
I'm at a loss. Any help with this would be greatly appreciated.
Thank you reading.
Amjid
