Compile error: Method or data member not found

[Brian Day]

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]

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????

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

 

[Brian Day]

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

 

[Dave Peterson]

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.

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

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