topic Re: Help with PasteSpecial in Macro VBScript issues required in QlikView

Help with PasteSpecial in Macro VBScript issues required

Anonymous — Thu, 09 Mar 2017 10:08:25 GMT

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

Re: Help with PasteSpecial in Macro VBScript issues required

sasiparupudi1 — Fri, 17 Mar 2017 13:07:44 GMT

Set XLApp = CreateObject("Excel.Application")

XLApp.Visible = True

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

RowCount=Doc.GetSheetObject(var17).GetNoOfRows

If RowCount>0 Then

Doc.GetSheetObject(var17).CopyTableToClipBoard TRUE

'-- Next a check is performed to see if the chart contains rows of data

ActiveDocument.Variables("vRows").SetContent RowCount, true

'--Now if there are rows of data present then the paste special is performed. xlPasteValues is set further up in my script as

xlApp.ActiveSheet.Range("A2").Select

xlApp.ActiveSheet.PasteSpecial xlPasteValues

End If

hth

Sas

Re: Help with PasteSpecial in Macro VBScript issues required

Anonymous — Mon, 20 Mar 2017 11:54:09 GMT

Thank you Sasidhar. I'll give this a go in a few days when I revisit this and will let you know how I get on.

Thanks again,

Amjid

Re: Help with PasteSpecial in Macro VBScript issues required

Anonymous — Thu, 23 Mar 2017 10:29:41 GMT

Thank you again Sasidhar. This has worked.

You have prevented any more of my hair turning grey.

Amjid

Re: Help with PasteSpecial in Macro VBScript issues required

sasiparupudi1 — Thu, 23 Mar 2017 20:07:39 GMT

I am glad that this helped you. Please mark any helpful/Correct answers, if you are satisfied with the suggestions

All the best,

Sasi