Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
amjidkhan
New Contributor II

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
amjidkhan
New Contributor II

Re: Help with PasteSpecial in Macro VBScript issues required

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

4 Replies
sasiparupudi1
Honored Contributor III

Re: Help with PasteSpecial in Macro VBScript issues required

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

amjidkhan
New Contributor II

Re: Help with PasteSpecial in Macro VBScript issues required

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

amjidkhan
New Contributor II

Re: Help with PasteSpecial in Macro VBScript issues required

Thank you again Sasidhar. This has worked.

You have prevented any more of my hair turning grey.

Amjid

sasiparupudi1
Honored Contributor III

Re: Help with PasteSpecial in Macro VBScript issues required

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

All the best,

Sasi

Community Browser