Unexpected error 1004 when using workbooks.open

[AHD]

I have what I thought was a fairly simple macro. This macro runs fine if
I step thru it in debug but gives me an error 1004 if I launch it from a
button from the sheet. Does anybody have any ideas? (I'm running Excel
2003 sp1 on windows XP pro 2002 sp1)

Sub PublishToIntranet()
'
'
'
On Error GoTo Err_Handler_Publish_To_Intranet
Range("A1:Q61").Select
Selection.Copy
ChDir "\\lrfp3\psm\intranetDocuments\dryer\dailyPlan"
workbooks.Open Filename:= _
"\\lrfp3\psm\IntranetDocuments\Dryer\DailyPlan\DailyPlan.xls"
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
MsgBox "Page published to Intranet."
Exit Sub
Err_Handler_Publish_To_Intranet:
MsgBox "Unexpected error " & Err.Number & " " & Err.Description &
". Page not published. Please contact CIS", vbOKOnly
End Sub

 

[Nigel]

I suspect you need to change the button on the sheet property
TakeFocusOnClick to False

 

[Dave Peterson]

I think that xl97 bug was fixed in xl2k and the AHD said he(she?) was running
xl2003.

 

[Dave Peterson]

I'd comment out the "on error..." line and run the code to find out the line
causing the damage.

chdir
won't work for UNC paths, but I'm not sure if it'll blow up either. In any
case, this line can be removed:

ChDir "\\lrfp3\psm\intranetDocuments\dryer\dailyPlan"

=======
There are some things that clear the clipboard. In my simple testing, opening a
workbook left it alone, but maybe that's not always the case (if there's a
workbook_open even that's running).

I'd change the order of a couple of things.

Option Explicit
Sub PublishToIntranet2()

Dim CurWks As Worksheet
Dim newWks As Worksheet
Set CurWks = ActiveSheet

On Error GoTo Err_Handler_Publish_To_Intranet

Workbooks.Open _
Filename:="\\lrfp3\psm\IntranetDocuments\Dryer\DailyPlan\DailyPlan.xls"
Set newWks = ActiveSheet

CurWks.Range("a1:q61").Copy
newWks.Range("a1").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False
newWks.Parent.Close savechanges:=True
MsgBox "Page published to Intranet."

Exit Sub

Err_Handler_Publish_To_Intranet:

MsgBox "Unexpected error " & Err.Number & " " _
& Err.Description & ". Page not published. Please contact CIS", _
vbOKOnly

End Sub

 

[AHD]

I went with the suggestion of opening the destination excel first and
then bringing focus back to the source book. That seemed to have taken
care of the problem for me. Thank you so much for the help (and yes,
you were helping "her" not "him" )
Thanks again!!! Great Forum!!!
Anja

 

[Dave Peterson]

Glad you got it working, ma'am! <vbg>.

I went with the suggestion of opening the destination excel first and
then bringing focus back to the source book. That seemed to have taken
care of the problem for me. Thank you so much for the help (and yes,
you were helping "her" not "him" )
Thanks again!!! Great Forum!!!
Anja