Repost with code - Runtime error '1004'

[muziq2]

Hi all, (see code below)

Some users are getting the following error.

"Run-time error '1004' Method 'open' of object 'workbooks' failed."

Obviously I'm not sure why. The user has to click on a link that open
the excel sheet. Then they click on a button on the sheet that open
another sheet read only and updates the original sheet. When the butto
is clicked the error is showing up. The files are in the same director
so if the user is able to open the file via the link the user shoul
also be able to open the other file.

This is happening in excel 2000 but the file works fine on my box i
excel 2000.

Any ideas?

Thanks,

Jeff

Private Sub UserForm_Initialize()

Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating

Set wb = Workbooks.Open("\\sfna-fs4\hrs\shared\CT
Training\ctmTrainingData.xls", True, True)
' open the source workbook, read only
With ThisWorkbook.Worksheets("Training")
' read data from the source workbook
.Range("A1:B13").Formula
wb.Worksheets("Sheet3").Range("A1:B13").Formula
End With
With ThisWorkbook.Worksheets("SignUp")
' read data from the source workbook
.Range("A:B").Formula
wb.Worksheets("SignUp").Range("A:B").Formula
End With

wb.Close False ' close the source workbook without saving an
changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating

cmbName.Value = ""
End Su

 

[Jake Marx]

Hi muziq2,

Some users are getting the following error.

"Run-time error '1004' Method 'open' of object 'workbooks' failed."

Obviously I'm not sure why. The user has to click on a link that opens
the excel sheet. Then they click on a button on the sheet that opens
another sheet read only and updates the original sheet. When the
button is clicked the error is showing up. The files are in the same
directory so if the user is able to open the file via the link the
user should also be able to open the other file.

This is happening in excel 2000 but the file works fine on my box in
excel 2000.

Since the code is being executed via a CommandButton, you should make sure
the TakeFocusOnClick property of the CommandButton is set to False. You can
run into random runtime errors if it's set to True.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[muziq2]

Seems that this problem has something to do with how the user accesse
the file.

However, I created the link to the file in an Outlook email that
didn't send and received the run-time error after opening the file an
clicking the CommandButton.

After sending the email to myself and clicking on the link I no longe
get the message.

When I access the file via open or explorer it also works fine.

Below is the link that causes the error.

\\sfna-fs4\hrs\shared\ctm training\ctmtrainingsignup.xls

The following link below does not cause the error.

<\\sfna-fs4\hrs\shared\ctm training\ctmtrainingsignup.xls>


Note that with macro security set to medium opening the above link
causes different warning messages.

The first link (the one that produces the run-time error) gives
warning that the file contains macros that may contain harmful viruse
and prompts the user to press yes, no or cancel.

The second also gives a warning that says the file contains viruses bu
prompts the user to either press one of the following buttons: disabl
macros, enable macros, more info.

Any ideas on why the two seperate messages? Or why the one causes th
run-time errors.

Thanks for your response Jake. Whether the takefocusonclick is enable
or disabled there are run-time errors

 

[Jake Marx]

It sounds to me like the first link is attempting to open Excel in Internet
Explorer, as I think that's the macro security prompt you get when opening
an XLS in IE. Spaces in UNC paths can cause issues if you don't use the
brackets like you did in the second example. Is everything working if you
use the <>?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[muziq2]

As far as I know everything works fine when the brackets are used.
Thankfully that's the format that was used when the link was sent to
all users.

Thanks for your help.

Jeff

 

[muziq2]

Actually, I just figured out the cause of this problem. Outlook 200
automatically recognizes the link. I do not have to put the brackett
in. The bracketts disappear when the email is opened in it's ow
window. This is when the error occurs.

Everything works fine when the email is viewed in the preview pane.
Not sure why this is.

Definitely a question for the outlook newsgroup.

Thanks,

Jef