FILE SAVE AS

selkov

Well-known Member
Joined
Jan 26, 2004
Messages
781
I HAVE TO GENERATE 250 FILES FROM A LIST WITH MODIFICATIONS TO EACH ONE, LIKE A MAIL MERGE. I HAVE THE HEART OF THE PROGRAM RUNNING AS I NEED IT TO,, WHAT I CAN NOT GET IS THE MACRO TO INITIATE THE "FILE_SAVE_AS" DIALOG BOX & AUTOMATICALLY REPLACE THE FILE TITLE WITH A NAME FROM A SPECIFIED CELL IN THAT SHEET.

ANY IDEAS?
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723
mostly from:

http://www.mrexcel.com/board2/viewtopic.php?t=74730&highlight=save+change+name

Code:
    Dim fullnme
    Dim Cust
    Dim Invce
'Assumes Customer Name is in A4
'Invoice Number is in D10

    '  save this workbook with it's original name to the default path
    ThisWorkbook.Save

    ' Will save as a different workbook, change the name of active workbook to
    ' the values of A4 & D10
    ',,,,,,,,True adds to MRU
    Cust = Sheets("sheet1").Range("A4")
    Invce = Sheets("sheet1").Range("D10")
    fullnme = Cust & Invce
    ThisWorkbook.SaveAs fullnme, , , , , , , , True

    ' OR to save a copy and keep the open workbook as same name

    Cust = Sheets("sheet1").Range("A4")
    Invce = Sheets("sheet1").Range("D10")
    fullnme = Cust & Invce
    ThisWorkbook.SaveCopyAs fullnme

    'close workbook with no further notifications
    Application.DisplayAlerts = False
    ActiveWorkbook.Close SaveChanges:=False
    Application.DisplayAlerts = True
    
    ' to add a path & name from ranges
    wkbkpath = Sheets("sheet1").Range("A1")
    If wkbkpath = "" Then wkbkpath = Application.DefaultFilePath
    If Right(wkbkpath, 1) = "\" Then wkbkpath = Left(wkbkpath, (Len(wkbkpath) - 1))
    savename = Sheets("sheet1").Range("A2").Value
    fullnme = wkbkpath & "\" & savename
    ThisWorkbook.SaveAs fullnme, , , , , , , , True
    MsgBox "File saved as:" & Chr(10) & Chr(10) & fullnme, vbExclamation, "STATUS"

Pick & choose what you want to add to your routine
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
If you're just saving the file(s) in the same folder as the original one, you can get away with this:
Code:
ActiveWorkbook.SaveAs Filename:=Range("A1").Text, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

It will save the file under the name you enter into cell A1.

This help?
Dan
 

Iridium

Well-known Member
Joined
Jul 15, 2002
Messages
2,831
Another idea

Code:
Public Sub SaveAsA1()
On Error go to Errhandler

Dim Location, ThisFile As String

Location = "\\directory\subdirectory\"
ThisFile = Range("A1")
ThisFile = Location + ThisFile
ActiveWorkbook.SaveAs filename:=ThisFile
Exit Sub

Errhandler:

MsgBox Err.Description

End Sub

For info try not to use all caps as it sounds like you're shouting and isn't to easy to read

HTH
 

selkov

Well-known Member
Joined
Jan 26, 2004
Messages
781

ADVERTISEMENT

HalfAce said:
If you're just saving the file(s) in the same folder as the original one, you can get away with this:
Code:
ActiveWorkbook.SaveAs Filename:=Range("A1").Text, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

It will save the file under the name you enter into cell A1.

This help?
Dan

Yes it did , perfect.
Thank you very much
 

selkov

Well-known Member
Joined
Jan 26, 2004
Messages
781
HalfAce said:
If you're just saving the file(s) in the same folder as the original one, you can get away with this:
Code:
ActiveWorkbook.SaveAs Filename:=Range("A1").Text, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

It will save the file under the name you enter into cell A1.

This help?
Dan

Dan, How can I get the macro to look at cell a:1 & if it sees somethinf there re-run the macro?
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

I'm sorry, I'm not following what you're asking...Can you elaborate?

Dan
 

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723
selkov said:
Dan, How can I get the macro to look at cell a:1 & if it sees somethinf there re-run the macro?




Code:
    if range("a1") <> vbnullstring then
        ActiveWorkbook.SaveAs Filename:=Range("A1").Text,   FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False 
    end if
 

Iridium

Well-known Member
Joined
Jul 15, 2002
Messages
2,831
I'm not entirely sure what you mean also but do you mean if A1 changes then rerun the macro?

If so then incorporate your saving code into this macro:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        ' Display a message when one of the designated cells has been
        ' changed.
        ' Place your code here.
        MsgBox "Cell " & Target.Address & " has changed."
       
    End If
End Sub

HTH
 

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,028
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top