Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone out there, im new in qlikview, I was doing export stuff and everything works fine when i only export 3 sheets into 1 book, the problem came when i was trying to export 4 sheets or more, i suppossed the reason was that a excel books has 3 sheets default when you open a normal excel file, so my question is how to export 4 or more sheets into a book, by the way i am only exporting data from straight tables, here is the code of the macro im using :
Sub ExportAllData
set XLApp = CreateObject("Excel.Application")
XLApp.Visible = True 'Visible set as true
set XLDoc = XLApp.Workbooks.Add
''''''''''''''''''''''''''''''''''''
set XLSheet = XLDoc.Worksheets(1)
ActiveDocument.Sheets("Inversiones Bancarias").Activate
set table1 = ActiveDocument.GetSheetObject("TB04")
table1.CopyTableToClipboard true
XLSheet.Paste XLSheet.Range("A1")
XLSheet.Name = "Inversiones Bancarias"
set Selection = XLDoc.Sheets(1).Columns("A:N")
With Selection
.Borders.ColorIndex = 0
.EntireRow.RowHeight = 12.75
.AutoFit
End With
''''''''''''''''''''''''''''''''''
set XLSheet2 = XLDoc.Worksheets(2)
ActiveDocument.Sheets("Otros Instrumentos").Activate
set table2 = ActiveDocument.GetSheetObject("TB08")
table2.CopyTableToClipboard true
XLSheet2.Paste XLSheet2.Range("A1")
XLSheet2.Name = "Otros Instrumentos"
set Selection = XLDoc.Sheets(2).Columns("A:N")
With Selection
.Borders.ColorIndex = 0
.EntireRow.RowHeight = 12.75
.AutoFit
End With
''''''''''''''''''''''''''''''''''
set XLSheet3 = XLDoc.Worksheets(3)
ActiveDocument.Sheets("Fondeos").Activate
set table3 = ActiveDocument.GetSheetObject("TB05")
table3.CopyTableToClipboard true
XLSheet3.Paste XLSheet3.Range("A1")
XLSheet3.Name = "Fondeos"
set Selection = XLDoc.Sheets(3).Columns("A:N")
With Selection
.Borders.ColorIndex = 0
.EntireRow.RowHeight = 12.75
.AutoFit
End With
''''''''''''''''''''''''''''''''''
set XLSheet4 = XLDoc.Worksheets(4)
ActiveDocument.Sheets("CompraVenta Dolares").Activate
set table4 = ActiveDocument.GetSheetObject("TB07")
table4.CopyTableToClipboard true
XLSheet4.Paste XLSheet4.Range("A1")
XLSheet4.Name = "CompraVenta Dolares"
set Selection = XLDoc.Sheets(4).Columns("A:N")
With Selection
.Borders.ColorIndex = 0
.EntireRow.RowHeight = 12.75
.AutoFit
End With
'''''''''''''''''''''''''
End Sub
Dear Jairo,
There is trick for this. By default when you open excel you will have only 3 sheets, however you can change this sheet option to more than 3 sheets.
Please find the below screenshot to set the option. For office 2010, open Microsoft Excel 2010, In File tab select option
After doing the settings macro will work.
Dear Jairo,
There is trick for this. By default when you open excel you will have only 3 sheets, however you can change this sheet option to more than 3 sheets.
Please find the below screenshot to set the option. For office 2010, open Microsoft Excel 2010, In File tab select option
After doing the settings macro will work.
El problema es que por defecto el libro de excel te abre solo tres hojas.
Puedes cambiar el valor por defecto en excel o controlar cuantas hojas abiertas hay desde la macro y si no tienes suficientes, abrir nuevas antes de exportar.
Depending on this setting to stay the same across multiple user workstations is risky. (I normally change that setting to 1. It saves having to verify the extra Sheet(2) & Sheet(3) are blank when exchanging Workbooks. Besides, occasionally adding sheets is faster than constantly deleting two.)
It would be safer to get a Sheet count after the
set XLDoc = XLApp.Workbooks.Add
then add sheets as necessary.
Alternately, you could change the "Include this many sheets" option to the needed sheet count in your script before adding the Workbook.
Excel.Application.SheetsInNewWorkbook = 4
There's a VBA example at Start empty Excel workbook without any worksheets - Stack Overflow