topic Macro to export multiple tables in to single excel in QlikView

Macro to export multiple tables in to single excel

markgraham123 — Fri, 15 Apr 2016 01:44:30 GMT

Hi all,

I'm trying to fetch all tables/straight tables into single excel sheet using a macro( i can put this in a button).

Can some one please help here.

I'm new to QlikView and struggling on this.

Any help is really appreciated!!

Re: Macro to export multiple tables in to single excel

tamilarasu — Fri, 15 Apr 2016 01:55:18 GMT

Mark,

Check the attachment.

Re: Macro to export multiple tables in to single excel

markgraham123 — Fri, 15 Apr 2016 02:05:41 GMT

Hi Nagaraj,

When i click on it, its' displaying the code. But not exporting to excel.

Re: Macro to export multiple tables in to single excel

tamilarasu — Fri, 15 Apr 2016 02:09:48 GMT

In the Module editor make sure the settings are like in this picture:

Re: Macro to export multiple tables in to single excel

markgraham123 — Fri, 15 Apr 2016 02:13:26 GMT

Nagaraj,

Every time i click on the button, the module opens showing the script.

Can i stop this?

Also, i'm trying to get it in one sheet with 3 lines seperated. (I did notice that there is declaration of sheets)

Is there a possibility to get this??

Re: Macro to export multiple tables in to single excel

tamilarasu — Fri, 15 Apr 2016 03:00:46 GMT

Here you go.

Re: Macro to export multiple tables in to single excel

markgraham123 — Fri, 15 Apr 2016 03:05:40 GMT

Nagaraj,

Its opening in single sheet but the macro module opens when we click on the button.

Apart from that this is excellent and superb.

Re: Macro to export multiple tables in to single excel

tamilarasu — Fri, 15 Apr 2016 03:14:48 GMT

Mark,

Glad to hear.

For me it's not showing the macro window. Try below steps and check once again.

In the Module Editor make sure the settings are like in this picture:

After that go to Settings -> Document Properties set "Macro override security" as below:

Save your application.

Re: Macro to export multiple tables in to single excel

markgraham123 — Fri, 15 Apr 2016 03:28:39 GMT

Thanks a ton Sir!

You nailed it

Re: Macro to export multiple tables in to single excel

tamilarasu — Fri, 15 Apr 2016 04:11:24 GMT

I'm posting the code here. Hope this helps someone someday.

sub exportToExcel

==============================================================
' File Path & Name


 Path = "C:\temp\"
FileName = "Test_"
strSaveFile = Path & FileName
'==============================================================
'Open Excel
set oXL=CreateObject("Excel.Application")
oXL.visible=True
oXL.Workbooks.Add

aSheetObj=Array("TB01","TB02","CH01")  ' Chart ID's here


'==============================================================
for i=0 to UBound(aSheetObj)
Set oSH = oXL.ActiveSheet

num_rows = oSH.UsedRange.Rows.Count

If num_rows = 1  then
oSH.Range("A2").Select   
Else
oSH.Range("A" & num_rows+4).Select
End If

Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))
obj.CopyTableToClipboard True
oSH.Paste
sCaption=obj.GetCaption.Name.v
set obj=Nothing 
       
oSH.Cells.Select


oSH.Columns("A").ColumnWidth = 12.17
oSH.Columns("B").ColumnWidth = 12.17


If num_rows = 1  then
oSH.Range("A" & num_rows).Value = sCaption
oSH.Range("A" & num_rows).Font.Bold = True
'oSH.Range("A" & num_rows).Font.ColorIndex = 3
oSH.Range("A" & num_rows).Interior.ColorIndex = 40
Else

oSH.Range("A" & num_rows+3).Value  = sCaption
oSH.Range("A" & num_rows+3).Font.Bold = True
'oSH.Range("A" & num_rows+3).Font.ColorIndex = 3
oSH.Range("A" &num_rows+3).Interior.ColorIndex = 40
End If
'oXL.Selection.Columns.AutoFit 


next


'==============================================================
  oSH.Range("A1").Select 
  oXL.Sheets("Sheet2").Delete
  oXL.Sheets("Sheet3").Delete
  oSH.Name = "Data"
   
   oXL.ActiveWorkBook.SaveAs strSaveFile & replace(date, "/", "-") & ".xlsx"
  
set oSH = Nothing
set oXL=Nothing
end sub

Output:

Re: Macro to export multiple tables in to single excel

markgraham123 — Fri, 15 Apr 2016 14:17:10 GMT

Nagaraj,

Here is the problem.

The excel is exporting, but since there is already a file name with that name, it's popping up the error and asking to replace the existing file.

Please see the attached.

I think, getting timestamp in the 'save as' class works.

I tried to but its not working.

Re: Macro to export multiple tables in to single excel

tamilarasu — Sat, 16 Apr 2016 07:26:53 GMT

Hello Mark,

Sorry for the late reply. I'm not active on weekends in this forum. You can replace the below line in macro.

oXL.ActiveWorkBook.SaveAs strSaveFile & replace(date, "/", "-") & ".xlsx"

oXL.ActiveWorkBook.SaveAs strSaveFile & ActiveDocument.Evaluate("date(Now(), 'DD-MM-YYYY hh:mm:ss')") & ".xlsx"

Also, you no need to hard code the folder path in macro. Instead, create an inputbox and click new variable and name the variable as "vPath". Now, you can enter the path in front end. You need to change the below line in macro window.

Path = "C:\temp\"

Set Path = ActiveDocument.GetVariable("vPath")

Path = vPath.GetContent.String

Hope this helps you.

Re: Macro to export multiple tables in to single excel

gagan_bhasin1 — Tue, 09 Jan 2018 10:14:08 GMT

This is a gem in terms of macro for QlikView exports. I am trying to send a Pivot Table and List Box by this method. The data is going but the functionality is not traveling. May you please help in this regard?

Re: Macro to export multiple tables in to single excel

tamilarasu — Tue, 09 Jan 2018 10:31:00 GMT

Hi Gagan,

Thank you. Could you please create a new thread and post some sample file and explain the issues that you are facing. I have some important work to complete now. I'll finish it and answer your question within 2 hours. Hope this is fine with you. Don't forget to tag me.

Re: Macro to export multiple tables in to single excel

gagan_bhasin1 — Wed, 10 Jan 2018 12:16:38 GMT

Sure!

Re: Macro to export multiple tables in to single excel

its_anandrjs — Thu, 08 Nov 2018 12:46:19 GMT

Hi arsu,

While using the above code explain by you it is still asking for save the file.

But actually, excel file to save on the mention location without asking for the file to save.

Can you check this, i have the same requirement.

Regards,

Anand

Re: Macro to export multiple tables in to single excel

Luci19 — Tue, 02 Jul 2019 13:26:33 GMT

Hi,

I recently come across this thread and used the code in the sample qlikview module and it works except that it only exports one table despite the fact that i've changed the name of the other two tables to the ones i have in my module, any reason why? I've also amended the settings as originally suggested in the trail of emails but it seems that the macro gets stuck after exporting the first table

Thanks in advance,

Luci