Help with Workbook name change on update

[Brian]

Can anyone help me with this. I am not exactly sure what Dave Peterson means.

Q: Is the activeworkbook the same as the workbook that was opened?
A: Yes, but when the Workbook is opened it has one file name, but as soon as
you update it and save it the name changes. Then when you try and re-update
it the new name is not found, so I get the run Time Error.

Not exactly sure what you mean:

If yes, then declare the bk variable in a General module and make it public.

Public bk as workbook

(Remove the dim statement in the open procedure.)

Then use the bk variable to saveas

bk.saveas

And use bk as object to represent that workbook--no matter what the name is.

What's happening is when I save the workbook to a different name, because it
auto assigns the file name to according to the information on the User form.

The Workbook changes from the name "Master Engineering Spec.xlsm" to the
following.
strFile = "SPEC " & CLLI_Code_1.Value _
& Space(1) & TEO_No_1.Value _
& Space(1) & CES_No_1.Value _
& Space(1) & TEO_Appx_No_2.Value

I need help in changing the code so that it will recognize the new file
name. I posted all the code in my prior post on 1/7/2010 "Run Time Error on
File Name".

If you need me to repost the code let me know.

 

[JLGWhiz]

If you have not already tried this modified version, then try it and see if
you still get the Subscript out of Range message.

Private Sub Save_Engineering_Spec_11_Click()

Dim strFile As String
Dim fileSaveName As Variant
Dim myMsg As String

strFile = "SPEC " & CLLI_Code_1.Value _
& Space(1) & TEO_No_1.Value _
& Space(1) & CES_No_1.Value _
& Space(1) & TEO_Appx_No_2.Value

fileSaveName = Application.GetSaveAsFilename _
(InitialFileName:=strFile, _
fileFilter:="Excel Macro-Enabled Workbook(*.xlsm),(*.xlsm")

If fileSaveName <> False Then
ActiveWorkbook.SaveAs Filename:= _
fileSaveName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
CreateBackup:=False
Workbooks.Open ("Master Engineering Spec.xlsm")

Else
MsgBox prompt:=Engineer_2.Value & vbLf & "You canceled saving the
Engineering Spec." & vbCrLf & _
"Engineering Spec was not Saved.", _
Title:="C.E.S."
End If
End Sub

I added a line of code to re-open the Master Engineering Spec file. That is
the one that is causing the error when you try to correct an input error,
because it goes away as soon as you execute the SaveAs. Try doing the
corrective update process using this version and see if it still errors out.
If it does you might have to unload the UserForm and and re-load it.

With regards to Dave's suggestion about the Public variable, I do not
believe he was aware that you are saving the form data in the Master
Engineering Spec file. He was attempting to allow you to use the same
variable for different workbooks as you open them. That would probably
prevent the error message, but not cure the logic problem. You would open a
workbook but not necessarily have access to your previous form data.