topic Re: Multiple Tables Exporting from QV to Excel in QlikView

Multiple Tables Exporting from QV to Excel

mestevens499 — Sat, 16 Nov 2024 20:39:20 GMT

Hi All,

I am currently using version 12 of QlikView, where I am attempting to export 7 tables at the same time from an application to individual tabs within an Excel workbook.

I am new to the world of macro building from within QV. I am looking to build a macro that allows all 7 tables I have within one application to be exported at the same time to individual tabs within Excel and would like to see what advice / simple layman's term of building one is out there in the community.

I have been researching how best to go about building such a macro by checking in with various forums such as QlikCommunity for tips and advice.

Any help or advice would be greatly appreciated please.

Thanks and best wishes, Mike.

Re: Multiple Tables Exporting from QV to Excel

Frank_Hartmann — Mon, 17 Jun 2019 12:04:43 GMT

something like this should work:

Sub ExportToExcel Set doc = ActiveDocument Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True Set objWorkbook = objExcel.Workbooks.Add Set objSheet = objWorkbook.Sheets.Add objSheet.Name = "1" 'Give your Exceltabs a name set obj = ActiveDocument.GetSheetObject("CH01") 'Paste here the correct Qlikview Object-ID Set ASheet = objExcel.ActiveSheet ASheet.Application.DisplayAlerts = False ASheet.Range("A1").Select obj.CopyTableToClipboard true ASheet.Paste Set objSheet = objWorkbook.Sheets.Add objSheet.Name = "2" 'Give your Exceltabs a name set obj = ActiveDocument.GetSheetObject("CH02") 'Paste here the correct Qlikview Object-ID Set ASheet = objExcel.ActiveSheet ASheet.Application.DisplayAlerts = False ASheet.Range("A1").Select obj.CopyTableToClipboard true ASheet.Paste Set objSheet = objWorkbook.Sheets.Add objSheet.Name = "3" 'Give your Exceltabs a name set obj = ActiveDocument.GetSheetObject("CH03") 'Paste here the correct Qlikview Object-ID Set ASheet = objExcel.ActiveSheet ASheet.Application.DisplayAlerts = False ASheet.Range("A1").Select obj.CopyTableToClipboard true ASheet.Paste Set objSheet = objWorkbook.Sheets.Add objSheet.Name = "4" 'Give your Exceltabs a name set obj = ActiveDocument.GetSheetObject("CH04") 'Paste here the correct Qlikview Object-ID Set ASheet = objExcel.ActiveSheet ASheet.Application.DisplayAlerts = False ASheet.Range("A1").Select obj.CopyTableToClipboard true ASheet.Paste Set objSheet = objWorkbook.Sheets.Add objSheet.Name = "5" 'Give your Exceltabs a name set obj = ActiveDocument.GetSheetObject("CH05") 'Paste here the correct Qlikview Object-ID Set ASheet = objExcel.ActiveSheet ASheet.Application.DisplayAlerts = False ASheet.Range("A1").Select obj.CopyTableToClipboard true ASheet.Paste Set objSheet = objWorkbook.Sheets.Add objSheet.Name = "6" 'Give your Exceltabs a name set obj = ActiveDocument.GetSheetObject("CH06") 'Paste here the correct Qlikview Object-ID Set ASheet = objExcel.ActiveSheet ASheet.Application.DisplayAlerts = False ASheet.Range("A1").Select obj.CopyTableToClipboard true ASheet.Paste Set objSheet = objWorkbook.Sheets.Add objSheet.Name = "7" 'Give your Exceltabs a name set obj = ActiveDocument.GetSheetObject("CH07") 'Paste here the correct Qlikview Object-ID Set ASheet = objExcel.ActiveSheet ASheet.Application.DisplayAlerts = False ASheet.Range("A1").Select obj.CopyTableToClipboard true ASheet.Paste End sub

Re: Multiple Tables Exporting from QV to Excel

mestevens499 — Wed, 19 Jun 2019 19:58:33 GMT

Hi Frank,

