Method 'Range' of object '_Global' failed

  • Thread starter Thread starter Rob A.
  • Start date Start date
R

Rob A.

I'm having some trouble with some VBA code I've written to
open an Excel file (which I just transferred data to) and
create a chart based on that data so that it can be
exported to a .jpg file.

The first time I run the module, it goes through just fine
and everything works great. However, the second time I
run it, I get an error stating "Method 'Range' of
object '_Global' failed. I've tried getting rid of
the 'With' statement and explicity defining what commands
should run on each object, but that doesn't help at all.

Here's the code:

DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel8, "myDurData",_
"E:/Home/C77921/myData.xls"

Dim appExcel As Object
Dim xlbook As Object
Dim xlsheet As Object

Set appExcel = New Excel.Application
appExcel.Visible = False

Set xlbook = appExcel.Workbooks.Open("myData.xls")
Set xlsheet = appExcel.Worksheets("myDurData")

appExcel.Range("A2:A1001").Sort Key1:=Range("A2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

'... yada yada yada (chart stuff) ...

xlbook.Close
appExcel.Application.Quit


I get the error at the appExcel.Range("A2:A1001") line,
but only after I've already run the module once with
success. I'm guessing it's an initialization problem, but
I just don't have too much experience with this kind of
thing. Any help you could give me would be most
appreciated.

Thanks,

Rob A.
 
Back
Top