Save an Excel file with a new name

[PJFry]

I am new to Excel VB and I want to write a routine that will save the current Excel file in same directory as the original, using the same name as the current file with '_External' appended to the end.

The current file is :
C:\Rpt\Richards_June07
The new file will save as
C:\Rpt\Richards_June07_External

The trick is that the location and filename needs to dynamic.

The external file would overwrite anything out there.

I code quite a bit in Access, but the Excel objects are still very new to me.

Ideas?
Thanks in Advance!
PJ

 

[SkipVought]

Hi,

As I stated in the other forum, turn on your macro recorder and record doing a SaveAs.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[Fr33dan]

ActiveWorkbook.SaveAs (ActiveWorkbook.FullName & "_External")
should work

 

[PHV]

Or this ?
ActiveWorkbook.SaveAs Replace(ActiveWorkbook.FullName, ".xls", "_External.xls")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

The external file would overwrite anything out there
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Replace(ActiveWorkbook.FullName, ".xls", "_External.xls")
Application.DisplayAlerts = True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Fr33dan]

PHV,
Maybe you have a newer verion of office but i don't have a replace method but thanks for pointing out the error in my code:

ActiveWorkbook.SaveAs (Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) - 4) & "_External")

That Code should work

 

[PHV]

a newer verion of office
My code should work with office 2000 and above.

 

[Fr33dan]

Ah, Still running 97 here. Looks like you've got the better code then