Macro to export multiple tables in to single excel

[markgraham123]

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

[tamilarasu]

Mark,

Check the attachment.

[markgraham123]

Hi Nagaraj,

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

[tamilarasu]

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

[markgraham123]

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

[tamilarasu · Accepted solution]

Here you go.

[markgraham123]

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.

[tamilarasu]

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.

[markgraham123]

Thanks a ton Sir!

You nailed it

[tamilarasu]

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: