Excel Macros error on file copy

[Snowcrash]

I am in need of some help with Microsoft Excel 2002 (SP3). I have created a spreadsheet with Macros and VBA behind it to do various data manipulations. Only limited data is actually stored so Excel was chosen as the approriate application for this work. Anyway, it all works super fine until I try and make a copy of the file. If I copy the file to a new location and leave the name the same and try opening the file, I get the error “A document with the name ‘MyExcelFile.xls’ is already open. You cannot open two documents with the same name, even if the documents are in different folders. …”

If I copy the file and rename it I get the error “MyExcelFile.xls’ could not be found. Check the spelling of the file name, and verify that the file location is correct…”

Now there is no where in my code that I refer to the file name MyExcelFile.xls nor is there in the macros. The security on my machine is set to medium to prompt with macros are encountered. The macros are saved into "This Workbook" so I would have assumed they would copy along with the workbook itself.

Do you have any idea what is happening here and how I can fix this? I have done some extensive searching but have not found any useful help.

Thanks in advance.

Snowcrash

 

[KenWright]

If you save the file somewhere else and try opening it whilst the original was open then you will get that error message. Are you saying that you have no files open but then try to open th enew one and then get that messsage?

When you say you rename the file, firstly which file are you renaming, the original or the new copied one, and what exactly do you do that prompts the MyExcelFile.xls could not be found ie are you trying to open it from the MRU list or are you using File / open or are you double clicking the file in Explorer.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[Snowcrash]

Ken

What I do is close my orginal file MyExcelFile.xls which is on the server. I then copy and paste it into Temp on my local directory as either MyExcelFile.xls or copy_MyExcelFile.xls. So the orginal still exists on the server but is definitely closed.

To get the errors, what I do is click a button on a customised toolbar. This toobar is enabled/visible when the workbook opens. Code for that is:

Private Sub Workbook_Activate()
Application.CommandBars("Questions_Toolbar").Visible = True
End Sub

Barb

 

[KenWright]

Any idea what the code is that the toolbar button calls?

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[Snowcrash]

Sub loadForm()
oktoGo = False
formQuestion.Show
End Sub 'loadForm

which is turn calls

Private Sub UserForm_Initialize()
On Error GoTo ErrHandler
globalFunctName = "UserForm_Initialize"

spinQuestionValue.Value = 1
textQuestionValue = spinQuestionValue
multipageQuestion.Value = 0
textQTitle.Text = "Question Title"
Call EnableDisableTabs(False, "allActive")
comboQType.SetFocus
Exit Sub
ErrHandler:
MsgBox ("Error occurred in " & globalFunctName & ". Contact Administrator")
End Sub 'UserForm_Initialize