7/21/2007

Thank you in advance for your assistance. I think I may have posted this t
the wrong Excel Newsgroup

I have a two computer network. Files from Computer #1 are backed up to
Computer #2.
On both computers I'm running XP Pro, with Office 2003. Excel on Computer#1
has 2 toolbars with macro buttons one for formatting routines I use
frequently, the other has macro buttons that I've assigned macros that open
workbooks on computer # 1 in the FILES folder. I need to use computer # 2
regularly. Excel on it has the same two toolbars. I want to open workbooks
on computer #1 to avoid problems with updating(Do I update #2 to #1 or #1 to
#2, in other words).

I've created a new personal.xls called, no2.xls, it's stored in the
Office11\XLStart folder. The formatting macros work fine on #2. I've
recorded the Workbook opening macros so that the VBA would be correct.
However, when I click a workbook button on #2 I get this error. Compile
error: Method or data member not found. workbooks.open filename: _ is always
highlighted by the debugger. and always the word open. This is a sample
macro. I've use it with and without the ChDir statement. no change. I have
re-recorded every workbook macro hoping to solve this, and Goggled for an
answer
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sub BUDGET2007()
'
' BUDGET2007 Macro
' Macro recorded 7/21/2007 by Bob Felmore'

'
ChDir "\\Felmore-15e49b7\FILES (F)\FILES\$$$\CORE"
Workbooks.Open Filename:="\\Felmore-15e49b7\FILES
(F)\FILES\$$$\CORE\BUDGET2007.XLS", UpdateLinks:=0 [I would like to update
links, but doing this produced an error also]
End Sub
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Computer #1 is named Felmore, #2 is Robert. A pseudonym of mine has been Bob
Felmore Quality Used Cars and Trinkets.
Why does the macro keep erroring out? How should it be written?
Thank you once again for your assistance

Brian Day

First, you don't need the ChDir line. Delete that.

Then I'd bet that you have a typo in that name of the workbook.

Maybe record a macro one more time to see the difference in spelling????


Brian Day wrote:
>
> 7/21/2007
>
> Thank you in advance for your assistance. I think I may have posted this t
> the wrong Excel Newsgroup
>
> I have a two computer network. Files from Computer #1 are backed up to
> Computer #2.
> On both computers I'm running XP Pro, with Office 2003. Excel on Computer#1
> has 2 toolbars with macro buttons one for formatting routines I use
> frequently, the other has macro buttons that I've assigned macros that open
> workbooks on computer # 1 in the FILES folder. I need to use computer # 2
> regularly. Excel on it has the same two toolbars. I want to open workbooks
> on computer #1 to avoid problems with updating(Do I update #2 to #1 or #1 to
> #2, in other words).
>
> I've created a new personal.xls called, no2.xls, it's stored in the
> Office11\XLStart folder. The formatting macros work fine on #2. I've
> recorded the Workbook opening macros so that the VBA would be correct.
> However, when I click a workbook button on #2 I get this error. Compile
> error: Method or data member not found. workbooks.open filename: _ is always
> highlighted by the debugger. and always the word open. This is a sample
> macro. I've use it with and without the ChDir statement. no change. I have
> re-recorded every workbook macro hoping to solve this, and Goggled for an
> answer
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Sub BUDGET2007()
> '
> ' BUDGET2007 Macro
> ' Macro recorded 7/21/2007 by Bob Felmore'
>
> '
> ChDir "\\Felmore-15e49b7\FILES (F)\FILES\$$$\CORE"
> Workbooks.Open Filename:="\\Felmore-15e49b7\FILES
> (F)\FILES\$$$\CORE\BUDGET2007.XLS", UpdateLinks:=0 [I would like to update
> links, but doing this produced an error also]
> End Sub
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Computer #1 is named Felmore, #2 is Robert. A pseudonym of mine has been Bob
> Felmore Quality Used Cars and Trinkets.
> Why does the macro keep erroring out? How should it be written?
> Thank you once again for your assistance
>
> Brian Day

--

Dave Peterson

I mapped the FILES drive to P: and used P:\ in Macros Adding ChDir to all
Macros. Works Fine.

