Method 'Range' of object '_Global' failed

[Bill R via AccessMonster.com]

I get this Run-Time Error 1004 whenevery the following code runs:

On Error GoTo XLSheet2
Set objXL = CreateObject("Excel.Application")
With objXL
Set objWkb = .Workbooks.Open(strPath)
With objWkb
Set objSht1 = .Worksheets("Actual_Releases_by_Week")
With objSht1
If IsNull(.Range("A2")) Or .Range("A2") = "" Then GoTo
XLSheet2
Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("A2").Select
End With
XLSheet2:
On Error GoTo 0
On Error GoTo XLTransfer
Set objSht2 = .Worksheets("Drawing_Recovery_by_Week")
With objSht2
If IsNull(.Range("A2")) Or .Range("A2") = "" Then GoTo
XLTransfer
'************************************************* ***********************************************
HERE'S WHERE THE ERROR OCCURS...
.Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("A2").Select
End With
.Save
XLTransfer:
.Close (True)
End With
.Quit
End With
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
DoCmd.TransferSpreadsheet acExport, 5, "Actual Releases by Week", strPath,
True, ""
DoCmd.TransferSpreadsheet acExport, 5, "Drawing Recovery by Week",
strPath, True, ""
MsgBox "Successfully updated " & strFile, vbInformation + vbOKOnly, _
"Release Chart Data Update"
End If
Set objXL = New Excel.Application
objXL.Visible = True
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(strPath)
End With
Set objWkb = Nothing
Set objXL = Nothing

The strangest thing about it is that the 1st error handler works and shoots
the code down to "XLSheet2:"
But at the point where I get the msg, the error handler is ignored and I get
a run-time error 1004. As you can see, I've tried to close the workbook and
reset the objects in order to open it for the user, as opposed to having him
hunt for it in a directory on the server. Obviously, if he's gone to all the
trouble to update the data in the chart, he'd probably like to take a look at
it. When the code 1st runs, I check in Task Manager and Excel is not running
until my code sets the objXL. Pls advise.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1

[David Lloyd]

Bill:

You need to activate objSht2 before selecting a range on the sheet. If you
add the following line, the error does not occur:

objsht2.Activate 'Add this line before the following

HERE'S WHERE THE ERROR OCCURS...
.Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
"Bill R via AccessMonster.com" <fo***@AccessMonster.com> wrote in message
news:51***********@AccessMonster.com...
I get this Run-Time Error 1004 whenevery the following code runs:

On Error GoTo XLSheet2
Set objXL = CreateObject("Excel.Application")
With objXL
Set objWkb = .Workbooks.Open(strPath)
With objWkb
Set objSht1 = .Worksheets("Actual_Releases_by_Week")
With objSht1
If IsNull(.Range("A2")) Or .Range("A2") = "" Then GoTo
XLSheet2
Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("A2").Select
End With
XLSheet2:
On Error GoTo 0
On Error GoTo XLTransfer
Set objSht2 = .Worksheets("Drawing_Recovery_by_Week")
With objSht2
If IsNull(.Range("A2")) Or .Range("A2") = "" Then GoTo
XLTransfer
'************************************************* ***********************************************
HERE'S WHERE THE ERROR OCCURS...
.Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("A2").Select
End With
.Save
XLTransfer:
.Close (True)
End With
.Quit
End With
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
DoCmd.TransferSpreadsheet acExport, 5, "Actual Releases by Week",
strPath,
True, ""
DoCmd.TransferSpreadsheet acExport, 5, "Drawing Recovery by Week",
strPath, True, ""
MsgBox "Successfully updated " & strFile, vbInformation + vbOKOnly, _
"Release Chart Data Update"
End If
Set objXL = New Excel.Application
objXL.Visible = True
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(strPath)
End With
Set objWkb = Nothing
Set objXL = Nothing

The strangest thing about it is that the 1st error handler works and shoots
the code down to "XLSheet2:"
But at the point where I get the msg, the error handler is ignored and I get
a run-time error 1004. As you can see, I've tried to close the workbook and
reset the objects in order to open it for the user, as opposed to having him
hunt for it in a directory on the server. Obviously, if he's gone to all the
trouble to update the data in the chart, he'd probably like to take a look
at
it. When the code 1st runs, I check in Task Manager and Excel is not running
until my code sets the objXL. Pls advise.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1

[Bill R via AccessMonster.com]

David,

Thanks a million!

BTW, do you have any idea why the error handler failed to handle the error? I
have "Break for unhandled errors" selected in tools/options.

Any suggestions for opening the workbook after the data is updated?

Thanks,

Bill

David Lloyd wrote:

Bill:

You need to activate objSht2 before selecting a range on the sheet. If you
add the following line, the error does not occur:

objsht2.Activate 'Add this line before the following

HERE'S WHERE THE ERROR OCCURS...
.Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
The strangest thing about it is that the 1st error handler works and shoots
the code down to "XLSheet2:"
But at the point where I get the msg, the error handler is ignored and I get
a run-time error 1004. As you can see, I've tried to close the workbook and
reset the objects in order to open it for the user, as opposed to having him
hunt for it in a directory on the server. Obviously, if he's gone to all the
trouble to update the data in the chart, he'd probably like to take a look
at
it. When the code 1st runs, I check in Task Manager and Excel is not running
until my code sets the objXL. Pls advise.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1

[Bill R via AccessMonster.com]

David,

Is the technique I'm using valid? I want to delete the data in the workbook
because it's conceivable that the new data might have fewer records than the
old, and I don't want to leave old records at the bottom of the table.
I've used XLCopyFromRecordset code in the past, but opted for
TransferSpreadsheet for no compelling reason. Is that a mistake?
Finally, I want to open the newly updated workbook. I think I need to close
it for the TransferSpreadsheet function to work, and then reopen it. So I
don't want to .quit the workbook, but I need to empty the variables, right?

Thanks,

Bill

David Lloyd wrote:

Bill:

You need to activate objSht2 before selecting a range on the sheet. If you
add the following line, the error does not occur:

objsht2.Activate 'Add this line before the following

HERE'S WHERE THE ERROR OCCURS...
.Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1

[David Lloyd]

Bill:

The error handler will fail to handle to error in section XLSheet2 if it was
sent to that section by a preceding error. If no error occurs prior to
section XLSheet2 then the error handling does work. My suggestion would be
to structure the procedure so that it exits in the event of an error.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
"Bill R via AccessMonster.com" <fo***@AccessMonster.com> wrote in message
news:51***********@AccessMonster.com...
David,

Thanks a million!

BTW, do you have any idea why the error handler failed to handle the error?
I
have "Break for unhandled errors" selected in tools/options.

Any suggestions for opening the workbook after the data is updated?

Thanks,

Bill

David Lloyd wrote:

Bill:

You need to activate objSht2 before selecting a range on the sheet. If you
add the following line, the error does not occur:

objsht2.Activate 'Add this line before the following

HERE'S WHERE THE ERROR OCCURS...
.Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
The strangest thing about it is that the 1st error handler works and shoots
the code down to "XLSheet2:"
But at the point where I get the msg, the error handler is ignored and I
get
a run-time error 1004. As you can see, I've tried to close the workbook and
reset the objects in order to open it for the user, as opposed to having
him
hunt for it in a directory on the server. Obviously, if he's gone to all
the
trouble to update the data in the chart, he'd probably like to take a look
at
it. When the code 1st runs, I check in Task Manager and Excel is not
running
until my code sets the objXL. Pls advise.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1