Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Arjunarao
Honored Contributor 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

Re: Macro is not adding new tab to work sheet

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

3 Replies

Re: Macro is not adding new tab to work sheet

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.

MVP
MVP

Re: Macro is not adding new tab to work sheet

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.

Re: Macro is not adding new tab to work sheet

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

Community Browser