Macro Query

[shakey1181]

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]

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.

 

[shakey1181]

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

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.

 

[Dave Peterson]

Put the code in a General module and name the procedure

Sub Autpen()
'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?

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.