Macro Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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



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.
Click to expand...
 
that works great, thankyou. would there be anyway to automate this macro so
that it runs when the first file is opened?

Dave Peterson said:
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



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.
Click to expand...
Click to expand...
 
Put the code in a General module and name the procedure

Sub Auto_Open()
'code goes here
End sub



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

Dave Peterson said:
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



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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top