topic Re: Export to XLSX in QlikView

Export to XLSX

Mon, 13 Jan 2014 16:48:31 GMT

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

Re: Export to XLSX

Gysbert_Wassenaar — Mon, 13 Jan 2014 17:02:27 GMT

See this post: Re: QV to excel 2010?

Re: Export to XLSX

Mon, 13 Jan 2014 17:04:04 GMT

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

Re: Export to XLSX

Mon, 13 Jan 2014 17:29:43 GMT

Thanks for this.

Is there a simpler version of this code?

Thanks,

Re: Re: Export to XLSX

Mon, 13 Jan 2014 17:48:22 GMT

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

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

Re: Re: Export to XLSX

Mon, 13 Jan 2014 17:58:13 GMT

Thanks for this.

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

Thanks

Re: Export to XLSX

Mon, 13 Jan 2014 18:44:42 GMT

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

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

Re: Export to XLSX

Mon, 13 Jan 2014 18:50:42 GMT

Hi,

seems to fail at the line

set oXL=CreateObject("Excel.Application")

Re: Export to XLSX

Mon, 13 Jan 2014 18:59:36 GMT

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