Method 'Range' of object Global failed.

[marilyn]

I am encountered this message in the following code:

Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRange As Excel.Range

Set oExcel = CreateObject("Excel.Application")
IF FileExists(strFnameIn) Then
Set oBook = oExcel.Workbooks.Open(strFnameIn)
Else
Set oBook = oExcel.Workbooks.Add
oBook.SaveAs strFnameIn
End If

'Format numeric columns to currency format:
'Column 4-7:
Set oRange = Range("D:G")
With oRange
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End With
'Change Column 1 format to text:
Set oRange = Range("A:A")
With oRange
.NumberFormat = "@"
end with

The first time I run the code, everything runs okay. When
I re-run the code I encounter the error on line:

Set oRange = (Range("D:G")

Thank you for all of your help.

 

[Van T. Dinh]

Try

Set oRange = oBook.Range("D:G")

and later:

Set oRange = oBook.Range("A:A")