C

[chalky]

I have a part of VBA code that reads:

Windows("Data 31 Dec 05.xls").Activate

This works fine at the moment but at somepoint the file is going to be saved again with its name changed. This part of the formula kicks in after 6 pages are copied out of the main document and into a new one, which is saved under a new file name, ie:

Macro is run
6 Pages are copied out into a new book
New book is saved
Old file (Data 31 Dec 05.xls) is selected.

What i want to be able to do is to have a formula in the main doc that reads:
=CELL("Filename") so that the correct filename is known, then have this copied in with the 6 pages and have the Windows formula link to this cell reference.

Any ideas?

[Bob Phillips]

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"chalky" <[email protected]> wrote in
message news:[email protected]...
>
> I have a part of VBA code that reads:
>
> Windows("Data 31 Dec 05.xls").Activate
>
> This works fine at the moment but at somepoint the file is going to be
> saved again with its name changed. This part of the formula kicks in
> after 6 pages are copied out of the main document and into a new one,
> which is saved under a new file name, ie:
>
> Macro is run
> 6 Pages are copied out into a new book
> New book is saved
> Old file (Data 31 Dec 05.xls) is selected.
>
> What i want to be able to do is to have a formula in the main doc that
> reads:
> =CELL("Filename") so that the correct filename is known, then have this
> copied in with the 6 pages and have the Windows formula link to this
> cell reference.
>
> Any ideas?
>
>
> --
> chalky
> ------------------------------------------------------------------------
> chalky's Profile:
http://www.excelforum.com/member.php...o&userid=23758
> View this thread: http://www.excelforum.com/showthread...hreadid=513265
>

[Bob Phillips]

How about using

Set oWB = Windows("Data 31 Dec 05.xls")
oWB.Activate
'6 Pages are copied out into a new book
'New book is saved
oWb.SaveAs new_filename
'Old file (Data 31 Dec 05.xls) is selected.
oWB.Activate


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"chalky" <[email protected]> wrote in
message news:[email protected]...
>
> I have a part of VBA code that reads:
>
> Windows("Data 31 Dec 05.xls").Activate
>
> This works fine at the moment but at somepoint the file is going to be
> saved again with its name changed. This part of the formula kicks in
> after 6 pages are copied out of the main document and into a new one,
> which is saved under a new file name, ie:
>
> Macro is run
> 6 Pages are copied out into a new book
> New book is saved
> Old file (Data 31 Dec 05.xls) is selected.
>
> What i want to be able to do is to have a formula in the main doc that
> reads:
> =CELL("Filename") so that the correct filename is known, then have this
> copied in with the 6 pages and have the Windows formula link to this
> cell reference.
>
> Any ideas?
>
>
> --
> chalky
> ------------------------------------------------------------------------
> chalky's Profile:
http://www.excelforum.com/member.php...o&userid=23758
> View this thread: http://www.excelforum.com/showthread...hreadid=513265
>

[Tom Ogilvy]

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",
CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

--
Regards,
Tom Ogilvy


"chalky" <[email protected]> wrote in
message news:[email protected]...
>
> I have a part of VBA code that reads:
>
> Windows("Data 31 Dec 05.xls").Activate
>
> This works fine at the moment but at somepoint the file is going to be
> saved again with its name changed. This part of the formula kicks in
> after 6 pages are copied out of the main document and into a new one,
> which is saved under a new file name, ie:
>
> Macro is run
> 6 Pages are copied out into a new book
> New book is saved
> Old file (Data 31 Dec 05.xls) is selected.
>
> What i want to be able to do is to have a formula in the main doc that
> reads:
> =CELL("Filename") so that the correct filename is known, then have this
> copied in with the 6 pages and have the Windows formula link to this
> cell reference.
>
> Any ideas?
>
>
> --
> chalky
> ------------------------------------------------------------------------
> chalky's Profile:
http://www.excelforum.com/member.php...o&userid=23758
> View this thread: http://www.excelforum.com/showthread...hreadid=513265
>

[chalky]

Thanks for the input. However the file will change name at any time - It will remain as the normal filename for a quarter and then change, i can change the code manually but would rather not as it will be handed over to someone else non Excel minded