Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following routine:
Sub export
set obj = ActiveDocument.GetSheetobject("TB01")
obj.exportbiff ("C:\Test.xlsx")
end sub
I then call this in a button.
this exports the file correctly however I get an error when opening the file. excel says it is corrupted. Any ideas why?
I need a simple routine to export a table to XLSX
See this post: Re: QV to excel 2010?
I think that use the create object is the better option. Use the command CreateObject("Excel.Application"). Take a look at this related post below pls.
http://community.qlik.com/thread/80672
Best Regards.
Tiago
Thanks for this.
Is there a simpler version of this code?
Thanks,
The code below can be used to export more than one object to a excel file, take a look.
sub launchXL
set oXL=CreateObject("Excel.Application")
oXL.visible=True
oXL.Workbooks.Add
aSheetObj=Array("TB01","CH01")
for i=0 to UBound(aSheetObj)
oXL.Sheets.Add
Set oSH = oXL.ActiveSheet
oSH.Range("A1").Select
Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))
obj.CopyTableToClipboard True
oSH.Paste
sCaption=obj.GetCaption.Name.v
set obj=Nothing
oSH.Rows("1:1").Select
oXL.Selection.Font.Bold = True
oSH.Cells.Select
oXL.Selection.Columns.AutoFit
oSH.Range("A1").Select
oSH.Name=left(sCaption,30)
set oSH=Nothing
next
set oXL=Nothing
end sub
You just have to adjust it by your necessity.
For more advanced options look at the link I've posted before.
Best regards.
Tiago
Thanks for this.
I have amended it however it doesnt seem to work. where is the location to send the file to?
Thanks
Try this one, it worked for me. Now it's saving the Excel file.
sub launchXL
set oXL=CreateObject("Excel.Application")
oXL.visible=true
oXL.Workbooks.Add
aSheetObj=Array("CH1", "CH2")
for i=0 to UBound(aSheetObj)
oXL.Sheets.Add
Set oSH = oXL.ActiveSheet
oSH.Range("A1").Select
Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))
obj.CopyBitmapToClipboard
oSH.Paste
sCaption=obj.GetCaption.Name.v
set obj=Nothing
oSH.Rows("1:1").Select
oXL.Selection.Font.Bold = True
oSH.Cells.Select
oXL.Selection.Columns.AutoFit
oSH.Range("A1").Select
'oSH.Name=left(sCaption,30)
'set oSH=Nothing
next
oSH.SaveAs "D:\temp.xlsx", 51
set oSH=Nothing
set oXL=Nothing
end sub
I'm passing just charts, I didn't do the logic for tables.
Best regards.
Tiago
Hi,
seems to fail at the line
set oXL=CreateObject("Excel.Application")
You need to allow the System Access to execute this line. See the picture below.