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
