VBA Code - pick file name from cell value

[hrayani]

Hello Friends,

This is part of my macro which I am using.

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

 

[Fluff]

You just need to correct the range reference

"MAKE" & Range("A1").value & "MODEL" & Range("B2").Value

 

[hrayani]

Hi Fluff,

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

 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

 

[Joe4]

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.

 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.

 

[hrayani]

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

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

 

[Joe4]

I recommend building the file name in a variable, and use a MsgBox to verify it is being built correctly, i.e.

[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.

 

[hrayani]

Debug

 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

 

[Joe4]

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.

 

[hrayani]

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

 

[Joe4]

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:

[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).