All,

I am attempting to record a macro which simply opens a file, updates it [it
is populated with VLOOKUPs which won't update when it's closed] and then
closes it again.

The problem I have is that the file I am opening also contains a
find/replace macro to auto update the formulas at the start of each month. To
do this I have used a 'dummy' ref, and then a find/replace. So most of the
formulas look like this before the find/replace:

=(VLOOKUP($c1,'z:\2007\07''07\[Dummy]sheet1'!$B$6:$H$30,4,FALSE))

When I open this file, it automatically asks me for the location of dummy,
but it doesn't exist, so I have to cancel out. How do I write into the macro
to cancel this, or is there a way to update all formulas except those looking
for the dummy file? Or any other way around this?

Thanks in advance for any help.

This kind of thing worked in my small test:

Dim wkbk As Workbook
Set wkbk = Workbooks.Open(Filename:="C:\my documents\excel\book1.xls", _
UpdateLinks:=0)
Application.Calculate
wkbk.Close savechanges:=False




shakey1181 wrote:

All,

I am attempting to record a macro which simply opens a file, updates it [it
is populated with VLOOKUPs which won't update when it's closed] and then
closes it again.

The problem I have is that the file I am opening also contains a
find/replace macro to auto update the formulas at the start of each month. To
do this I have used a 'dummy' ref, and then a find/replace. So most of the
formulas look like this before the find/replace:

=(VLOOKUP($c1,'z:\2007\07''07\[Dummy]sheet1'!$B$6:$H$30,4,FALSE))

When I open this file, it automatically asks me for the location of dummy,
but it doesn't exist, so I have to cancel out. How do I write into the macro
to cancel this, or is there a way to update all formulas except those looking
for the dummy file? Or any other way around this?

Thanks in advance for any help.

--

Dave Peterson

that works great, thankyou. would there be anyway to automate this macro so
that it runs when the first file is opened?

"Dave Peterson" wrote:

This kind of thing worked in my small test:

Dim wkbk As Workbook
Set wkbk = Workbooks.Open(Filename:="C:\my documents\excel\book1.xls", _
UpdateLinks:=0)
Application.Calculate
wkbk.Close savechanges:=False




shakey1181 wrote:

All,

I am attempting to record a macro which simply opens a file, updates it [it
is populated with VLOOKUPs which won't update when it's closed] and then
closes it again.

The problem I have is that the file I am opening also contains a
find/replace macro to auto update the formulas at the start of each month. To
do this I have used a 'dummy' ref, and then a find/replace. So most of the
formulas look like this before the find/replace:

=(VLOOKUP($c1,'z:\2007\07''07\[Dummy]sheet1'!$B$6:$H$30,4,FALSE))

When I open this file, it automatically asks me for the location of dummy,
but it doesn't exist, so I have to cancel out. How do I write into the macro
to cancel this, or is there a way to update all formulas except those looking
for the dummy file? Or any other way around this?

Thanks in advance for any help.

--

Dave Peterson

Put the code in a General module and name the procedure

Sub Auto_Open()
'code goes here
End sub




shakey1181 wrote:

that works great, thankyou. would there be anyway to automate this macro so
that it runs when the first file is opened?

"Dave Peterson" wrote:

This kind of thing worked in my small test:

Dim wkbk As Workbook
Set wkbk = Workbooks.Open(Filename:="C:\my documents\excel\book1.xls", _
UpdateLinks:=0)
Application.Calculate
wkbk.Close savechanges:=False




shakey1181 wrote:

All,

I am attempting to record a macro which simply opens a file, updates it [it
is populated with VLOOKUPs which won't update when it's closed] and then
closes it again.

The problem I have is that the file I am opening also contains a
find/replace macro to auto update the formulas at the start of each month. To
do this I have used a 'dummy' ref, and then a find/replace. So most of the
formulas look like this before the find/replace:

=(VLOOKUP($c1,'z:\2007\07''07\[Dummy]sheet1'!$B$6:$H$30,4,FALSE))

When I open this file, it automatically asks me for the location of dummy,
but it doesn't exist, so I have to cancel out. How do I write into the macro
to cancel this, or is there a way to update all formulas except those looking
for the dummy file? Or any other way around this?

Thanks in advance for any help.

--

Dave Peterson


--

Dave Peterson