Export to XLSX

[#1]

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

[Gysbert_Wassenaar]

See this post: Re: QV to excel 2010?

[#3]

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

[#4]

Thanks for this.

Is there a simpler version of this code?

Thanks,

[#5]

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

[#6]

Thanks for this.

I have amended it however it doesnt seem to work. where is the location to send the file to?

Thanks

[#7]

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

[#8]

Hi,

seems to fail at the line

set oXL=CreateObject("Excel.Application")

[#9]

You need to allow the System Access to execute this line. See the picture below.