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("E xcel.Applicatio n")
With objXL
Set objWkb = .Workbooks.Open (strPath)
With objWkb
Set objSht1 = .Worksheets("Ac tual_Releases_b y_Week")
With objSht1
If IsNull(.Range(" A2")) Or .Range("A2") = "" Then GoTo
XLSheet2
Range("A2:B2"). Select
Range(Selection , Selection.End(x lDown)).Select
Selection.Delet e Shift:=xlUp
Range("A2").Sel ect
End With
XLSheet2:
On Error GoTo 0
On Error GoTo XLTransfer
Set objSht2 = .Worksheets("Dr awing_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(x lDown)).Select
Selection.Delet e Shift:=xlUp
Range("A2").Sel ect
End With
.Save
XLTransfer:
.Close (True)
End With
.Quit
End With
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
DoCmd.TransferS preadsheet acExport, 5, "Actual Releases by Week", strPath,
True, ""
DoCmd.TransferS preadsheet acExport, 5, "Drawing Recovery by Week",
strPath, True, ""
MsgBox "Successful ly updated " & strFile, vbInformation + vbOKOnly, _
"Release Chart Data Update"
End If
Set objXL = New Excel.Applicati on
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.c om
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.Activat e 'Add this line before the following

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

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

On Error GoTo XLSheet2
Set objXL = CreateObject("E xcel.Applicatio n")
With objXL
Set objWkb = .Workbooks.Open (strPath)
With objWkb
Set objSht1 = .Worksheets("Ac tual_Releases_b y_Week")
With objSht1
If IsNull(.Range(" A2")) Or .Range("A2") = "" Then GoTo
XLSheet2
Range("A2:B2"). Select
Range(Selection , Selection.End(x lDown)).Select
Selection.Delet e Shift:=xlUp
Range("A2").Sel ect
End With
XLSheet2:
On Error GoTo 0
On Error GoTo XLTransfer
Set objSht2 = .Worksheets("Dr awing_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(x lDown)).Select
Selection.Delet e Shift:=xlUp
Range("A2").Sel ect
End With
.Save
XLTransfer:
.Close (True)
End With
.Quit
End With
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
DoCmd.TransferS preadsheet acExport, 5, "Actual Releases by Week",
strPath,
True, ""
DoCmd.TransferS preadsheet acExport, 5, "Drawing Recovery by Week",
strPath, True, ""
MsgBox "Successful ly updated " & strFile, vbInformation + vbOKOnly, _
"Release Chart Data Update"
End If
Set objXL = New Excel.Applicati on
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.c om
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.Activa te 'Add this line before the following

HERE'S WHERE THE ERROR OCCURS...
.Range("A2:B2") .Select
Range(Selection , Selection.End(x lDown)).Select
Selection.Delet e 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.c om
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 XLCopyFromRecor dset code in the past, but opted for
TransferSpreads heet 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 TransferSpreads heet 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.Activa te 'Add this line before the following

HERE'S WHERE THE ERROR OCCURS...
.Range("A2:B2") .Select
Range(Selection , Selection.End(x lDown)).Select
Selection.Delet e Shift:=xlUp

--
Message posted via AccessMonster.c om
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.c om" <fo***@AccessMo nster.com> wrote in message
news:51******** ***@AccessMonst er.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.Activa te 'Add this line before the following

HERE'S WHERE THE ERROR OCCURS...
.Range("A2:B2") .Select
Range(Selection , Selection.End(x lDown)).Select
Selection.Delet e 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.c om
http://www.accessmonster.com/Uwe/For...ccess/200507/1