Save Workbook with name generated from excel data

andymalan

Board Regular
Joined
Feb 22, 2017
Messages
65
Hello brainiacks, (said with utmost respect). Please help with my problem:

I have some code in a workbook named configureData.xlsm that creates a filename in Q2 .
I want to open a new workbook.xlsx and save it with the fileName created.

The path would always be the same:

UsersName = Environ("USERNAME")
strFileName = "C:\Users" & UsersName & _
"\Desktop\Excalibur Winner\configureData.xlsm\NewFileName.xlsx"

but the NewFileName will change depending on the data used by the code when creating the filename and storing it in Q2.
Your help will be appreciated hugely.

kind regards
Andy
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,251
.
"but the NewFileName will change depending on the data used by the code when creating the filename and storing it in Q2."

Where is the data to be selected for the new file name ?
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,251
.
Based on your answer are we to understand you want the saved workbook to be named " configureData.xlsm " ??
 

andymalan

Board Regular
Joined
Feb 22, 2017
Messages
65
no, as i stated in my request
"but the NewFileName will change depending on the data used by the code when creating the filename and storing it in Q2.
Your help will be appreciated hugely."

in the workbook
configureData.xlsm, the existing macro uses the current customers name and quote number and concatenates these into Q1. then the macro copies Q1 and pastes to Q2 using save special values.
i want to use the results of Q2 as the filename. in the path as stated previously.

many thanks
Andy

 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,251
.
Try this :

Code:
[COLOR=#333333]"\Desktop\Excalibur Winner\configureData.xlsm\[/COLOR][COLOR=#0000cd]" & Sheets("Sheet1").Range("Q2").Value & ".xlsx[/COLOR][COLOR=#333333]"[/COLOR]
You will need to edit the sheet name to match that sheet you are referencing in your workbook.
 

andymalan

Board Regular
Joined
Feb 22, 2017
Messages
65
"\Desktop\Excalibur Winner\configureData.xlsm\" & Sheets("Sheet1").Range("Q2").Value & ".xlsx"
Dear Logit,
I get a compile error/syntax error when I add your code.
Rich (BB code):
"\Desktop\Excalibur Winner\ExcaliburProPlus\BarCodes.xlsm\" & Sheets("Sheet2").Range("BA1").Value & ".xlsx"
 
Last edited by a moderator:

andymalan

Board Regular
Joined
Feb 22, 2017
Messages
65
Dear Logit
thank you for your efforts so far, but I am still not able to save the worksheet to the path/filename, the code below no longer gives an error but its still not saving.
Do you have any other advice?
Code:
'save workbook AS filename from BA1
    Dim sFile As String
    sFile = Range("BA1").Value & ".xlsm"
    UsersName = Environ("USERNAME")
    strFileName = "C:\Users\" & UsersName & _
    "\Desktop\Excalibur Winner\ExcaliburProPlus\BarCodes.xlsm\" & Sheets("Components").Range("BA1").Value & ".xlsx"
    [\code]
very much in appreciation.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,285
Office Version
365
Platform
Windows
Can you please post the rest of your code, as there is nothing in what you have posted that saves the file.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,251
VBA Code:
Sub saveShtAs()
Dim MyFolder As String
Dim fname As String, ext As String
Dim TempFolder As String
 
    MyFolder = Environ$("USERPROFILE") & "\Desktop\Excalibur Winner\ExcaliburProPlus"

    If MyFolder = "" Then Exit Sub

    fname = Sheets("Sheet1").Range("Q2").Value

    Sheets("Sheet1").Copy

    ActiveWorkbook.SaveAs Filename:=MyFolder & "\" & fname, _
         CreateBackup:=False
    ActiveWorkbook.Close False
    
MsgBox "Done!"

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,100,193
Messages
5,473,071
Members
406,845
Latest member
JohnR123

This Week's Hot Topics

Top