PDA

View Full Version : [SOLVED] HELP! Saving exported spreadsheet and naming the file based on a cell value VBA



dongkatsu
05-21-2015, 10:18 AM
Hello,

I've created an export button in excel and I would like to save this exported spreadsheet based on a cell value. For example, when i export the spreadsheet out, I want the macro to read cell B3 to see if the name of the person starts with E or an F. if Cell B3 has a name of the person Eric which starts with an "E", then save the file as "E_Results" else save it as "F_Results" if the name starts with an F (frank).

Can anyone help me out?

thanks!

Yongle
05-21-2015, 10:34 AM
Are you wanting to save each sheets in your workbook into individual files with filenames = [the first character of cell B3 in each workbook] followed by "_Results"?

dongkatsu
05-21-2015, 11:00 AM
There's only one sheet that I have to export out. I've already given a field range name from cell A1 to C5 to be exported. Now what i'm trying to do is by looking at cell B3 (Frank), I want the macro to see if the name starts with letter F or an E and save the file accordingly as "E_Results" or "F_Results". So the file will contain all the information below and it's just a matter of how it's naming the exported file by looking at Cell B3. Sorry if my explanations are confusing but hope this clears it up.

Column A Column B Column C
Counter Name Age
1 Frank 15
2 Eddie 20
3 Fred 30
4 Edison 40

PS - Numbers 1 - 4 is the counter, Frank, Eddie... is the Name, and 15 20.. is the age.

Yongle
05-21-2015, 11:28 AM
Amend the FilePath to your chosen path. Amend .xlsx to .xls if using older versions of Excel. Then run from the sheet you want to save.

The file name will begin with the first character of cell B3 which should be E or F. You could use an if statement to restrict to only E or F
This will overwrite any previous file with the same name . Application.DisplayAlerts = False - switches off the prompt. Delete this line if you want to be prompted if file already exists.




Sub Save_Sheet_as_WorkBook()
'Run from the sheet you want to save


Dim FileName As String, FilePath As String, FullPath As String
Dim Newbook As Workbook


FilePath = "D:\Documents\" ' <<<< Put in your filepath here incl trailing \


FileName = Left(ActiveSheet.Range("B3").Value, 1) & "_Results.xlsx"
FullPath = FilePath & FileName
MsgBox FullPath

ActiveSheet.Cells.Copy
Set Newbook = Workbooks.Add
Newbook.Sheets(1).Paste

Application.DisplayAlerts = False
Newbook.SaveAs FullPath
Newbook.Close
Application.DisplayAlerts = True

End Sub

dongkatsu
05-21-2015, 12:16 PM
Thank you so much. I got it to work!! One last question. What if cell B3 had a name like this "Frank_123" (without quotation) and I only wanted the name to populate from the code below:

FileName = Left(ActiveSheet.Range("B3").Value, 1) & "_Results.xlsx

So the Export file name will be Frank_Results.xlsx and if cell B3 had a name of Edward_123, Export it as Edward_Results.xlsx


Or only names that are there are Frank_123 and Edward_123, how would i only take the characters before "_" to be saved as Name_Results?

Yongle
05-21-2015, 01:06 PM
Looks odd, but it works


FileName = Range("B3").Name.Name & "_Results.xlsx"

Yongle
05-21-2015, 01:51 PM
please go to thread tools (at top of thread) and mark the thread as solved.
thanks

dongkatsu
05-21-2015, 01:51 PM
Not working and I must've misinterpreted.

If B3 is F_123 and if it starts with letter F, then Frank_Results, if it's E_123 and starts with letter E, then Edward_Results.

sorry for numerous questions.

Yongle
05-21-2015, 02:08 PM
What do you mean by the word "name". Ranges can have names, and a single cell is a range containing one cell. I assumed that is what you meant.

Do you mean that
if the cell value is F_123 then call the file Frank_Results
if the cell value is E_123 then call the file Edward_Results

With only 2 values you could use:


If Left(ActiveSheet.Range("B3").Value, 1) = "F" then
FileName = "Frank_Results.xlsx"
End if
If If Left(ActiveSheet.Range("B3").Value, 1) = "E" then
FileName = "Edward_Results.xlsx"
End if

dongkatsu
05-21-2015, 02:08 PM
please go to thread tools (at top of thread) and mark the thread as solved.
thanks

I will def mark this as solved once one more of the request have been solved. I tried modifying the code but not working.

Currently, i'm using this code below but i would like to add a if statement where if B3 is F_123, then save the file as Frank_Results and if B3 is E_123, then save the file as ED_Results.

FileName = Application.GetSaveAsFilename(Left(ActiveSheet.Range("B3").Value, 1) & "_Results.csv", _
filefilter:="Excel File (*.csv), *.csv")

Yongle
05-21-2015, 02:26 PM
Another choice would be to use Excel to work out if Edward or Frank. Something along the lines of

Put this formula in a cell "D3"
=IF(LEFT(B3,1)="E","Edward",IF(LEFT(B3,1)="F",Frank,"Neither"))


FileName = ActiveSheet.Range("D3").Value & "_Results.xlsx"

Or Put this formula in a cell "D3"
=IF(LEFT(B3,1)="E","Edward_Results.xlsx",IF(LEFT(B3,1)="F","Frank_Results.xlsx","Neither_Results.xlsx"))

FileName = ActiveSheet.Range("D3").value

Yongle
05-21-2015, 03:01 PM
To save a file as a csv, this will work (where FullPath is built up as before but has .csv instead of .xlsx at the end)

Newbook.SaveAs FileName:=FullPath, FileFormat:=xlCSV

PS - it is only possible to save single worksheets to a csv file. Multi-sheet workbooks need separate files for each sheet.

dongkatsu
05-22-2015, 06:33 AM
Great thank you!