Save Workbook with name generated from excel data

andymalan

Board Regular
Joined
Feb 22, 2017
Messages
128
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
.
"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 ?
 
Upvote 0
.
Based on your answer are we to understand you want the saved workbook to be named " configureData.xlsm " ??
 
Upvote 0
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

 
Upvote 0
.
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.
 
Upvote 0
"\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:
Upvote 0
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.
 
Upvote 0
Can you please post the rest of your code, as there is nothing in what you have posted that saves the file.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,480
Messages
6,113,899
Members
448,530
Latest member
yatong2008

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