"Brian Day" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 7/21/2007
>
> Thank you in advance for your assistance. I think I may have posted this
> t the wrong Excel Newsgroup
>
> I have a two computer network. Files from Computer #1 are backed up to
> Computer #2.
> On both computers I'm running XP Pro, with Office 2003. Excel on
> Computer#1 has 2 toolbars with macro buttons one for formatting routines
> I use frequently, the other has macro buttons that I've assigned macros
> that open workbooks on computer # 1 in the FILES folder. I need to use
> computer # 2 regularly. Excel on it has the same two toolbars. I want to
> open workbooks on computer #1 to avoid problems with updating(Do I update
> #2 to #1 or #1 to #2, in other words).
>
> I've created a new personal.xls called, no2.xls, it's stored in the
> Office11\XLStart folder. The formatting macros work fine on #2. I've
> recorded the Workbook opening macros so that the VBA would be correct.
> However, when I click a workbook button on #2 I get this error. Compile
> error: Method or data member not found. workbooks.open filename: _ is
> always highlighted by the debugger. and always the word open. This is a
> sample macro. I've use it with and without the ChDir statement. no change.
> I have re-recorded every workbook macro hoping to solve this, and Goggled
> for an answer
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Sub BUDGET2007()
> '
> ' BUDGET2007 Macro
> ' Macro recorded 7/21/2007 by Bob Felmore'
>
> '
> ChDir "\\Felmore-15e49b7\FILES (F)\FILES\$$$\CORE"
> Workbooks.Open Filename:="\\Felmore-15e49b7\FILES
> (F)\FILES\$$$\CORE\BUDGET2007.XLS", UpdateLinks:=0 [I would like to
> update links, but doing this produced an error also]
> End Sub
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Computer #1 is named Felmore, #2 is Robert. A pseudonym of mine has been
> Bob Felmore Quality Used Cars and Trinkets.
> Why does the macro keep erroring out? How should it be written?
> Thank you once again for your assistance
>
> Brian Day
>

If you specify the complete path in the Workbooks.open statement, you don't need
to use the chdir line.

And chdir won't work with UNC paths anyway.

I'd guess that you corrected the spelling of the filename when you modified your
code.

Brian Day wrote:
>
> I mapped the FILES drive to P: and used P:\ in Macros Adding ChDir to all
> Macros. Works Fine.
>
> "Brian Day" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > 7/21/2007
> >
> > Thank you in advance for your assistance. I think I may have posted this
> > t the wrong Excel Newsgroup
> >
> > I have a two computer network. Files from Computer #1 are backed up to
> > Computer #2.
> > On both computers I'm running XP Pro, with Office 2003. Excel on
> > Computer#1 has 2 toolbars with macro buttons one for formatting routines
> > I use frequently, the other has macro buttons that I've assigned macros
> > that open workbooks on computer # 1 in the FILES folder. I need to use
> > computer # 2 regularly. Excel on it has the same two toolbars. I want to
> > open workbooks on computer #1 to avoid problems with updating(Do I update
> > #2 to #1 or #1 to #2, in other words).
> >
> > I've created a new personal.xls called, no2.xls, it's stored in the
> > Office11\XLStart folder. The formatting macros work fine on #2. I've
> > recorded the Workbook opening macros so that the VBA would be correct.
> > However, when I click a workbook button on #2 I get this error. Compile
> > error: Method or data member not found. workbooks.open filename: _ is
> > always highlighted by the debugger. and always the word open. This is a
> > sample macro. I've use it with and without the ChDir statement. no change.
> > I have re-recorded every workbook macro hoping to solve this, and Goggled
> > for an answer
> > --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> > Sub BUDGET2007()
> > '
> > ' BUDGET2007 Macro
> > ' Macro recorded 7/21/2007 by Bob Felmore'
> >
> > '
> > ChDir "\\Felmore-15e49b7\FILES (F)\FILES\$$$\CORE"
> > Workbooks.Open Filename:="\\Felmore-15e49b7\FILES
> > (F)\FILES\$$$\CORE\BUDGET2007.XLS", UpdateLinks:=0 [I would like to
> > update links, but doing this produced an error also]
> > End Sub
> > --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> > Computer #1 is named Felmore, #2 is Robert. A pseudonym of mine has been
> > Bob Felmore Quality Used Cars and Trinkets.
> > Why does the macro keep erroring out? How should it be written?
> > Thank you once again for your assistance
> >
> > Brian Day
> >

--

Dave Peterson