Multiple Tables Exporting from QV to Excel

[mestevens499]

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.

[Frank_Hartmann]

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 

[mestevens499]

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:

1. 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?
2. 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.
   
   
3. 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?
   
   
4. 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.

[Frank_Hartmann · Accepted solution]

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