Unexpected error 1004 when using workbooks.open

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

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

--
Cheers
Nigel



"AHD" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> AHD
> ------------------------------------------------------------------------
> AHD's Profile:
http://www.excelforum.com/member.php...o&userid=24033
> View this thread: http://www.excelforum.com/showthread...hreadid=376445
>

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




Nigel wrote:
>
> I suspect you need to change the button on the sheet property
> TakeFocusOnClick to False
>
> --
> Cheers
> Nigel
>
> "AHD" <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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
> >
> >
> > --
> > AHD
> > ------------------------------------------------------------------------
> > AHD's Profile:
> http://www.excelforum.com/member.php...o&userid=24033
> > View this thread: http://www.excelforum.com/showthread...hreadid=376445
> >

--

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 wrote:
>
> 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
>
> --
> AHD
> ------------------------------------------------------------------------
> AHD's Profile: http://www.excelforum.com/member.php...o&userid=24033
> View this thread: http://www.excelforum.com/showthread...hreadid=376445

--

Dave Peterson

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

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

AHD wrote:
>
> 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
>
> --
> AHD
> ------------------------------------------------------------------------
> AHD's Profile: http://www.excelforum.com/member.php...o&userid=24033
> View this thread: http://www.excelforum.com/showthread...hreadid=376445

--

Dave Peterson