Help with Workbook name change on update

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.
0
Utf
1/10/2010 1:36:01 AM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
1161 Views

Similar Articles

[PageSpeed] 40

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.




"Brian" <Brian@discussions.microsoft.com> wrote in message 
news:04B08D56-132A-4FDA-93DB-CA995E6A7E6F@microsoft.com...
> 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. 


0
JLGWhiz
1/10/2010 3:25:11 AM
Reply:

Similar Artilces:

I want to unlock my word doc to make changes its protected
I am writing a word document the other night. I went to carry on with it tonight but found it has protected the document and won't let me continue writing or editing Word 2007? Assuming that you have activated Office, it seems your trial version of the application has expired. Time to pay the piper! -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> ...

Macro Help 11-24-09
I have one workbook of data (1 tab) that has data for 20 different Sales Reps (different names). I need to copy all data for "Rep A" into a separate worksheet, and same for "Rep B" and so on. At the end I would have 1 tab for all data and 20 tabs with the data for each rep. Basically, I need to copy and paste each rep data into a new worksheet within the same workbook but didn't want to do it manually. I hope this makes sense. See Ron de Bruin's site for code. http://www.rondebruin.nl/copy5.htm Also check out his easyfilter add-in. http://www.ro...

Changing SQL Server for CRM 1.2
As my SQL server is currently on its last breath I am in the process of moving our CRM 1.2 databases to a new dedicated serever. I have detatched the databases from the old server, copied the files to the server and attached them. I have then gone into CRM deployment manager and changed to the SQL server to the new server. That all appears to work fine, no error messages. But when users try to access the CRM system they get an error and the CRM server event log fills with error messages. A few are listed below: dmLog: Failed to execute the SQL batch in the file sqlbatch.sql. MSCRM P...

help need with VC 6.0 IDE and mfc
Hello, First let me explain the scenario where i m using this requirement. We are Using CustomAppWizard and designing a wizard .One of the wizard pages will Insert Composite controls as many as the user wants . 1.So i should be able to dynamically insert ATL controls without using Insert Control Dailog. 2. can any one tell me how to dynamically create Template file in TEMPLATE folder of resource view . 3. I want to include many files created by templet files and add them to build by editing newproj.inf Is it possible to do this. 4.I would even like to know if i have 2 ifles in my C drive h...

Territory Change
Our Sales VP has restructured all of the geographical territories that we had set up in Microsoft CRM. Our many thousands of Accounts are associated with territories. Obviously it is ludicrous to think that we would have to go one by one and change the territory on each account to the new territories. However, I have heard that there are strict rules for making changes directly to the backend SQL database. If we develop a SQL statement to change the territories assigned to each account to the new territories, are we going to be breaking anything? We don't want to screw up any de...

chart MAcro to change on activecell
Here is the macro below.. The only problem is that the columns change every month. so range (columns)keeps widening.... I have sorted it by selecting range upto column N. so it is provided for all 12 months. But now the "Grand total" column which is always the last column also gets included..(.which shouldnt be included in the range for the chart) Is there a way to modify this macro ? Sub updatechart1() Dim ThechartObj As ChartObject Dim Thechart As Chart Dim Userrow As Long Dim CatTitles As Range Dim SrcRange As Range Dim SourceData As Range If Sheets("summary").Ch...

Help, I cannot Save!
I created a document and locked the worksheet to protect the formulars before creating a template for the document. But now when I open th document and insert a new sheet using the template I created, th document will refuse to save. Once I click on save, office assistant will say "doc not saved". Wha could I have done wrong? PLease help. computerfinema -- computerfinema ----------------------------------------------------------------------- computerfineman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3716 View this thread: http://www.excelforum.c...

Methodology
I'm looking for some tips for how to manage naming conventions within and across Visio files. If you have a subprocess that is used in/called by multiple processes, what would be a good way to name the "start" and "end" shapes? For example, if you were modeling something simple such as "serve breakfast", "serve lunch", and "serve dinner", all three would have a subprocess called "set table". The "set table" process is the same regardless of the main process that calls it. Do you just call them "start&...

update column
How would I update a column with numeric values so that there are 3 leading zeros for each row? hi it is not possible to add leading zeros to a numeric value. Mathematically, this is redundent and unnecessary. "brian" wrote: > How would I update a column with numeric values so that there are 3 leading > zeros for each row? opps. hit the post button too quick. option 1. custom format if your numeric value is 12345 then see the custom format to 00000000. note. format do not change data - it just changes the way it looks in the cell. option2. format to text then use the c...

Help Required
Hi, Whenever I open Outlook 2003, I am getting a dialog box which displays the following message: Microsoft Office Outlook has encountered a problem and needs to close. We are sorry for the inconvenience When I click Debug it displays a message box with the following error message "The instruction at "0x3007e993" referenced memory at "0x0000000:. The memory could not be read" When I click No it Visual Studio JIT debugger pops up. I uninstalled and installed several times but still the problem persists. Is there any regsitry entry that I've to modify/delete? ...

Changing font in Money 2004 register?
Is there any way to change the font(s) used in MS Money 2004's registers? The default font is too small and difficult for me to read. Also, is there any way to change the color scheme to something more pleasing to my eye (like you can do in Quicken...) Thanks. Nope and Nope. See http://umpmfaq.info/faqdb.php?q=136. "Debbie R." <debbimsr@bellsouth.net> wrote in message news:f5ff01c43e15$e2ae3700$a401280a@phx.gbl... > Is there any way to change the font(s) used in MS Money > 2004's registers? The default font is too small and > difficult for me to read....

