save file name based on 2 cell references , ask to save as a xlsm or xls to a specific location

[Trevor3007]

hekko,

I have the following code :

Private Sub filename_cellvalue()
Dim Path As String
Dim filename As String
Path = "C:\Users\user\Documents\Vista Mileage\Send to fleet manager\"
filename = Range("jb7").Value & Range("e2").Value & Range("e4").Value
ActiveWorkbook.SaveAs filename:=Path & filename & ".xls", FileFormat:=xlNormal
End Sub

however, users are asking if they can have the option to save as a standard or macro based workbook & to specified location too?

my VB is very limited & therefore reaching out top mr excl for help.

many thanks in advance

 

[Micron]

Perhaps research msoFileDialogFolderPicker. However, you will need a way to get the file extension needed by the user. Option buttons in a frame on a userform? Value in a sheet cell?

 

[Micron]

Found some dialog code in a wb I have and added it to yours. Needs adapting to your situation, but here's the gist of what I'd do:

Private Sub filename_cellvalue()
Dim strPath As String, strFile As String

strPath = "C:\Users\user\Documents\Vista Mileage\Send to fleet manager\"
strFile = Range("jb7") & Range("e2") & Range("e4")

With Application.FileDialog(msoFileDialogFolderPicker)
  .InitialFileName = strPath 'edit strPath value to suit
  If .Show Then
    strPath = .SelectedItems(1)
  Else
    MsgBox "Folder selection was cancelled"
    Exit Sub
  End If
End With
    
strPath = strPath & "\" & strFile & ".xls"
''Debug.Print strPath

ActiveWorkbook.SaveAs Filename:=strPath & ".xls", FileFormat:=xlNormal

End Sub

What's missing is how to get the option of xls or xlsm, unless you have that in one of those cell references.

 

[Trevor3007]

many thanks for your help, got the above error?

 

[Micron]

Always state what line raised the error - nice that you provided the message and number though.
Perhaps put a break point at the first line and step through the code (F8) and check your variable values after each line is executed (before the error causing line if possible). Where did you put that code? If not in a sheet module and the sheet is not protected, then the cause is likely an invalid range reference.

 

[Trevor3007]

hi,

many thyanks.
found the problem , cell ref was the issue...many thanks again

 

[Micron]

you're welcome and thanks for choosing that solution.

 

[namaradesire]

Found some dialog code in a wb I have and added it to yours. Needs adapting to your situation, but here's the gist of what I'd do:

Private Sub filename_cellvalue()
Dim strPath As String, strFile As String

strPath = "C:\Users\user\Documents\Vista Mileage\Send to fleet manager\"
strFile = Range("jb7") & Range("e2") & Range("e4")

With Application.FileDialog(msoFileDialogFolderPicker)
  .InitialFileName = strPath 'edit strPath value to suit
  If .Show Then
    strPath = .SelectedItems(1)
  Else
    MsgBox "Folder selection was cancelled"
    Exit Sub
  End If
End With
   
strPath = strPath & "\" & strFile & ".xls"
''Debug.Print strPath

ActiveWorkbook.SaveAs Filename:=strPath & ".xls", FileFormat:=xlNormal

End Sub

What's missing is how to get the option of xls or xlsm, unless you have that in one of those cell references.

Hello. This is the code I wrote to save the file name as 2 cell values in a specific location. One of the cell values is a date and the other is a string variable. This code was supposed to be activated once the save command button I created is clicked. I keep on getting errors. How can I proceed?

Private Sub CommandButton1_Click()
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Path = “C:\inventory\”
FileName1 = Range(“A1”)
FileName2 = Range(“B1”)
ActiveWorkbook.SaveAs Filename:=Path & FileName1 & “-” & FileName2 & “.xls”, FileFormat:=xlNormal
End Sub

Please help. Ps I am very new to VBA code

 

[Micron]

You need to start your own thread - this is called hijacking the thread.
- if OP comes back seeking further help, their thread likely has gone off the rails by helping others.
- others who think this thread is being taken care of won't see your posts, especially when it's marked as solved

When you start your own thread, if you want to increase your chances of getting a solution
- post code in code tags (use vba button on posting toolbar) and try to use proper indentation.
- always provide error number and its message. Just "I get an error" and "it doesn't work" is of no help to others who would try to help you.
Good luck!