Open Text Method Fails

[Mike]

Hello,

I have an excel file hosted from a MS Sharepoint site. If a user opens the
file in Excel, the program executes fine. If the user opens the file in a web
browser my program terminates at the below line.

The error message states Run-Time error 1004. Method OpenText of object
workbooks failed.

Workbooks.OpenText Filename:= _
fname, Origin:=437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
, Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1 _
), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 2), Array(6, 1), Array(7,
1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1)),
TrailingMinusNumbers:=True

 

[Joel]

This is all you really need. the other part of the code is probably creating
the errors.


Workbooks.OpenText Filename:=fname, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
Other:=True, OtherChar:="|", _
TrailingMinusNumbers:=True

 

[Mike]

Thanks Joel.

I changed the code and despite the change, the code continues to break when
I attempt to execute the file from within a web broswer. If I open the Excel
file native to Excel then the code works like a charm.

Thanks,
Mike

 

[Joel]

Is it breaking on the same line? What is the filename?

Usually from a webbrowser you need to reference thisworkbook if it is
breaking elsewhere in the code.

 

[Mike]

Yes, it continues to break on the opentext line. The filename is obtained
from the user selection in the file dialog.

Could I add a new workbook and then reference the new workbook in the open
text method?

 

[Joel]

Is the owner of the file the one who is trying to access the file. Make
sure you can open the file without using excel application. The priledge of
the file may not be set properly.

I think it is a file sharing issue or a read/write priledge issue. If you
open a window explorer (Not internet explorer) right click the file/folder.
There is a sharing option (may need to check properties). Since the
application is on the web you have to allow the file to be shared.

 

[Mike]

I have open access to all authenticated domain users for the site and
location where the file is located. I am the admininstrator of the site so
privilidges should not be an issue.

 

[Joel]

What error are you getting?

Some things to try is the is a break on error option in the VBA tools -
options menu. Try changing the setings.

Remember when running on the web you are running with the version of excel
on the server not the excel on your PC. Even though you have priledges on
you PC you may not have the same privledges on the server where th ewebpage
is located.

I don't knnow where you placed the webpage but you may want to put it on the
C: drive in a location where you know you have adminstarative priledges.