Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Export to XLSX

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

8 Replies

Re: Export to XLSX

See this post: Re: QV to excel 2010?


talk is cheap, supply exceeds demand
Not applicable

Re: Export to XLSX

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

Not applicable

Re: Export to XLSX

Thanks for this.

Is there a simpler version of this code?

Thanks,


Not applicable

Re: Re: Export to XLSX

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

Not applicable

Re: Re: Export to XLSX

Thanks for this.

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

Thanks


Not applicable

Re: Export to XLSX

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

Not applicable

Re: Export to XLSX

Hi,

seems to fail at the line

set oXL=CreateObject("Excel.Application")

Not applicable

Re: Export to XLSX

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

Community Browser