• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto generate workbook file name based on multiple cell contents

naquin316

New Member
Hi all! I'm trying to come up with a macro to save my workbook a certain way. I have a sales invoice that has 6 worksheets and I want to create a picture on the 1st sheet and assign this macro to it. I want to save it to a folder on the desktop called tickets.

I also would like it where no prompts come up, ie. click the button and it saves the file to a new name. I would like the naming convention to look like this:


ticket#_company_wellname.xls Where ticket# is cell A1, company is cell B1, and wellname is cell C1. It is important to have the underscores between names.


any help would be much appreciated.

Ryan
 
Hi and welcome


Try this code

[pre]
Code:
Sub SaveFile()
Dim NameFile As String

With Worksheets("Sheet1")
NameFile = .Range("A1") & "_" & .Range("B1") & "_" & .Range("C1") & ".xls"
End With

ThisWorkbook.SaveAs Environ("USERPROFILE") & "Desktoptickets" & NameFile
End Sub
[/pre]
PS be careful of charcters not admitted to name files such as !?*
 
Thanks you!! That worked perfectly. Instead of saving it to a certain folder can it be coded where the saveas dialog box comes up with the dim namefile string already there then allowing the user to select his own save location? Thanks again. I learned alot from your first code!
 
Hi

Try this again

[pre]
Code:
Sub SaveFile()
Dim NameFile As Variant

With Worksheets("Sheet1")
NameFile = .Range("A1") & "_" & .Range("B1") & "_" & .Range("C1") & ".xls"
End With

NameFile = Application.GetSaveAsFilename(InitialFileName:=Environ("USERPROFILE") & "Desktoptickets" & NameFile, Filefilter:="Fichier Excel (*.xls), *.xls")

If NameFile = False Then
MsgBox "File not saved"
Else
ThisWorkbook.SaveAs Filename:=NameFile
End If
End Sub
[/pre]

Regards!
 
I got it to make the save as dialog come up with the correct name. I am also able to select a different folder and click save. Then I get a runtime error '13'.

When I click debug the yellow error points to the line:

If Namefile = False Then

I'm I doing something wrong? I'll try to attach my current code below.

[pre]
Code:
Sub SaveFile()
Dim NameFile As String
With Worksheets("SO1")
NameFile = .Range("A1") & "_" & .Range("B1") & "_" & .Range("C1") & ".xls"
End With
NameFile = Application.GetSaveAsFilename(InitialFileName:=Environ("USERPROFILE") & "Desktoptickets" & NameFile, Filefilter:="Fichier Excel (*.xls), *.xls")
If NameFile = False Then
MsgBox "File not saved"
Else
ThisWorkbook.SaveAs Filename:=NameFile
End If
End Sub
[/pre]
 
Hi mercatog


How can I get the first part of your cod to save the file as an .xlsm it works fine as .xls but I need the user to run macros in the work sheet after they have saved it.


Hope you can assist
 
Back
Top