4 Replies Latest reply: Mar 23, 2017 4:07 PM by Sasidhar Parupudi RSS

    Help with PasteSpecial in Macro VBScript issues required

    Amjid Khan

      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