3 Replies Latest reply: Mar 1, 2016 11:21 PM by Tamil Nagaraj RSS

    Macro is not adding new tab to work sheet

    arjun rao

      Hi All,

       

      Macro is generating WorkReport.xlsx with Tab1 and Tab2 successfully.I want to add Tab3,Tab4 to WorkReport.xlsx.

      I am able to add Tab3 but not Tab4.

      Please help me.

      Thanks in advance.

       

      Script:

      Sub ProductExport

      Dim XLApp 'as Excel.Application

      Set XLApp = CreateObject("Excel.Application")

      Set XLDoc = XLApp.Workbooks.Add

      ActiveDocument.GetSheetObject("CH2").CopyTableToClipboard true

      XLDoc.Sheets(1).Paste()

      ActiveDocument.GetSheetObject("CH11").CopyTableToClipboard true

      XLDoc.Sheets(4).Paste()

       

      set vProductYear = ActiveDocument.Variables("vProductYear")

      set vLastYear = ActiveDocument.Variables("vLastYear")

      ActiveDocument.Fields("year").ToggleSelect vProductYear.GetContent.String

      ActiveDocument.Fields("year").ToggleSelect vLastYear.GetContent.String

      'set vLastYear = ActiveDocument.Variables("vLastYear")

      'ActiveDocument.Fields("year").ToggleSelect vLastYear.GetContent.String

      ActiveDocument.GetSheetObject("CH1").CopyTableToClipboard true

      XLDoc.Sheets(2).Paste()

       

      ActiveDocument.GetSheetObject("CH22").CopyTableToClipboard true

      XLDoc.Sheets(3).Paste()

       

      XLDoc.Sheets(2).Rows("1:60000").RowHeight = 12.75

      XLDoc.Sheets(2).Columns("A:K").EntireColumn.AutoFit

      XLDoc.Sheets(1).Rows("1:60000").RowHeight = 12.75

      XLDoc.Sheets(1).Columns("A:AG").EntireColumn.AutoFit

       

      XLDoc.Sheets(3).Rows("1:60000").RowHeight = 12.75

      XLDoc.Sheets(3).Columns("A:AG").EntireColumn.AutoFit

       

      XLDoc.Sheets(4).Rows("1:60000").RowHeight = 12.75

      XLDoc.Sheets(4).Columns("A:AG").EntireColumn.AutoFit

       

       

      Set obj = ActiveDocument.GetSheetObject("CH2")

      R = obj.GetRowCount

      Set WS = XLDoc.worksheets(1)

      WS.Range("A1:AG"&R).Borders.LineStyle = 1

      XLDoc.Sheets(1).Range("I3:AG"&R).NumberFormat = "#,##0.00"

       

      Set obj = ActiveDocument.GetSheetObject("CH11")

      R = obj.GetRowCount

      Set WS = XLDoc.worksheets(1)

      WS.Range("A1:AG"&R).Borders.LineStyle = 1

      XLDoc.Sheets(4).Range("I3:AG"&R).NumberFormat = "#,##0.00"

       

      Set obj = ActiveDocument.GetSheetObject("CH3")

      R = obj.GetRowCount

      XLDoc.Sheets(2).Name = "Tab1"

      XLDoc.Sheets(1).Name = "Tab2"

      XLDoc.Sheets(3).Name = "Tab3"

      XLDoc.Sheets(4).Name = "Tab4"

       

      XLApp.DisplayAlerts = false

      Set WS = XLDoc.worksheets(1)

      ws.SaveAS "C:\QlikviewOutput\WorkReport.xlsx"

      XLApp.DisplayAlerts = true

      XLDoc.close

       

      XLApp.quit

      set XLDoc = nothing

       

      End sub

        • Re: Macro is not adding new tab to work sheet
          Tamil Nagaraj

          Hi Suren,

           

          When you open a new excel file, you will see 3 worksheets (By default). You need to add worksheet, so that you can paste the copied chart into the 4th sheet. You need to add a line like XLDoc.Worksheets.Add().

           

          I have attached the code in text file. Let me know.

          • Re: Macro is not adding new tab to work sheet
            jagan mohan rao appala

            Hi,

             

            As Tamil Nagaraj said there will be default 3 sheets when you create a new file.  That is why when you are copying objects in 4th sheet you are getting error.  Try adding this

             

            Set XLDoc = XLApp.Workbooks.Add

            XLDoc.Worksheets.Add()


            Hope this helps you.


            Regards,

            jagan.

            • Re: Macro is not adding new tab to work sheet
              Tamil Nagaraj

              Your code is not complete and references seems wrong. I just modified your code a bit.

               

              Sub ProductExport

              Dim XLApp 'as Excel.Application

              Set XLApp = CreateObject("Excel.Application")

              Set XLDoc = XLApp.Workbooks.Add

              XLDoc.Worksheets.Add() 

               

              '********************************************************************************************

              ActiveDocument.GetSheetObject("CH2").CopyTableToClipboard true

              XLDoc.Sheets(1).Paste()

              ActiveDocument.GetSheetObject("CH1").CopyTableToClipboard true

              XLDoc.Sheets(2).Paste()

              ActiveDocument.GetSheetObject("CH22").CopyTableToClipboard true

              XLDoc.Sheets(3).Paste()

              ActiveDocument.GetSheetObject("CH11").CopyTableToClipboard true

              XLDoc.Sheets(4).Paste()

               

              '********************************************************************************************

              set vProductYear = ActiveDocument.Variables("vProductYear")

              set vLastYear = ActiveDocument.Variables("vLastYear")

              ActiveDocument.Fields("year").ToggleSelect vProductYear.GetContent.String

              ActiveDocument.Fields("year").ToggleSelect vLastYear.GetContent.String

              'set vLastYear = ActiveDocument.Variables("vLastYear")

              'ActiveDocument.Fields("year").ToggleSelect vLastYear.GetContent.String

               

              '********************************************************************************************

              XLDoc.Sheets(1).Rows("1:60000").RowHeight = 12.75

              XLDoc.Sheets(1).Columns("A:AG").EntireColumn.AutoFit

              XLDoc.Sheets(2).Rows("1:60000").RowHeight = 12.75

              XLDoc.Sheets(2).Columns("A:K").EntireColumn.AutoFit

              XLDoc.Sheets(3).Rows("1:60000").RowHeight = 12.75

              XLDoc.Sheets(3).Columns("A:AG").EntireColumn.AutoFit

              XLDoc.Sheets(4).Rows("1:60000").RowHeight = 12.75

              XLDoc.Sheets(4).Columns("A:AG").EntireColumn.AutoFit

               

              '********************************************************************************************

              Set obj = ActiveDocument.GetSheetObject("CH2")

              R = obj.GetRowCount

              Set WS = XLDoc.worksheets(1)

              WS.Range("A1:AG"&R).Borders.LineStyle = 1

              XLDoc.Sheets(1).Range("I3:AG"&R).NumberFormat = "#,##0.00"

               

               

              Set obj = ActiveDocument.GetSheetObject("CH1")

              R = obj.GetRowCount

              Set WS = XLDoc.worksheets(2)

              WS.Range("A1:AG"&R).Borders.LineStyle = 1

              XLDoc.Sheets(2).Range("I3:AG"&R).NumberFormat = "#,##0.00"

               

               

              Set obj = ActiveDocument.GetSheetObject("CH22")

              R = obj.GetRowCount

              Set WS = XLDoc.worksheets(3)

              WS.Range("A1:AG"&R).Borders.LineStyle = 1

              XLDoc.Sheets(3).Range("I3:AG"&R).NumberFormat = "#,##0.00"

               

              Set obj = ActiveDocument.GetSheetObject("CH11")

              R = obj.GetRowCount

              Set WS = XLDoc.worksheets(4)

              WS.Range("A1:AG"&R).Borders.LineStyle = 1

              XLDoc.Sheets(4).Range("I3:AG"&R).NumberFormat = "#,##0.00"


              '********************************************************************************************

              XLDoc.Sheets(1).Name = "Tab1"

              XLDoc.Sheets(2).Name = "Tab2"

              XLDoc.Sheets(3).Name = "Tab3"

              XLDoc.Sheets(4).Name = "Tab4"

               

              XLApp.DisplayAlerts = false

              Set WS = XLDoc.worksheets(1)

              ws.SaveAS "C:\QlikviewOutput\WorkReport.xlsx"

              XLApp.DisplayAlerts = true

              XLDoc.close

               

              XLApp.quit

              set XLDoc = nothing

               

              End sub