3 Replies Latest reply: Mar 15, 2015 7:18 PM by Brian McCullough RSS

    Can´t export 4 or more sheets in excel book

    Jairo Ordaz

      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

        • Re: Can´t export 4 or more sheets in excel book
          kushal chawda

          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

          Sheet.jpg

           

           

          After doing the settings macro will work.

          • Re: Can´t export 4 or more sheets in excel book
            Enrique Colomer

            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.

            • Re: Can´t export 4 or more sheets in excel book

              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