Qlikview to excel export macro

[#1]

Hi,

I am looking for a macro to export the data currently select in a straight table(not the column headers, just data) and send to a specified excel file on the next available row.  I do not wish to create a new excel file with this macro and I do not want to override what is already in the excel file.  I want to just add on to it.

Could someone help me out? 

My knowledge of VB macros is basic, but I can meander my way around pretty well.

Thanks!

[#2]

Hi,

Any help on this?

My macro is below.  Looking to copy and paste the data in a straight table to the next empty row/line in a specified excel document.

Sub ExcelExpwCaption
     'Set the path where the excel will be saved
     filePath = "C:\Users\nm36505\Desktop\SD QV\test.xls"

     'Create the Excel spreadsheet
     Set excelFile = CreateObject("Excel.Application")
     excelFile.Visible = true
     'Create the WorkBook
     Set curWorkBook = excelFile.WorkBooks.Add
     'Create the Sheet
     Set curSheet = curWorkBook.WorkSheets(1)

     'Get the chart we want to export
     Set tableToExport = ActiveDocument.GetSheetObject("CH82")
     Set chartProperties = tableToExport.GetProperties
     tableToExport.CopyTableToClipboard true

     'Get the caption
     chartCaption = tableToExport.GetCaption.Name.v
     'MsgBox chartCaption

     'Set the first cell with the caption
     curSheet.Range("A1") = chartCaption
     'Paste the rest of the chart
     curSheet.Paste curSheet.Range("A2")
     excelFile.Visible = true

     'Save the file and quit excel
     curWorkBook.SaveAs filePath
     curWorkBook.Close
     excelFile.Quit

     'Cleanup
     Set curWorkBook = nothing
     Set excelFile = nothing
End Sub

[#3 · Accepted solution]

Hi

This macro will do this, and will not append the objects title.

Just call the function with the ful file and path to the excel file and the Object ID.

Cheers

Sub Test
   ExcelAppend "h:\test.xlsx", "CH01"
End Sub

Sub ExcelAppend(strExcelAppenFile, strExelAppendObjectID)
   ' Create an instance of Excel
   SET objExcelApp = CREATEOBJECT("Excel.Application")
   
   ' Open workbook
   WITH objExcelApp
      .DefaultSaveFormat = xlWorkbookNormal
      .DisplayAlerts = FALSE
      .Workbooks.Open strExcelAppenFile
      .DisplayFullScreen = FALSE
      .Visible = FALSE
   END WITH
   
   ' Set worksheet
   SET objExcelSheet = objExcelApp.Worksheets(1)
      
   ' Set Excel used range
   SET objExcelRange = objExcelSheet.Range("A65535").End(-4162)
   ' Last used row in column A
   intExcelLastRow = objExcelRange.Row
   ' Set object to append from
   SET objObjectFrom = ActiveDocument.GetSheetObject(strExelAppendObjectID)
   ' Loop all rows of the object except first header row
   FOR intObjectRow = 1 To objObjectFrom.GetRowCount - 1
      ' Loop all columns of the object
      FOR intObjectColumn = 0 To objObjectFrom.GetColumnCount - 1
         ' Get object data
         SET objCell = objObjectFrom.GetCell(intObjectRow, intObjectColumn)
         ' Add that data to Excel cell
         objExcelSheet.Cells(intObjectRow + intExcelLastRow, intObjectColumn + 1) = objCell.Text
      NEXT
   NEXT
   
   ' Save and quit
   objExcelSheet.SaveAs strExcelAppenFile
   objExcelApp.Application.Quit
   SET objExcelSheet = NOTHING      
   SET objExcelApp = NOTHING
END SUB   

[#4]

Thank you!!! Awsome!

Now, the next part is the following:

I have an input box where the user can put some comments in.  I want to have those comments appended to the record that was selected at the time..then send all of this to excel using the macro above.

[#5]

Just add the variable in the input box to the object you are exporting.

(or make a new hidden object that is a copy of the object with the variable added to the object)

Cheers

[#6]

Hi,

Wouldnt there have to be some sort of statement to take what is typed in that inputbox and place it into the object I was exporting?

[#7]

Nevermind, I got it to work.

Thanks!!

[#8]

Hi guys,

I know this is an old post (2013), but I'm kinda stuck with the same problem.

I used the script of Martin Grape

(Thanks very much, It was very helpfull)

But I DO want the objects titles.

My VBscript knowledge isn't that good, so I couldn't figure it out myself.

Anyone know how to get the titles back on top in Excel?

Thanks.

Kevin

PS. sorry to reply in an old post.

[#9]

Not sure what you mean, describe the object and what you want to have in the Excel.

If you just want the headers, chahnge this:

FOR intObjectRow = 1 To objObjectFrom.GetRowCount - 1

To this:

FOR intObjectRow = 0 To objObjectFrom.GetRowCount - 1

In the code

Cheers

[#10]

That's what I thought..

But that doesn't work. It won't complete the macro and when I run it, it sets the "type marker" at the 0 on that line.

I want to use the same script you descibed above, but then with the headers.