Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
Hi,
As TamilArasu 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.
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