Filename as Cell value

[NeilMack]

I'm trying to create a macro to save an excel file as a cell value.

Sub Macro2()
'
' Macro2 Macro
'


'
Dim Path As String
Dim filename As String

Path = "/Users/NeilDavison/Library/Mobile Documents/com~apple~CloudDocs/NEMC/Invoices/2018"
filename = Range("JobNumber").Value

ActiveWorkbook.SaveAs filename:=Path & filename & "xlsm"

End Sub

I've tried the above code but am getting various error messages.

I'm using Excel 2016 for Mac.

Any ideas.

Cheers

Neil Mack

 

[Joe4]

Welcome to the Board!

A few things.

1. If saving as a Macro Enabled workbook, you also need the FileFormat argument in your SaveAs command.
See: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbook-saveas-method-excel
and: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlfileformat-enumeration-excel
This shows an example: https://www.mrexcel.com/forum/excel...ng-macro-enabled-workbook-using-vba-code.html

2. Is "2018" the beginning of your file name, or is it a folder?
If it is a folder, you need another slash after it, i.e.
Path = "/Users/NeilDavison/Library/Mobile Documents/com~apple~CloudDocs/NEMC/Invoices/2018/"

 

[NeilMack]

Joe4

Thank you for your response.

I have now modified the code:

Sub Macro2()


Dim Path As String
Dim FileName As String


Path = "/Users/neildavison/Library/Mobile Documents/com~apple~CloudDocs/NEMC/Invoices/2018/"
FileName = Range("JobNumber").Value


ActiveWorkbook.SaveAs FileName:=Path & FileName & ".xlsm", FileFormat:=52




End Sub

The macro has now re-named the file as 2018 and I'm now getting an error message:

Run time error 1004

Your changes could not be saved to ' 2018.xlsm' because of a sharing violation. Try saving to a different file.

When clicking on Debug, the last line of the code is highlighted.

For info, the Range named JobNumber has a value of NEMC / 02 / 2018

Any further help will be appreciated.

Thank you.

Neil Mack

 

[Norie]

Neil

Have you checked the value of FileName?

FileName = Range("JobNumber").Value

MsgBox FileName

 

[Joe4]

For info, the Range named JobNumber has a value of NEMC / 02 / 2018

You cannot have slashes in your file name. It will think those are more subdirectories.

 

[NeilMack]

Hi Norie
The code returns the file name exactly as in the cell named JobNumber.

However, it seems that slashes can’t be in the file name. I’ve changed the slashes to dashesand the code now works perfectly.

Many thanks for for your help.

Cheers

Neil Mack

 

[NeilMack]

Joe4

Perfect, I changed the slashes to dashes and the code now works perfectly.

Many thanks for for your help with this, it’s a problem I’ve been struggling with for a while.

Again, many thanks, your help in sorting this problem is much appreciated.

Cheers

Neil Mack