save as to file path using cell name

[WillieW]

In cell C5, I have my users "Full Name" in Cell H5, I have my user "SSN" I would like to use VBA to Save to a file name that combines the two cell as follows

Cell C5 = John Moore Cell H5 = 222-33-4444 I want to save as " John Moore - 4444" (using only the last 4 of the SSN) if possible I would like the save as filename to be Moore -4444 (Last Name and Last 4 of SSN but this may be to difficult since the first and last name lenght will vary. I currently using the follow code that work but it save as the full Name and the entire SSN "John Moore - 222-333-4444. I don't understand what I need to do to modify the code to get the file name to be

1 John Moore - 4444 or
2 Moore - 4444

Here's the current code i"m using

Please Login or Register  to view this content.

I would be grateful for any help

[inesi]

Hi WillieW, try the following below. I tried to make it complete (checking if name/SSN is not null, if file already exsists, etc), but you can remove the code lines you might not need. The main "problem" with macros is that you have to write a code that will do whatever you say it to do, but also prevent/catch "mistakes" from the users.




Sub Save_File_Last_Name()
Dim fName As String
Dim fSSN As String
Dim fPath As String
Dim fSave As Boolean
Dim i As Integer

'Get Name
fName = Trim(Sheets("Sheet1").Range("C5").Value) 'Considering the data is on cell C5 and on sheet Sheet1
'Get Last Name (considering there is a space between first and last name)
i = InStrRev(fName, Chr(32)) ' Please notice Chr(32) = Space in ASCII code
If i > 0 Then
fName = Right(fName, Len(fName) - i)
End If

'Get SSN
fSSN = Trim(Sheets("Sheet1").Range("H5").Value) 'Considering the data is on cell H5 and on sheet Sheet1
fSSN = Right(fSSN, 5) ' Considering the SSN will always end with "-XXXX"

fPath = "C:\" & fName & fSSN & ".xls"
'Test if file already exists
If Dir(fPath) <> "" Then
If MsgBox("The file '" & fPath & "' already exists. Do you wish to overwrite it?", vbExclamation + vbYesNo) = vbYes Then
fSave = True
If Windows(Dir(fPath, vbArchive)) Is Nothing Then 'Check if the file fPath is currently open
Kill fPath
End If
Else
fSave = False
End If
Else
fSave = True
End If

If fSave Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=fPath 'Keep this line if you are using Excel 2003
ActiveWorkbook.SaveAs Filename:=fPath, FileFormat:=xlExcel8 'Keep this line if you are using Excel 2007
Application.DisplayAlerts = True
End If

End Sub

[WillieW]

Inesi, you're awesome the code work great.Thank you so much, this really means alot,we have alot of older volunteers who are not comfortable with computers us this excel file. Second, I learned today that they need to save the file to a directory structure as follows

C;\budgets under the budgets directory we have sub folders as follows

01 January
02 February
03 March
04 April
05 May
06 June
07 July
08 August
09 September
10 October
11 November
12 December

We need to save the budgets in the folders base on the month it was done. for example any budget we do in the month of May need to be saved in the "05 May" folder under C:\budgets. Can the code you provide be modified to do this, our second option would be to just open up the "C:\budgets" directory and have the volunteer click and save into the correct folder. The current code just save the file the root of c:\budgets requiring the user to move the file to the coreect folder.

I'm grateful for your help with this, agian thank you so much.

[inesi]

Hi WillieW, I am glad I could help.

See example of a code below. It will create the path as per a given date (in the example below I am using the current system date by means of the formula NOW).

Please Login or Register  to view this content.

The trick is in the formula FORMAT. MM will make the corresponding month of the variable fDate as 01, or 02, or ...12 and MMMM will give its name (only MMM would create a three letters month).

I hope that will help

[corpoppe]

Hello, my name is Cor,
I'm also trying to get this vba code going but I can't get the path to be right can somebody help me?

Here's my code:

Please Login or Register  to view this content.

Without the part: "C:\Bureaublad\afroepen Cor" the code works but is then stored in the defaultfolder by windows.
=desktop

Thanks in advance!

[arlu1201]

corpoppe,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

Also,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.