HELP Recovering addresses and email from Outlook 2003
I had some serious driver issues that required re-installing XP from disc. I did use the backup option and have a backup of all the old data. And of course had to reinstall Office 2003. Will third party software restore my old email and addresses or am I out of luck?? Thanks for the help texraid wrote: > I had some serious driver issues that required re-installing XP from > disc. I did use the backup option and have a backup of all the old > data. And of course had to reinstall Office 2003. > > Will third party software restore my old email and addresses or am I > out of lu...

Need Help with Deleting Empty Paragraphs in Word 2003
I have written the code below to delete all empty paragraphs at the end of a document and then place the cursor at the end of the last paragraph. It works fine as a stand alone sub in a new doc, but fails inside the real document that contains other code that manipulates several documents. The failure is that it will delete the last empty para, but then gets stuck looping inside the While...Wend because subsequent .Delete are not happening. So, the question is why would this work in one document, but then fail in another? n = 0 ...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Change the Exchange Virtual Directory to different website
I would like to remove the exchange virtual directory default website and move it another website which is currently redirecting to the website I want to delete. Meaning rather than logon to OWA as http://www.wheresmylunch.com/exchange (current default website) I want to move to http://www.getyourownsandwich,com/exchange. I am using Exchange 2000 server. Rube You would change the host header on the current website. -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply t...

Meeting updates #2
My users cannot update meetings created when they were on the old email server. I have noticed that the old string is still mapped to the meeting. e.g x400;c=us;a= ;p=Org name;o=exchagne;s=Lastname;g=firstname; Take a look at the following article: 275134 XADM: Cannot Reply to Messages That Are Sent from a User Account That http://support.microsoft.com/?id=275134 The same thing applies to meetings. How did you move them and what version(s) of Exchange? Thanks, Richard Roddy Microsoft Exchange Support This posting is provided "AS IS" with no warranties, and confers no ri...

Renaming Partial File Names
Is it possible to rename part of a file name? (almost like a find and replace) My database pulls in the excel file names from a directory, with it's subfolders and contents as well. Every file begins with "Kay Form". Kay no longer works in our department, and I'd like to run a loop that replaces "Kay Form" with "Featured Track". I don't know how to do a partial replace though. Thank you in advance! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201001/1 Checkout the Replace function. I...

Help me identify my missing permission (Cannot open public folder) -2147217843 (Maybe Authentication Fails?)
The following snippet of code throws an error number -2147217843. When I googled this error code, I see many references to authentication failed. I am assuming my problem is some kind of permission related problem on the "MyNewFolder" public folder. -- start code --- Dim objFolder As New CDO.Folder Dim f As ADODB.Field 'sURL is like: file://./backofficestorage/mydomain.com/Public Folders/MyNewFolder/ objFolder.DataSource.Open sURL, , adModeReadWrite, adFailIfNotExists --- end code -- I have code that runs before this that actually creates the "MyNewFolder" publ...

Money Plus not Updating Quotes
For the past couple of days Money Plus has not been automatically updating stock quotes and manual quotes does not work either. I should add that this problem has been intermittent for the past couple of days. Any suggestions? In microsoft.public.money, D.Duck wrote: >For the past couple of days Money Plus has not been automatically updating >stock quotes and manual quotes does not work either. > >I should add that this problem has been intermittent for the past couple of >days. > >Any suggestions? > High server loading. Try again later. In microsoft.publi...

Excel Opens Without Displaying Workbook
I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Displaying Workbook (http://support.microsoft.com/default.aspx?scid=kb;en-us;158996&Product=xlw97), but neither of the resolutions fixed the problem. Any suggestions?? Are you using Excel 97? -John Baughman Fort Collins, CO >-----Original Message----- >I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Di...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

Money 2000 Account Balance Changed Inexplicably
Opening Money 2000, which I've used without problem since late 1999, I noticed that my checking account balance was overstated by almost $2000! I went to the register to see if there was a false transaction entered and it appears that this balance change goes back years with no obvious single entry being the culprit. When I run the "balance this account" function, it shows that the closing balance from my last statement, which was correct and rectified, is now also wrong and reflects the higher balance. What do I do now? The only thing that I can think of is to restore...

Shortcut for inserting names?
Hi, I am building many formulas, each with many named cells as part of it. Currently I have to go to Insert | Name | Paste with the mouse each time I want to insert a name. I looked in Walkenbach's Excel Bible but couldn't find any mention of a keyboard shortcut or other shortcut. Is there one I could take advantage of? I've got Excel 2002. Thanks, Jamie ...

Online Price Updates
Somehow online price updating was turned on in my file. I have turned it off. However, when I go to delete the erroneous past-dated Online Prices for a particular stock, it appears they are initialling deleting, but when I return to the stock later, all the deleted online prices (from the past) are re-instated. It is as though none of the price deletions I performed have taken effect. How do I clean up these erroneous past online price updates? ...

Changing language
I am running the Swedish version of Excel for XP and I need the US (or English) version. Is there a way to convert the language and all the settings associated with it? Using the swedish version is rather annoying since the formula names are translated to swedish. Thank you, Magnus ...