Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
abirami_palanis
Contributor III
Contributor III

macro for Export Table data to excel with desired file name

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..

1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

in third line you have:

objExcel.Visible = False

just replace false with true and delete line 10

View solution in original post

8 Replies
devarasu07
Master II
Master II

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)

Capture.JPG

abirami_palanis
Contributor III
Contributor III
Author

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
Master II
Master II

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
Contributor III
Contributor III
Author

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.

Capture.PNG

Frank_Hartmann
Master II
Master II

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
Contributor III
Contributor III
Author

Hi Frank,

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

Excel.PNG

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.

Capture.PNG

Thanks!

Frank_Hartmann
Master II
Master II

in third line you have:

objExcel.Visible = False

just replace false with true and delete line 10

abirami_palanis
Contributor III
Contributor III
Author

Thank You Frank. Working fine..