Help in changing code to see new file name (Dave Peterson)

[Brian]

Dave can you help me with this. I am not exactly sure what you mean.

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.

 

[Dave Peterson]

In a general module:
Option Explicit
Public bk as workbook

In your userform:

'procedure to open the file:

set bk = workbooks.open(....)

In the procedure that saves the file.

bk.saveas filename:=...

'then to prove that the variable is still working
'no matter what the name changed to
msgbox bk.fullname

 

[Brian]

Everything works perfect except for the Update. I was playing around with and
it seems as long as the workbook name stays "Master Engineering Spec.xlsm" it
works great, but as soon as you save the Workbook and the name changes it
gives the Run Time error on this statement:

With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET")

Cause it's looking for a filename that is not open. Remeber the name when
the workbook was open was "Master Engineering Spec.xlsm", but after the
workbook was saved the name was changed to .

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

Would it just be easier to change the New Workbook name to the same format
as the exsisiting workbook name or would it still have the same problem?

I am still not exactly sure where to place the code. I posted all the code
prior to the site going down. Do you need me to repost all the code again for
you? If so I will in a new post.

 

[Dave Peterson]

That's why I keep suggesting using a variable:

With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET")
could be
With bk.Sheets("COVER SHEET")

You don't need to post the entire code for me. My suggestion won't change.