Re: Creating Macros to Export all the data from an... - Qlik Community

crichter14 · ‎2018-03-02

I know there have been answers to this, but I need something a bit more basic. What I am wondering is has anyone done a video (YouTube or something else) that goes over this?

I'm sure the example I've found here is awesome: Export multiple objects to excel (multi sheets) without opening the excel application

BUT it's been a long time since I've used VB and I would really like something that explains what each piece does. We are trying to export every object from Qlik to Excel on different spreadsheet tabs (xlsx) in order to audit changes to the Qlik Script. If we can do it in Excel our users will be able to validation changes resulting from script changes are appropriate. It doesn't matter if the object is a Table, a Chart, a List Box, etc.

We want everything.

I have included a simple code that I found (I will not take credit), but I need to expand it so that I can add more than TB01. If I understood each piece I could adjust. Again, I need something explained so K.I.S.S. Keep it simple stupid - I am the stupid this time and I am humbly looking for an expert with some patience. I can look through link codes and examples all day - but if the documentation that explains the code isn't there, it's not much help.

Thank you in advance for any assistance you can provide.

Cheryl

================================

Sub ExcelFile

DIM ExcelApplication, ExcelWorkbook

SET ExcelApplication = CREATEOBJECT("Excel.Application")

SET ExcelWorkbook = ExcelApplication.Workbooks.Add

ActiveDocument.GetSheetObject("TB01").CopyTableToClipBoard TRUE

ExcelWorkbook.Worksheets(1).Paste

ExcelApplication.DisplayAlerts = FALSE

ExcelWorkbook.SaveAs "C:\Users\admin.cherylr\Documents\test.xls", 56

ExcelApplication.Quit

MsgBox "Export Complete"

End Sub

=====================================

crichter14 · ‎2018-03-02

Note: Tamil Nagaraj has been very helpful and I am working through the solution provided. If it works, I will post it back here with any comments or questions. He suggested I start my own thread, since the one I had found was dated.

If you see this, thank you Tamil.

crichter14 · ‎2018-03-02

The file works really well! I am trying to merge it with another file from Tamil. That shows the hidden tables as well. Here's what I have so far. I get no error message, but I also get no data. Ideas?

Sub ExcelFile

set oXL = CreateObject("Excel.Application")

oXL.DisplayAlerts = False

oXL.visible=True 'False to hide the excel

Dim oXLDoc 'as Excel.Workbook

Dim i

Set oXLDoc = oXL.Workbooks.Add

FileName = "Test.xlsx"

FilePath = ActiveDocument.GetVariable("vPath").GetContent.String

ResetShow = ActiveDocument.GetVariable("vBO_Plan_Split").GetContent.String

vBO_Plan_Split = ActiveDocument.GetVariable("vBO_Plan_Split").GetContent.String

If vBO_Plan_Split <> 1 then

ActiveDocument.Variables("vBO_Plan_Split").SetContent "1", true

End If

FilePath = ActiveDocument.Variables("vPath").GetContent.String

FileName = "Export_" & ActiveDocument.Evaluate("date(Now(), 'DDMMYYYY hhmmss')")

'---------------------------------------

SheetObj=Array("CH953","CH1093","CH1094","CH1095","CH1096","CH1145") 'Chart ID's

'---------------------------------------

for i=0 to UBound(SheetObj)

'ActiveDocument.GetApplication.WaitForIdle

oXL.Sheets.Add

oXL.ActiveSheet.Move ,oXL.Sheets( oXL.Sheets.Count )

Set oSH = oXL.ActiveSheet

oSH.Range("A1").Select

Set obj = ActiveDocument.GetSheetObject(SheetObj(i))

obj.CopyTableToClipboard True

oSH.Paste

sCaption=obj.GetCaption.Name.v

Set obj=Nothing

oSH.Rows("1:1").Select

oXL.Selection.Font.Bold = True

oSH.Cells.Select

oXL.Selection.Columns.AutoFit

oSH.Range("A1").Select

oSH.Name=left(sCaption,30)

Set oSH=Nothing

Call Excel_DeleteBlankSheets(oXLDoc)

'---------------------------------------

oXL.DisplayAlerts = True

oXLDoc.Sheets(1).Select

If FilePath <>"" then

oXLDoc.SaveAs FilePath & "\" & FileName & ".xlsx"

Else

Msgbox "Folder path can not be empty. Enter Valid path"

Exit Sub

End If

oXLDoc.Close FALSE

oXL.Quit

Set oXL =Nothing

Set oXLDoc =Nothing

ActiveDocument.Variables("vBO_Plan_Split").SetContent ResetShow, true

MsgBox "Export Complete"

End Sub

'--------------------------------------------------------------------

Private Sub Excel_DeleteBlankSheets(ByRef oXLDoc)

For Each ws In oXLDoc.Worksheets

If oXLDoc.Application.WorksheetFunction.CountA(ws.Cells) = 0 Then

On Error Resume Next

Call ws.Delete()

End If

'--------------------------------------------------------------------

tamilarasu · ‎2018-03-02

Hi Cheryl,

Great and happy that you have tried to something new other than what I have provided to you. Whatever you tried so far looks good to me. Is it possible for you to attach a sample file and let me know the issues that you are facing? I will try my level best to help you tomorrow or Monday. Hope this is fine with you. Happy weekend, Cheryl.

Creating Macros to Export all the data from an Application to Excel - Seperate Tabs