VBA Code - pick file name from cell value

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,475
Office Version
  1. 2016
Platform
  1. Windows
Hello Friends,

This is part of my macro which I am using.

Code:
Sub makecopy()

    Workbooks.Add
    ChDir "C:\BACKUP"
    ActiveWorkbook.SaveAs Filename:="C:\Backup\[B][COLOR=#ff0000]Factory Target[/COLOR][/B].xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False  
    
End Sub

Can I tell the code to pick file name from cell value & some free defined text in the code.

For Example

Cell A1 = "Honda"
Cell B2 = "Civic"

I would like the file name to be "MAKE Honda MODEL Civic"

something like this
"MAKE" & Range A1.value & "MODEL" & Range B2.Value



Any help would be appreciated

Regards,

Humayun
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
You just need to correct the range reference
Code:
"MAKE" & Range("A1").value & "MODEL" & Range("B2").Value
 
Upvote 0
Hi Fluff,

Thanks for the reply... Did you mean something like this

Code:
 ActiveWorkbook.SaveAs Filename:="C:\Backup\[B][COLOR=#ff0000]"MAKE" & Range("A1").value & "MODEL" & Range("B2")[/COLOR][/B].Value.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False

I am getting compile error
Expected End of statement
 
Upvote 0
All the literal parts need to be between double-quotes, and all variables and range references need to be outside of them.
Then each piece piece is swen together with concatenate symbols (&), i.e.
Code:
 ActiveWorkbook.SaveAs Filename:=[B][COLOR=#0000ff]"C:\Backup\MAKE" [/COLOR]&[COLOR=#ff0000] Range("A1").Value [/COLOR]& [COLOR=#0000ff]"MODEL" [/COLOR]&[COLOR=#ff0000] Range("B2")[/COLOR][COLOR=#ff0000].Value[/COLOR] & [COLOR=#0000ff]".xlsx"[/COLOR][/B], FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
I did all the literal text parts in blue, and all the variables/range references in red so you can easily see the differences.
 
Last edited:
Upvote 0
Its only picking literal parts - not picking cell values.

Actual cells are A1 & F1 (both are merged cells) I thought the merging is causing the problem so I removed merging but still same problem

Also I am using .Activate in the code where it is debugging. Do I have to Change the file name where I am activating the file. If yes than how ? or if there is any alternate solution - pls provide

Here is the full code

Code:
Sub makecopy()

Application.DisplayAlerts = False


    Workbooks.Add
    ChDir "C:\BACKUP"
    ActiveWorkbook.SaveAs Filename:="C:\Backup\Customer" & Range("A1").Value & "Supplier" & Range("F1").Value & ".xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    
    
    Windows("REPORTS").Activate
    Sheets("FACTORY TARGETS").Select
    
     Range("A1:AF1000").Select
    Selection.copy
    
[COLOR=#ff0000]    Windows("Factory Target.xlsx").Activate[/COLOR]
    
    ActiveSheet.Paste
    Cells.Select
    Cells.EntireColumn.AutoFit
        
    Range("A3:AF3").Interior.ColorIndex = 15    
    
    Range("D:D,L:L,P:S,V:W,AB:AE").Delete
    
    Columns("J:S").ColumnWidth = 12
    Columns("T:T").ColumnWidth = 45
    
    Rows("1:2").RowHeight = 34.5
    Rows("3:3").RowHeight = 63.5
    Rows("4:1000").RowHeight = 24.75
    
    ActiveWindow.DisplayGridlines = False
    Cells.Validation.Delete
     
    ActiveWorkbook.Close SaveChanges:=True
    
    Windows("REPORTS.xlsm").Activate
    
    Application.DisplayAlerts = True
    
End Sub

Its making file with the name CustomerSupplier
 
Last edited:
Upvote 0
I recommend building the file name in a variable, and use a MsgBox to verify it is being built correctly, i.e.
Code:
[COLOR=#ff0000]    Dim fName as String
    fName = "C:\Backup\Customer" & Range("A1").Value & "Supplier" & Range("F1").Value & ".xlsx"
    MsgBox fName[/COLOR]

    Workbooks.Add
    ChDir "C:\BACKUP"
    ActiveWorkbook.SaveAs Filename:=[COLOR=#ff0000]fName[/COLOR], FileFormat:= xlOpenXMLWorkbook, CreateBackup:=False
If the "Range" parts are returning any values, it probably means one of two things:
1. The correct file where this is supposed to be pulling from is not the active file when this code is called.
2. The correct sheet where this is supposed to be pulling from is not the active sheet when this code is called.
 
Upvote 0
Debug
Code:
 ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False


This time its picking literal pars & cells values both - I can see correct name in the msg box

But, this part of code is debugging - with below msg

Runtime error 1004
Microsoft excel cannot excess the file 'C:\Backup\Customer etc etc

* The file name or path does not exist.
* The file is being used by another program
* The workbook you are trying to save has the same name as currently open workbook
 
Upvote 0
It is telling you that the problem is most likely one of these three things.
* The file name or path does not exist.
* The file is being used by another program
* The workbook you are trying to save has the same name as currently open workbook
So investigate each of these and see if any of these things are the issue.
 
Upvote 0
The file name or path does not exist.
The path does exist but the file name does not at it is new file being created


The file is being used by another program
Thats not possible coz it is a new file which is been created.

The workbook you are trying to save has the same name as currently open workbook
No there is no workbook currently open with the same name


Confused :(



 
Upvote 0
Are you sure that in your many attempts to get this to work, that there is not another copy of the file with this exact name already open?
Can you confirm that you are actually able to save files to the C:\Backups directory (that it is not locked down)?

Just for kicks, try this version, which should guarantee a unique file name every time:
Code:
[COLOR=#000000]    fName = "C:\Backup\Customer" & Range("A1").Value & "Supplier" & Range("F1").Value & [/COLOR][COLOR=#ff0000]Format(Now, "yyyymmddhhmmss")[/COLOR][COLOR=#000000] & ".xlsx"[/COLOR]
If that works, it means that you had a file name collision (that you did have another file with the same name already open).
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top