Order Tabs in Excel Extract of Qlikview

schumi1980 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All

I have the following problem with my code that extracts several tables from Qlikview.

I have used at this stage three worksheets and would like to add a new worksheet AFTER sheet three. However, using the blue line in my code I only get the error message "Expected')'".

Could you tell me where I am making the mistake.

Many thanks.

Best regards,

Jan

sub exportLossRun2

'==============================================================
' File Path & Name

Path = "C:\temp\"
FileName = "Test_"
strSaveFile = Path & FileName
'==============================================================
'Open Excel

set oXL=CreateObject("Excel.Application")
oXL.visible=True
oXL.Workbooks.Add

aSheetObj=Array("Main","Summary") ' Chart ID's here

'==============================================================
for i=0 to UBound(aSheetObj)
Set oSH = oXL.ActiveSheet

num_rows = oSH.UsedRange.Rows.Count

If num_rows = 1 then
oSH.Range("A2").Select
Else
oSH.Range("A" & num_rows+4).Select
End If

Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))
obj.CopyTableToClipboard True
oSH.Paste
sCaption=obj.GetCaption.Name.v
set obj=Nothing

oSH.Cells.Select

oSH.Columns("A").ColumnWidth = 12.17
oSH.Columns("B").ColumnWidth = 12.17

If num_rows = 1 then
oSH.Range("A" & num_rows).Value = sCaption
oSH.Range("A" & num_rows).Font.Bold = True
'oSH.Range("A" & num_rows).Font.ColorIndex = 3
oSH.Range("A" & num_rows).Interior.ColorIndex = 40
Else

oSH.Range("A" & num_rows+3).Value = sCaption
oSH.Range("A" & num_rows+3).Font.Bold = True
'oSH.Range("A" & num_rows+3).Font.ColorIndex = 3
oSH.Range("A" &num_rows+3).Interior.ColorIndex = 40
End If
'oXL.Selection.Columns.AutoFit

next
'==============================================================
oSH.Range("A1").Select
' oXL.Sheets("Sheet2").Delete
' oXL.Sheets("Sheet3").Delete
oSH.Name = "Data"

oXL.Sheets("Sheet2").Activate

aSheetObj2=Array("Gross") ' Chart ID's here

'==============================================================
for i=0 to UBound(aSheetObj2)

Set oSH2 = oXL.ActiveSheet

num_rows = oSH2.UsedRange.Rows.Count

If num_rows = 1 then
oSH2.Range("A2").Select
Else
oSH2.Range("A" & num_rows+4).Select
End If

Set obj2 = ActiveDocument.GetSheetObject(aSheetObj2(i))
obj2.CopyTableToClipboard True
oSH2.Paste
sCaption2=obj2.GetCaption.Name.v
set obj2=Nothing

oSH2.Cells.Select

oSH2.Columns("A:Z").ColumnWidth = 12.17
'oSH2.Columns("B").ColumnWidth = 12.17

If num_rows = 1 then
oSH2.Range("A" & num_rows).Value = sCaption
oSH2.Range("A" & num_rows).Font.Bold = True
'oSH.Range("A" & num_rows).Font.ColorIndex = 3
oSH2.Range("A" & num_rows).Interior.ColorIndex = 40
Else

oSH2.Range("A" & num_rows+3).Value = sCaption
oSH2.Range("A" & num_rows+3).Font.Bold = True
'oSH.Range("A" & num_rows+3).Font.ColorIndex = 3
oSH2.Range("A" &num_rows+3).Interior.ColorIndex = 40
oSH2.Rows(num_rows+4).WrapText = True
'oSH2.Range("B" &num_rows+4).WrapText = True
'oSH2.Range("C" &num_rows+4).WrapText = True
End If
'oXL.Selection.Columns.AutoFit

next
'==============================================================
oSH2.Range("A1").Select
' oXL.Sheets("Sheet2").Delete
' oXL.Sheets("Sheet3").Delete
oSH2.Name = "Data2"

oXL.Sheets("Sheet3").Activate

aSheetObj3=Array("Net") ' Chart ID's here

'==============================================================
for i=0 to UBound(aSheetObj3)

Set oSH3 = oXL.ActiveSheet

num_rows = oSH3.UsedRange.Rows.Count

If num_rows = 1 then
oSH3.Range("A2").Select
Else
oSH3.Range("A" & num_rows+4).Select
End If

