Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewwizard
Master II
Master II

Macro is not adding new tab to work sheet

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

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

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

View solution in original post

3 Replies
tamilarasu
Champion
Champion

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.

jagan
Luminary Alumni
Luminary Alumni

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.

tamilarasu
Champion
Champion

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