Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Order Tabs in Excel Extract of Qlikview

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

1 Solution

Accepted Solutions
Master II
Master II

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"))


View solution in original post

2 Replies
Master II
Master II

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"))


View solution in original post

Highlighted
Contributor III
Contributor III

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