Set obj3 = ActiveDocument.GetSheetObject(aSheetObj3(i))
obj3.CopyTableToClipboard True
oSH3.Paste
sCaption3=obj3.GetCaption.Name.v
set obj3=Nothing

oSH3.Cells.Select

oSH3.Columns("A:Z").ColumnWidth = 12.17
'oSH2.Columns("B").ColumnWidth = 12.17

If num_rows = 1 then
oSH3.Range("A" & num_rows).Value = sCaption
oSH3.Range("A" & num_rows).Font.Bold = True
'oSH.Range("A" & num_rows).Font.ColorIndex = 3
oSH3.Range("A" & num_rows).Interior.ColorIndex = 40
Else

oSH3.Range("A" & num_rows+3).Value = sCaption
oSH3.Range("A" & num_rows+3).Font.Bold = True
'oSH.Range("A" & num_rows+3).Font.ColorIndex = 3
oSH3.Range("A" &num_rows+3).Interior.ColorIndex = 40
oSH3.Rows(num_rows+4).WrapText = True
'oSH2.Range("B" &num_rows+4).WrapText = True
'oSH2.Range("C" &num_rows+4).WrapText = True
End If
'oXL.Selection.Columns.AutoFit

next
'==============================================================
oSH3.Range("A1").Select
oSH3.Name = "Data3"

oXL.Sheets.Add(After:=oXL.Sheets("Data3"))
'oXL.Sheets("Sheet4").Activate
'
oXL.Sheets("Sheet4").Move After:= oXL.Sheets("Data3")

aSheetObj4=Array("Arrow") ' Chart ID's here

'==============================================================
for i=0 to UBound(aSheetObj4)

Set oSH4 = oXL.ActiveSheet

num_rows = oSH4.UsedRange.Rows.Count

If num_rows = 1 then
oSH4.Range("A2").Select
Else
oSH4.Range("A" & num_rows+4).Select
End If

Set obj4 = ActiveDocument.GetSheetObject(aSheetObj4(i))
obj4.CopyTableToClipboard True
oSH4.Paste
sCaption4=obj4.GetCaption.Name.v
set obj4=Nothing

oSH4.Cells.Select

oSH4.Columns("A:Z").ColumnWidth = 12.17
'oSH2.Columns("B").ColumnWidth = 12.17

If num_rows = 1 then
oSH4.Range("A" & num_rows).Value = sCaption
oSH4.Range("A" & num_rows).Font.Bold = True
'oSH.Range("A" & num_rows).Font.ColorIndex = 3
oSH4.Range("A" & num_rows).Interior.ColorIndex = 40
Else

oSH4.Range("A" & num_rows+3).Value = sCaption
oSH4.Range("A" & num_rows+3).Font.Bold = True
'oSH.Range("A" & num_rows+3).Font.ColorIndex = 3
oSH4.Range("A" &num_rows+3).Interior.ColorIndex = 40
oSH4.Rows(num_rows+4).WrapText = True
'oSH2.Range("B" &num_rows+4).WrapText = True
'oSH2.Range("C" &num_rows+4).WrapText = True
End If
'oXL.Selection.Columns.AutoFit

next
'==============================================================
oSH4.Range("A1").Select
' oXL.Sheets("Sheet2").Delete
' oXL.Sheets("Sheet3").Delete
oSH4.Name = "Data4"

oXL.ActiveWorkBook.SaveAs strSaveFile & replace(date, "/", "-") & ".xlsx"

set oSH = Nothing
set oSH2 = Nothing
set oSH3 = Nothing
set oSH4 = Nothing
set oXL=Nothing

end sub

Re: Order Tabs in Excel Extract of Qlikview

m_woolf — Wed, 22 Aug 2018 17:04:04 GMT

I believe that the "After:=" syntax won't work in vbscipt. When I google adding a worksheet in vbscript, I get:

'Add another one 
Set xlWs = xlWb.Worksheets.Add(, xlWb.Worksheets(xlWb.Sheets("SheetName"))

Re: Order Tabs in Excel Extract of Qlikview

schumi1980 — Fri, 24 Aug 2018 07:03:47 GMT

Thanks, put me on the right track, it works now.

topic Order Tabs in Excel Extract of Qlikview in QlikView

Order Tabs in Excel Extract of Qlikview

Re: Order Tabs in Excel Extract of Qlikview

Re: Order Tabs in Excel Extract of Qlikview