topic Export table to Excel with Macro in QlikView

Export table to Excel with Macro

Mon, 09 Mar 2015 13:56:43 GMT

I'm trying to export data from a table into Excel using a button via a macro. It seems pretty straight forward, but for some reason I can't get it to work. The below code fails on the first line - the Excel object is never created. My button is indeed calling the method - I verified this by putting message boxes before and after the 'Set objXL' line. The first message box fires, but the second one doesn't and the code editor pops up, indicatng and error. Can anyone see anythng wrong with this?

Sub Excel_Dump()

Set objXL = CreateObject("Excel.Application")
Set objWkb = objXL.Workbooks.Add
Set objSht1 = objWkb.Worksheets("Sheet1")
objSht1.Select
ActiveDocument.GetSheetObject("TB01").CopyTableToClipboard true
objSht1.Paste
objSht1.Name = "Export"
XLApp.Visible = true

end Sub

Re: Export table to Excel with Macro

Mon, 09 Mar 2015 14:44:42 GMT

I figued it out. I had to adjust the Requsted Modue Security and Current Local Security to System Access. I wound up using the following code, if anyone in the future would like it.

Sub Excel_Dump()

Set objXL = CreateObject("Excel.Application")
set objWkb = objXL.Workbooks.Add

objXL.Visible = False
objWkb.Worksheets("Sheet1").Range("A1").Select()

ActiveDocument.GetSheetObject("TB01").CopyTableToClipboard true

objWkb.Worksheets("Sheet1").PasteSpecial
objWkb.Worksheets("Sheet1").Name= "Export"

objXL.Visible = False
end Sub

Re: Export table to Excel with Macro

Anonymous — Sun, 24 May 2015 09:31:58 GMT

Could you please tell me how to save Excel to a folder,

When I add these code : XLDoc.SaveAs Path,true

it will open the macro editer windows again and always ask me whether replace the existing file

sub ExcelFile

strDate = CDate(Date)

strDay = DatePart("d", strDate)

strMonth = DatePart("m", strDate)

strYear = DatePart("yyyy", strDate)

If strDay < 10 Then

strDay = "0" & strDay

End If

If strMonth < 10 Then

strMonth = "0" & strMonth

End If

GetFormattedDate = strMonth & "-" & strDay & "-" & strYear

Path = "C:\Users\Administrator\Desktop\培训\Distribute\Book5.xls"

FileName = "Test_" & GetFormattedDate & ".xlsx"

set XLApp = CreateObject("Excel.Application")

' XLApp.Visible = true

set XLDoc = XLApp.Workbooks.Add

ActiveDocument.GetSheetObject("CH23").CopyTableToClipboard true

XLDoc.Sheets(1).Paste()

XLDoc.Sheets(1).Rows("1:3000").EntireRow.AutoFit

ActiveDocument.GetSheetObject("CH17").CopyTableToClipboard true

XLDoc.Sheets(2).Paste()

XLDoc.Sheets(2).Rows("1:3000").EntireRow.AutoFit

XLDoc.Sheets(1).Name = "Page One"

XLDoc.Sheets(2).Name = "Page Two"

XLDoc.Sheets(3).Delete

' XLDoc.Sheets(1).Range("A1").Select

XLDoc.SaveAs Path,true

' XLDoc.ExportBiff "C:\Users\Administrator\Desktop\培训\Distribute\Book5.xlsx"

end sub

Thanks a lot