FILE SAVE AS

selkov

Well-known Member
Joined
Jan 26, 2004
Messages
787
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?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top