Issue With "Save As" Code

[mike.lewis]

Hey There

i have a coded cell where when it is selected the template worksheet performs a save as process

The file currently saves as the below:
Value from cell A _ Value from Cell B _ Todays Date _ Value from cell c

This in theory may look like the following:
Mike Jones_Krystal Mary_15.03.2011_89.39382712612


I am having a couple of issues

1. The file name that is auto generated will be edited to reduce the numbers at the end to two decimal spaces, therefore being 89.39, as the rest are generally not needed, i have not found i way of having this done automatically yet
2. When the file name is edited in the Save As field that is presented to reflect the number that is needed, the file name automatically reverts back to its original name even if the Save As field is editedMike Jones_Krystal Mary_15.03.2011_89.39382712612

Code is below:

'/// Do the following if A1 is the cell selected (change that to suit...)
If Not Intersect(Target, Range("B1")) Is Nothing Then
Dim s As String
Dim x As Variant
s = Range("C6") & "_" & Range("C5") & "_" & Format(Date, "dd.mm.yy") & "_" & Range("E5") & ".xls"
x = Application.GetSaveAsFilename(s, _
"Microsoft Office Excel Workbook(*.xls), *.xls") '///Alert: hardcoded for XL2003
If x <> False Then
ActiveWorkbook.SaveAs s
End If
End If

 

[JoeMo]

Assuming the number you want to truncate is in E5:

s = Range("C6") & "_" & Range("C5") & "_" & Format(Date, "dd.mm.yy") & "_" & Round(Range("E5"),2) & ".xls"

 

[mike.lewis]

Thanks Jo, that is awesome !!!

solves the first issue, any ideas how i can get around the save as issue.

to clarify what will happen is someone may end up with the same file name twice, however when they go to save as through clicking B1 it will show the standard save as dialog box, you can retype whatever you like, but when you click save, the "original" file name that was auto generated is saved, quite bizzare, almost like its hard coded

 

[mike.lewis]

Assuming the number you want to truncate is in E5:

s = Range("C6") & "_" & Range("C5") & "_" & Format(Date, "dd.mm.yy") & "_" & Round(Range("E5"),2) & ".xls"

just tried this, how do i get the number in E5 to tuncate to two decimal places, therefor 98.867356363 will look like 98.86, formatting the cell does not fix this

 

[JoeMo]

just tried this, how do i get the number in E5 to tuncate to two decimal places, therefor 98.867356363 will look like 98.86, formatting the cell does not fix this

Not sure I understand. The Round function should be doing that for you unless you have text rather than a number in E5. Do this test: In any empty cell enter =TYPE(E5). If this returns a 1, you have a number in E5. If a 2 it is text. In the latter case use: Round(Val(Range(E5)),2) instead of Round(Range(E5),2).

 

[JoeMo]

Thanks Jo, that is awesome !!!

solves the first issue, any ideas how i can get around the save as issue.

to clarify what will happen is someone may end up with the same file name twice, however when they go to save as through clicking B1 it will show the standard save as dialog box, you can retype whatever you like, but when you click save, the "original" file name that was auto generated is saved, quite bizzare, almost like its hard coded

Not clear to me why you need these lines:

x = Application.GetSaveAsFilename(s, _
  "Microsoft Office Excel Workbook(*.xls), *.xls") '///Alert: hardcoded for XL2003
  If x <> False Then

since you finish by doing an Activeworkbook.SaveAs s
save which ensures the file will have a filename set by the string s no matter what the user did to edit the filename in the dialog box.

 

[mike.lewis]

Not clear to me why you need these lines:

x = Application.GetSaveAsFilename(s, _
 "Microsoft Office Excel Workbook(*.xls), *.xls") '///Alert: hardcoded for XL2003
 If x <> False Then

since you finish by doing an Activeworkbook.SaveAs s
save which ensures the file will have a filename set by the string s no matter what the user did to edit the filename in the dialog box.

i removed these lines too see what would happen and the function stops working

 

[JoeMo]

Most likely it hasn't stopped working, it's just saving the file with the filename you assigned to the variable s. If you change one or more of the cell values that s is derived from, then you should see a new file has been saved. The GetSaveAsFilename function does nothing more than allow the user to identify a file name. It does not actually save the file. After you invoke this function, your code then does a SaveAs using s as the file path/filename, independent of anything the user might have done when the SaveAs dialog box was open.