Open Text Method Fails

M

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
 
J

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
 
M

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
 
J

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.
 
M

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?
 
J

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.
 
M

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.
 
J

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top