Workbooks.open is not working

[Harald Battran]

Hi,

can anyone open my eyes on this strange problem:

I need to open a workbook on the fly to be able to read data from it
via VBA.

This piece of code is working while on a button called via click:

Sub Schaltfläche2_KlickenSieAuf()
Workbooks.Open ("C:\Dokumente und Einstellungen\name\Desktop
\Prototyp fuer Michael\Test2.xlsm")
End Sub

This piece is NOT working in a VBA module called with the formula
update in a cell :

Public Function Dateiopen()
Dim temp As Workbook
Set temp = Workbooks.Open("C:\Dokumente und Einstellungen\name
\Desktop\Prototyp fuer Michael\Test2.xlsm")
temp.Activate
End Function

by the way: this is also not working

Public Function Dateiopen()
Workbooks.Open("C:\Dokumente und Einstellungen\name\Desktop
\Prototyp fuer Michael\Test2.xlsm")
End Function

Both files are in the same folder. I am running Excel 2007 and have
only two causes I can think of

1) I am blind and missing something really important
2) There is a security setting I should check to allow the routine to
open up an external file.

Anyone out there who can help?

Much appreciated!
Harald Battran

 

[Gary''s Student]

I don't think Open will operate in VBA:

Function fails_with_no_error() As String
fails_with_no_error = ""
Workbooks.Open Filename:="C:\a.xls"
End Function

This does not work and returns no error. However Shell does works:

Function seems_to_work()
seems_to_work = ""
seems_to_work = Shell("cmd.exe /c C:\a.xls", 1)
End Function

For a real app, the function should:
1. insure that a.xls has not already been opened
2. activate a.xls and get data needed

 

[Harald Battran]

Hi Gary''s Student,

I don't think Open will operate in VBA:

Do you know? The method is described but nowhere I can find
restrictions or anything why it should not work.

This does not work and returns no error. However Shell does works:

Correct! No error what so ever! That's what irritates me!

seems_to_work = Shell("cmd.exe /c C:\a.xls", 1)

Will try tomorrow. As I only need to open the file once It should be
ok.

Could I probably start the Macro from the VBA-Code? Will try that as
well.

For a real app, the function should:
1. insure that a.xls has not already been opened
2. activate a.xls and get data needed

The function does this already. But I just cut out the lines which are
failing. And the function is working fine! As long as the other file
is already open!

Thanks
Harald Battran

 

[Gary''s Student]

I can get Open to work in a module, but not in a function.