Saving as cell values

[Artisez]

Hi all, having a bit of trouble. I have a workbook that I need to save based on 3 cell values and macro enabled:

>>> use code - tags <<<

Sub SaveWorkbookWithCellValues()
    Dim ws As Worksheet
    Dim fileName As String
    Dim path As String
    Set ws = ThisWorkbook.Sheets("Vessel Schedule")
    Dim value1 As String
    Dim value2 As String
    Dim value3 As String
    value1 = ws.Range("F2").Value
    value2 = ws.Range("P1").Value
    value3 = ws.Range("Q1").Value
    path = "C:\Art\"
    fileName = value1 & " " & value2 & " " & value3 & ".xlsm"
    ThisWorkbook.SaveAs fileName:=path & fileName, FileFormat:=xlOpenXMLWorkbook
End Sub

I get an error on



Any help would be appreciated.

 

[Monty]

Try updating that line in your code and see if the error persists.

ThisWorkbook.SaveAs fileName:=path & fileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

 

[Artisez]

Thank you, unfortunately getting the following error.

 

[vletm]

Artisez​

What are verified values for value1, value2 & value3?

 

[Artisez]

Not sure what you mean by "verified values".

Value 1 is cell F2 contains the month and year "03-2024"
Value 2 is cell P1 which contains text "TP"
Value 3 is cell Q1 which contains the current date in format "03-15" (Custom format mm-dd)

 

[vletm]

Verified means that You've checked/verified that those three values are valid for filename... as written in that Run-time error.
How long is path & filename?
Your the 1st parameter (filename) is same as Your variable filename
... it would be better to have different variable name

 

[Artisez]

Verified means that You've checked/verified that those three values are valid for filename... as written in that Run-time error.
How long is path & filename?
Your the 1st parameter (filename) is same as Your variable filename
... it would be better to have different variable name

My bad, I accidentally posted before I finished the above reply, the name should be good

 

[Artisez]

Let me expand, at the beginning of a month we create a new schedule and title it the month and year, "03-2024" and "Traffic Planner". During the weekdays we will save a backup copy and add the day "03-15" to the end when we save as. I have a manager ask me to create a button that would do it for him.

 

[vletm]

Okay #1
If Your cells F2, P1 & Q1 contains texts as You wrote then there are invalid characters.
I try one more time and other way:
if You select cell F2 ... what can You see in formula in Excel formula bar?
and do same with cells P1 & Q1.
#2 Length of path & filename?
#3 Did You modify variable name?
You the latest writing don't match with Your previous writing.

 

[Monty]

As per my understanding based on your discussions!

Sub SaveWorkbookWithCellValues()
    Dim ws As Worksheet
    Dim fileName As String
    Dim path As String
    Dim value1 As String
    Dim value2 As String
    Dim value3 As String
   
    Set ws = ThisWorkbook.Sheets("Vessel Schedule")
   
    ' Extract values from cells
    value1 = Replace(ws.Range("F2").Value, "/", "-") ' Replace invalid characters
    value2 = ws.Range("P1").Value
    value3 = Replace(ws.Range("Q1").Value, "/", "-") ' Replace invalid characters
   
    ' Define path
    path = "C:\Art\"
   
    ' Define file name
    fileName = value1 & " " & value2 & " " & value3 & ".xlsm"
   
    ' Save workbook
    ThisWorkbook.SaveAs fileName:=path & fileName, FileFormat:=52 ' 52 for xlOpenXMLWorkbookMacroEnabled
   
    MsgBox "Workbook saved successfully!"
End Sub

 

[Artisez]

Okay #1
If Your cells F2, P1 & Q1 contains texts as You wrote then there are invalid characters.
I try one more time and other way:
if You select cell F2 ... what can You see in formula in Excel formula bar?
and do same with cells P1 & Q1.
#2 Length of path & filename?
#3 Did You modify variable name?
You the latest writing don't match with Your previous writing.

Cell F2 contains = 03-2024

Path = path = "C:\Art\"

Filename = 03-2024 TP 03-15

As per my understanding based on your discussions!


[
CODE]Sub SaveWorkbookWithCellValues()

Dim ws As Worksheet

Dim fileName As String

Dim path As String

Dim value1 As String

Dim value2 As String

Dim value3 As String



Set ws = ThisWorkbook.Sheets("Vessel Schedule")



' Extract values from cells

value1 = Replace(ws.Range("F2").Value, "/", "-") ' Replace invalid characters

value2 = ws.Range("P1").Value

value3 = Replace(ws.Range("Q1").Value, "/", "-") ' Replace invalid characters



' Define path

path = "C:\Art\"



' Define file name

fileName = value1 & " " & value2 & " " & value3 & ".xlsm"



' Save workbook

ThisWorkbook.SaveAs fileName:=path & fileName, FileFormat:=52 ' 52 for xlOpenXMLWorkbookMacroEnabled



MsgBox "Workbook saved successfully!"

End Sub[/CODE]

As far as I can tell I am not using invalid characters. Will give this a go.

 

[Marc L]

Hi, you should better use Text property rather than Value and by the way if the file already exists then Kill it before …​

​

 

[Monty]

Good suggestion by Marc

Using the Text property instead of the Value property can be beneficial in certain cases, especially when dealing with formatted cells. If you're interested in using the Text property, you can modify the code like this:

Sub SaveWorkbookWithCellValues()
    Dim ws As Worksheet
    Dim fileName As String
    Dim path As String
    Dim value1 As String
    Dim value2 As String
    Dim value3 As String
    
    Set ws = ThisWorkbook.Sheets("Vessel Schedule")
    
    ' Extract values from cells using Text property
    value1 = Replace(ws.Range("F2").Text, "/", "-") ' Replace invalid characters
    value2 = ws.Range("P1").Text
    value3 = Replace(ws.Range("Q1").Text, "/", "-") ' Replace invalid characters
    
    ' Define path
    path = "C:\Art\"
    
    ' Define file name
    fileName = value1 & " " & value2 & " " & value3 & ".xlsm"
    
    ' Save workbook
    ThisWorkbook.SaveAs fileName:=path & fileName, FileFormat:=52 ' 52 for xlOpenXMLWorkbookMacroEnabled
    
    MsgBox "Workbook saved successfully!"
End Sub

 

[Artisez]

THANKS! Y'all ROCK! The text property worked! I appreciate the help Monty, vletm and Marc L.

 

[Monty]

Glad! it helped.

 

[vletm]

Artisez​

About Your During the weekdays we will save a backup copy and add the...
Questions:
Could this backup procedure do automatic?
Why You or Your boss would like to apply some cells and press the button?
Solution:
That procedure could happen while opening/closing file by the code.
There should be more than one backup version ... then one day that will need to use.