asked on

Saving a file in Excel

I have two cells on sheet in a workbook. Cell A1 contains a text string of a directory into which I would like to save a file and cell A2 contains a text string of an excel file name.

I was wondering if anyone had code that would allow me to save the file name (both for first save and for subsequent saves (requiring a file replace)), automatically?

Thank

ASKER CERTIFIED SOLUTION

Eric Zwiekhorst

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

starport

ASKER

I want to save the active workbook only, including the sheet with the two cells.
Thanks
David

SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eric Zwiekhorst

Dear David and Rob

for the message not to popup and save anyway

start youre code with
application.DsplayAlerts = false

and end the code with
application.DisplayAlerts = true

Eric Zwiekhorst

if you would like the concatination to be done by VBA
Sub FileSave()
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=range(a1) & "\" & range(a2) _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
End Sub

Rob Henson

Thinking through the logic:

Make changes to workbook which do not affect filename ie save within existing workbook - just Save would suffice.

Make changes that do affect filename ie new file required - Save As required but current filename will be different to named range File_Name so could insert a check into the code or cell within sheet with existing pathe and filename:

=CELL("filename",a1) returns full path and filename. Give range name of "Curr_File"

One scenario which it doesn't allow for, which could be good, is the new file overwriting an older file. Suppressing the pop up could be dangerous.

Cheers
Rob H

CurrFile = Range("Curr_File")
NewFile = Range("File_Name")
If Curr_File = New_File Then
ActiveWorbook.Save
Else
ActiveWorkbook.SaveAs Filename:=NewFile _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False 
End If

Open in new window

Rob Henson

This Save routine will have to be activated by a macro button as it won't be activated automatically as per your question.

The automated part of it will be the definition of the directory and filename as per your text strings.

Cheers
Rob H

starport

ASKER

Rob,
I have been working with the snippet of code you very kindly gave me:
NewFile = Range("File_Name")
ActiveWorkbook.SaveAs Filename:=NewFile _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

But for some reason, it seems to want to save in Excel 2003 format, and as a result, it's asking me if I'm ok losing functionality. Don't understand why. Thanks

D