Error in Macro Export to Excel

[hamdaniahmad]

Hello All,

One question, does anybody know about this error? This error appear after I export in web browser and excel application open.

I use this macro to export to excel :

sub ExportExcel

'==============================================================

' File Path & Name

Path = "C:\"

FileName = "DailyReport_"

strSaveFile = Path & FileName

'==============================================================

'Open Excel

set oXL=CreateObject("Excel.Application")

oXL.visible=True

oXL.Workbooks.Add

aSheetObj=Array("CH334","CH335","CH339","CH337","CH340","CH341","CH342","CH343")  ' 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.ActiveWorkBook.SaveAs strSaveFile & replace(date, "/", "-") & ".xlsx"

set oSH = Nothing   

set oXL=Nothing

end sub

[petter]

If either of these sheets does not exist you will get the error message you got:

oXL.Sheets("Sheet2").Delete

oXL.Sheets("Sheet3").Delete

The other subscripts shouldn't go out of bounds as far as I can tell....

[vsmejkal]

Hi Ahmad

The answer is much simplier than you'd think.All arrays have a 0th member. Ubound returns the position of the last item, not the count!

Solution: for i=0 to UBound(aSheetObj)-1

Try this test:

Sub Test()

  y = Array("a", "b", "c")

  MsgBox UBound(y)

End Sub

Bottom line: Your script does run out of the range. You are looping the sheets and the last one doesn't exist.

A better way: Forget a fixed list. Loop all sheets through the FOR EACH.

Thanks, VJ

[hamdaniahmad]

Hello VJ,

Thank you for your response,

I've tried to change array into for i=0 to UBound(aSheetObj)-1

but still have same error (out of range).

I'm newbie in macro programming, can you give a sample from better way using FOR EACH?

Thank You,

Ahmad

[petter]

Ubound() returns the index of the Upper Bound ... not the number of elements in the Array.

And arrays start at 0 in VBScript. So his code is correct.

Have a look here: VBScript UBound Function

[petter]

I am pretty confident that what I suggested first is a good diagnosis.

You can test it by just adding a single quotation mark in front of the two lines:

' oXL.Sheets("Sheet2").Delete

' oXL.Sheets("Sheet3").Delete

This will comment out the lines and you can verify if the error disappears....

[hamdaniahmad]

Hello Petter,

I already add a single quotation to these 2 lines to make it comment and skipped.

The excel export is successful, but in IE appear new error, please see picture.

Thank you,

Ahmad