First of all, this is brilliant and very helpful/useful to me. Thank you for taking the time to assist me in getting to grips with this.

Following on from and seeing how the tables are exported and laid out in Excel workbooks, I now have a few additional queries / ambitions, which I’m hoping you will be able to answer for me or to point me in the right direction of.

My ambitions / queries are as follows:

When the table are exported to Excel, they appear to retain the text wrap around. I’ve attempted to unmark the checkbox for Multiline settings – Wrap text (within the Presentation tab of table Properties). But this appears not to work. The only other alternative would be to apply maximum column widths for all columns of every single table exported. Is there a way in which this can be applied to / added into the macro itself?
I would also like to be able to adjust the text size and font type of every single table when it exports to Excel? I understand there is a way of achieving this in macro form, but I’m not sure how best to go about this.
If I wanted to remove any blank sheets from an Excel workbook, could I embed a command from within the QlikView macro or is this not possible to achieve and I would therefore need to remove unwanted sheets from within Excel?
Finally, if I wanted to add heading(s) along with each table export to Excel is this achievable from within QlikView macros? Would you be able to demonstrate for me how I would need to be able to apply this command structure from within QlikView please?

Once again, many thanks for your help on this. It’s greatly appreciated.

Mike.

Re: Multiple Tables Exporting from QV to Excel

Frank_Hartmann — Thu, 20 Jun 2019 19:41:37 GMT

See attached macro for changes.

I have commented the interesting lines of code!

just adapt the ranges/cells according your needs

the loop before the end sub statement will remove the empty Worksheets

hope this helps

