Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Help with PasteSpecial in Macro VBScript issues required

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

4 Replies
sasiparupudi1
Master III
Master III

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

Thank you again Sasidhar. This has worked.

You have prevented any more of my hair turning grey.

Amjid

sasiparupudi1
Master III
Master III

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

All the best,

Sasi