Can´t export 4 or more sheets in excel book

[#1]

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

[Kushal_Chawda · Accepted solution]

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.

[ecolomer]

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.

[#4]

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. 

1.      Get the option's current setting,
2.      change it to the numbers of sheets you'll need,
3.      add the new workbook,
4.      then restore the option to its original setting.

Excel.Application.SheetsInNewWorkbook = 4

There's a VBA example at Start empty Excel workbook without any worksheets - Stack Overflow