Sub ExportToExcel Set doc = ActiveDocument Set objExcel = CreateObject("Excel.Application") with objExcel .Visible = True End with Set objWorkbook = objExcel.Workbooks.Add Set objSheet = objWorkbook.Sheets.Add objSheet.Name = "1" set obj = ActiveDocument.GetSheetObject("CH01") Set ASheet = objExcel.ActiveSheet ASheet.Application.DisplayAlerts = False obj.CopyTableToClipboard true chartCaption = obj.GetCaption.Name.v 'Get Caption of Table ASheet.Range("A1") = chartCaption 'Insert Caption/ChartTitle to A1 ASheet.Range("A2").Select 'Select A2 ASheet.Paste 'paste QV Table to A2 ASheet.Range("A1:B3").Font.Name = "Arial" 'Set Font to Arial ASheet.Range("A1").Font.Bold = True 'Set Font Bold ASheet.Range("A1").Font.Size = 16 'Set Fontsize ASheet.Range("A1").Font.Underline = True 'Set Underline ASheet.Range("A1:A3").Select With ASheet.Columns("A:L") .WrapText = False 'This prevents the text to be wrapped .Orientation = 0 'Maybe helpful setting .AddIndent = False 'Maybe helpful setting .IndentLevel = 0 'Maybe helpful setting .ShrinkToFit = False 'Maybe helpful setting .MergeCells = False 'Maybe helpful setting End With ASheet.Cells(1,1).HorizontalAlignment = -4108 'Set Alignment: -4107 --> xlVAlignBottom,-4108 --> Center,-4117 --> Distributed,-4130 -->Justify,-4160 --> Top ASheet.Columns("A:L").EntireColumn.AutoFit 'Autofits the columnwidth ' Set objSheet = objWorkbook.Sheets.Add ' objSheet.Name = "2" ' set obj = ActiveDocument.GetSheetObject("CH02") ' Set ASheet = objExcel.ActiveSheet ' ASheet.Application.DisplayAlerts = False ' ASheet.Range("A1").Select ' obj.CopyTableToClipboard true ' chartCaption = obj.GetCaption.Name.v 'Get Caption of Table ' ASheet.Range("A1") = chartCaption ' ASheet.Range("A2").Select ' ASheet.Paste ' ASheet.Range("A1:A3").Font.Name = "Arial" ' ASheet.Range("A1").Font.Bold = True ' ASheet.Range("A1").Font.Size = 16 ' ASheet.Range("A1").Font.Underline = True ' ' Set objSheet = objWorkbook.Sheets.Add ' objSheet.Name = "3" ' set obj = ActiveDocument.GetSheetObject("CH03") ' Set ASheet = objExcel.ActiveSheet ' ASheet.Application.DisplayAlerts = False ' ASheet.Range("A1").Select ' obj.CopyTableToClipboard true ' chartCaption = obj.GetCaption.Name.v 'Get Caption of Table ' ASheet.Range("A1") = chartCaption ' ASheet.Range("A2").Select ' ASheet.Paste ' ASheet.Range("A1:A3").Font.Name = "Arial" ' ASheet.Range("A1").Font.Bold = True ' ASheet.Range("A1").Font.Size = 16 ' ASheet.Range("A1").Font.Underline = True ' ' Set objSheet = objWorkbook.Sheets.Add ' objSheet.Name = "4" ' set obj = ActiveDocument.GetSheetObject("CH04") ' Set ASheet = objExcel.ActiveSheet ' ASheet.Application.DisplayAlerts = False ' ASheet.Range("A1").Select ' obj.CopyTableToClipboard true ' chartCaption = obj.GetCaption.Name.v 'Get Caption of Table ' ASheet.Range("A1") = chartCaption ' ASheet.Range("A2").Select ' ASheet.Paste ' ASheet.Range("A1:A3").Font.Name = "Arial" ' ASheet.Range("A1").Font.Bold = True ' ASheet.Range("A1").Font.Size = 16 ' ASheet.Range("A1").Font.Underline = True ' ' Set objSheet = objWorkbook.Sheets.Add ' objSheet.Name = "5" ' set obj = ActiveDocument.GetSheetObject("CH05") ' Set ASheet = objExcel.ActiveSheet ' ASheet.Application.DisplayAlerts = False ' ASheet.Range("A1").Select ' obj.CopyTableToClipboard true ' chartCaption = obj.GetCaption.Name.v 'Get Caption of Table ' ASheet.Range("A1") = chartCaption ' ASheet.Range("A2").Select ' ASheet.Paste ' ASheet.Range("A1:A3").Font.Name = "Arial" ' ASheet.Range("A1").Font.Bold = True ' ASheet.Range("A1").Font.Size = 16 ' ASheet.Range("A1").Font.Underline = True ' ' Set objSheet = objWorkbook.Sheets.Add ' objSheet.Name = "6" ' set obj = ActiveDocument.GetSheetObject("CH06") ' Set ASheet = objExcel.ActiveSheet ' ASheet.Application.DisplayAlerts = False ' ASheet.Range("A1").Select ' obj.CopyTableToClipboard true ' chartCaption = obj.GetCaption.Name.v 'Get Caption of Table ' ASheet.Range("A1") = chartCaption ' ASheet.Range("A2").Select ' ASheet.Paste ' ASheet.Range("A1:A3").Font.Name = "Arial" ' ASheet.Range("A1").Font.Bold = True ' ASheet.Range("A1").Font.Size = 16 ' ASheet.Range("A1").Font.Underline = True ' ' Set objSheet = objWorkbook.Sheets.Add ' objSheet.Name = "7" ' set obj = ActiveDocument.GetSheetObject("CH07") ' Set ASheet = objExcel.ActiveSheet ' ASheet.Application.DisplayAlerts = False ' ASheet.Range("A1").Select ' obj.CopyTableToClipboard true ' chartCaption = obj.GetCaption.Name.v 'Get Caption of Table ' ASheet.Range("A1") = chartCaption ' ASheet.Range("A2").Select ' ASheet.Paste ' ASheet.Range("A1:A3").Font.Name = "Arial" ' ASheet.Range("A1").Font.Bold = True ' ASheet.Range("A1").Font.Size = 16 ' ASheet.Range("A1").Font.Underline = True ' ASheet.Paste 'The following code will remove the empty excel tabs Dim ws Dim wb objExcel.DisplayAlerts = False For Each ws In objExcel.Worksheets If objExcel.CountA(ws.Cells) = 0 Then ws.Delete next End sub