macro for Export Table data to excel with desired file name

[abirami_palanis]

Hi,

I am looking for a macro to export the table data to Excel  with a  desired file name. I am using QV 12.0 edition.

Could someone help me out?

Thanks in Advance..

[devarasu07]

Hi,

Try this Code

http://www.qlikblog.at/971/qliktip-32-exporting-multiple-objects-single-excel-document/

enable this setting in your app (press ctrl+M)

[abirami_palanis]

Hi Devarasu,

Thank you for the reply.

but my requirement is to define the excel file name, not a sheet name.

When we export data to excel  it carries the name of the Object ID which QlikView will assign as default . I want to change that name to the desired file name.

[Frank_Hartmann]

sub ExcelExport
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = True
Set xlDoc = objExcel.Workbooks.Add
set obj = ActiveDocument.GetSheetObject("CH01")
obj.CopyTextToClipboard
objExcel.Range("A1").PasteSpecial
xlDoc.SaveAs "C:\Users\Admin\Desktop\Test.xlsx"
xlDoc.Close
end sub

Just adapt the Object-ID, Path and Outputfilename according your environment

hope this helps

[abirami_palanis]

Hi Frank,

Thanks!

I am getting error in code.  please see the screenshot.

This code will help to save a document in a particular path with a desired name. but my requirement is that once we export , the excel should open automatically.

[Frank_Hartmann]

adapt the object-ID ("CH01") to the Object-ID in your qvw which you want to export.

If you want to see the Excel File while exporting just adapt this line:

obj.Excel.Visible = True

and erase this line:

xlDoc.Close

hope this helps

[abirami_palanis]

Hi Frank,

Again i am getting error in code. Please see below.

In a specified location , the file is getting  saved. And also in location it is showing that the file is opened. But actually i am not seeing any excel file is opened in desktop.

Thanks!

[Frank_Hartmann · Accepted solution]

in third line you have:

objExcel.Visible = False

just replace false with true and delete line 10

[abirami_palanis]

Thank You Frank. Working fine..