Saving a variable filename

[Wes_A]

Good day all
I am trying to write a MACRO in Excel 2007 running on XP to automate a file
save as follows:
I want to save the currently active worksheet as a new workbook *.xls or
*.xlsb where “*†is a volatile filename contained in a cell on the currently
active worksheet, e.g. “Septemberâ€, “October†….
Can anyone help with the code?
Regards
Wes_A

 

[Jacob Skaria]

Anything wrong with your previous post...?


Dim flToSave As String
Dim flName As String
Dim flFormat As Long

flFormat = ActiveWorkbook.FileFormat
flName = ActiveSheet.Range("A1").Text & ".xls"

'If it is same as the current workbook
flToSave = ActiveWorkbook.Path
OR
'If it is a different path assign to that variable
flToSave = "m:\contract"

ActiveWorkbook.SaveAs flToSave & "\" & flName, flFormat
OR
ActiveWorkbook.SaveCopyAs flToSave & "\" & flName



If this post helps click Yes

 

[Wes_A]

Hi Jacob

Thanks, it was fine but I needed the month as the filename. Also it was
saved in a format not easily recognised by the excel when trying to open it
later.
Thanks again for the help which is much appreciated.

 

[Wes_A]

Jacob, my code is now as follows:

Dim flToSave As String
Dim flName As String
Dim flFormat As Long
flFormat = ActiveWorkbook.FileFormat
flName = ActiveSheet.Range("$F$3").Text & ".xlsb"
flToSave = "c:\INVENTORY\HISTORIC\DATA"
ActiveWorkbook.SaveAs flToSave & "\" & flName, flFormat

When trying to run it I get an error saying that file cannot be accessed
since it may not exist. The filename being looked for changes each time but
always looks like:

F2E6E900 or OE2BE900 or OE00F900 etc

Any ideas?

 

[Jacob Skaria]

Check whether your path is valid. "c:\INVENTORY\HISTORIC\DATA" or other wise
it should work fine...I tried the below..

Dim flToSave As String
Dim flName As String
Dim flFormat As Long
flFormat = ActiveWorkbook.FileFormat
flExtn = Mid(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, "."))
flName = ActiveSheet.Range("F3") & flExtn
flToSave = "c:"
ActiveWorkbook.SaveAs flToSave & "\" & flName, flFormat

If this post helps click Yes