topic Re: macro for Export Table data to excel with desired file name in QlikView

macro for Export Table data to excel with desired file name

abirami_palanis — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: macro for Export Table data to excel with desired file name

devarasu07 — Thu, 17 Aug 2017 08:39:16 GMT

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)

Re: macro for Export Table data to excel with desired file name

abirami_palanis — Thu, 17 Aug 2017 09:53:40 GMT

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.

Re: macro for Export Table data to excel with desired file name

Frank_Hartmann — Thu, 17 Aug 2017 10:15:53 GMT

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

Re: macro for Export Table data to excel with desired file name

abirami_palanis — Thu, 17 Aug 2017 10:43:27 GMT

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.

Re: macro for Export Table data to excel with desired file name

Frank_Hartmann — Thu, 17 Aug 2017 11:02:12 GMT

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

Re: macro for Export Table data to excel with desired file name

abirami_palanis — Thu, 17 Aug 2017 11:22:42 GMT

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!

Re: macro for Export Table data to excel with desired file name

Frank_Hartmann — Thu, 17 Aug 2017 11:34:51 GMT

in third line you have:

objExcel.Visible = False

just replace false with true and delete line 10

Re: macro for Export Table data to excel with desired file name

abirami_palanis — Thu, 17 Aug 2017 11:56:36 GMT

Thank You Frank. Working fine..