Hello,
Has anyone experience with passing cell parameters to a file name using
a button and VB? I have a spreadsheet I am using to send out to
multiple individuals to collect data and need a consistent format to
help manage the files that I will be receiving via email.

Here is what I have in mind: There are three cells containing data that
I would like to concatenate into a file name for my spreadsheet. The
filename would end up being something like: cell1_cell2_cell3_date.xls.


Finishing the data entry, the user would click on a button within the
spreadsheet, then click on the OK button in the SaveAs window. The
next step would involve code that emails the spreadsheet as an
attachement (this is done already). We are all on the same version of
Outlook and Excel so compatibility is not an issue. Any ideas?


Thanks.


Reply »

I solved my own problem. After some thought, I will need to validate
the content in the header to make sure that there are no illegal
characters in the file name. I may also specify the location (my
documents) to save to. Here is the code if anyone else might use:

Sub SaveAs()
' created to save parameters of three cells as part of the filename
Dim Org1 As String
Dim Office As String
Dim Header As String
Dim filename As String

Org1 = Right(Range("F15").Value, 3)
Office = Left(Range("F16").Value, 4)
Header = Range("F28").Value
filename = Org1 & "_" & Office & "_" & Header

Application.Dialogs(xlDialogSaveAs).Show filename
End Sub

bluegrassstateworker wrote:
Hello,
Has anyone experience with passing cell parameters to a file name using
a button and VB? I have a spreadsheet I am using to send out to
multiple individuals to collect data and need a consistent format to
help manage the files that I will be receiving via email.

Here is what I have in mind: There are three cells containing data that
I would like to concatenate into a file name for my spreadsheet. The
filename would end up being something like: cell1_cell2_cell3_date.xls.


Finishing the data entry, the user would click on a button within the
spreadsheet, then click on the OK button in the SaveAs window. The
next step would involve code that emails the spreadsheet as an
attachement (this is done already). We are all on the same version of
Outlook and Excel so compatibility is not an issue. Any ideas?


Thanks.


Reply »