topic Re: Macro is not adding new tab to work sheet in QlikView

Macro is not adding new tab to work sheet

qlikviewwizard — Wed, 02 Mar 2016 03:47:44 GMT

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

tamilarasu — Wed, 02 Mar 2016 03:58:10 GMT

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 — Wed, 02 Mar 2016 04:14:03 GMT

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

tamilarasu — Wed, 02 Mar 2016 04:21:03 GMT

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