0

I would like to save my file with the flexible name, which will change as the cell value changes.

The one answer is here:

Save a file with a name that corresponds to a cell value

however, I want also some fixed part of the name, which won't change unlike the part described in the query above.

Basing on this solution I tried to write something as follows:

Sub Save ()
Dim name As String, Custom_Name As String

name = Range("A2").Value
Custom_Name = "NBU" & name & "- Opportunity list.xlsx"
ActiveWorkbook.SaveAs Filename:=Custom_Name

In the effect, I am getting an error:

This extension cannot be used with the selected file type. Change the file extension in the File name text box or select a different type file by changing the Save as type.

I would like to have this file in the .xlsx extension.

Excel VBA - save as with .xlsx extension

The answer above doesn't really match to my situation. It will be vital to remove all form control buttons in the newly saved file, when possible.

Thanks & Regards, 

End Sub
Geographos
  • 827
  • 2
  • 23
  • 57
  • Your code only set the name to be used when you intend to save a workbook. Do you have a piece of code in order to make the saving? I don't like looking to other codes which does not do what you need. Can you clearly explain what do you wont your code to do? `ActiveWorkbook.SaveAs Filename:=Custom_Name` wouldn't be enough? – FaneDuru Feb 18 '20 at 17:05
  • I have added your piece of code. Sorry about it I completely forgot this snippet. Now I am getting an error. I updated my query with the missing part of the code. My file name is Opportunity v1.0.xslm if it can help at some point. Thank you in advance. – Geographos Feb 18 '20 at 17:31
  • Transform your line `ActiveWorkbook.SaveAs Filename:=Custom_Name` in `ActiveWorkbook.SaveAs Filename:=Custom_Name, FileFormat:= xlWorkbookDefault`. But, of course, you must have a string in your `Range("A2")` cell... – FaneDuru Feb 18 '20 at 17:39

2 Answers2

1

There is no action in the routine listed to save the file. It just simply takes the contents of a cell and creates a string with wrapped values.

I am not totally sure of what your goal is, but you need to add the action from the second link you provided. Workbook.SaveAs Method.

See the code below for a working example that I created to test.

Public Sub Save()
    Dim name As String, Custom_Name As String

    name = Range("A2").Value
    Custom_Name = ThisWorkbook.Path & "\" & "NBU" & name & " - Opportunity list.xlsx"

    'Disable alert when saving
    Application.DisplayAlerts = False

    'Save the workbook.
    ActiveWorkbook.SaveAs Filename:=Custom_Name, FileFormat:=51

End Sub

You should note that after this code has executed, you will now be in the newly created file. This is not an export.

Test this and let me know if you have any questions. There are a few things that seem to be unnecessary in your code, but we can address those if you find this answers your first issue.

Edit:

I would also call out specifically then worksheet with the range as well.

name = Worksheets("Sheet1").Range("A2")

DjJazzyJeffTN
  • 86
  • 6
0

You said the file name is 'Opportunity v1.0.xslm'. Would it have macros? My version of Excel complains about using .xlsx if there is code in the workbook.

MaryB
  • 3
  • 2