Open method of workbooks fails when Excel is hosted in IE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Excel macro in an xla addin that calls the Workbooks.Open method. The piece of code works fine if I just execute it from Excel normally

However, if I call the macro via an instance of Excel that is hosted in Internet Explorer (in .Net as it happens) the Workbooks.Open method fails with the error "Method 'Open' of 'Workbooks' failed".

I'm using Office XP on Windows XP Pro

I've discovered loads of people in the newsgroups who have had this problem, but I haven't see any answers. Surely their must be one?
 
Hi samuel,

Thanks for posting in the community!

From my understanding to this issue, when you open another workbook through
one xla from one workbook in the internet explorer window, you got one
error 1004 and failed to open another one.

Opening an Office document may result in the error 1004:
"Run-time error '1004':
Method '~' of object '~' failed"
Typically, this is the result of failing to initialize VBA due to
insufficient permissions or due to a lack of VBA component registration.
Generally speaking, both of them are typical when a user runs code from an
account with no user profile and the user token does not contain the
Interactive SID.

In your scenario, Internet explorer(IE) is not designed as one
multi-document interface which means only one document is allowed to be
opened in the same window. Therefore, "open" is not a valid action in IE
which caused the error 1004. I'd suggest you can open another workbook from
one link in the IE. When you click the link to open another workbook in the
same IE window, the opened workbook will be closed.

Furthermore, I'd also suggest you can configure at the client side to open
the excel workbook in Excel Application directly, not in IE window. In this
way, you can feel free to use the xla to open another workbook. The kb
article introduces the detailed configuration steps for you concerning this:
162059 How to Configure Internet Explorer to Open Office Documents in the
http://support.microsoft.com/?id=162059

Please feel free to let me know if you have any further questions. I am
standing by to be of assistance.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
We want Excel sheets to appear as part of our User Interface: since we are using .Net, which has (as yet) no Active Document container, we found that the only to do this was by loading up Excel inside IE. Therefore we want to avoid having to open up Excel seperately

With regard to the use of the Open method: I'm not wanting the opened workbook to be displayed. In fact the 'workbook' that I'm trying to open is a .csv file that I ultimately want to paste into a sheet in the currently opened workbook

Is there any way to get VBA to initialise properly for sheets that are embedded in Excel? Is it likely that many of the Excel commands will fail if I call them through a workbook that is open in Excel? Should I abandon this approach of opening Excel inside IE

Thanks for your help and advice
 
Hi samuel,

Thank you for replying and more information about the troubleshooting!

From my understanding now, you want to display the Excel data in .net
control and since there is no active document container for workbook, you
host the Excel workbook directly in IE window. Then you tried to open one
CSV file through the IE-hosted workbook, which reported the error 1004.

Based on my experience, if you want to display the Excel data in the Web,
the best choice is to use Office web component(OWC). The spreadsheet will
help you a lot in this scenario. I'd suggset you can use this instead of
the IE-hosted Excel workbook. You can transfer the Excel data in XML format
to the spreadsheet control and read the CSV file directly from the
spreadsheet and display the CSV as one new sheet in the spreadsheet.

Microsoft has released one OWC toolpack which contains the code sample and
new feature illustration for you which will help you obtain the OWC feature
very quickly. I list the owc download link with one useful MSDN link below:
Microsoft Office XP Web Component Toolpack
http://www.microsoft.com/downloads/details.aspx?FamilyId=BEB5D477-2100-4586-
A13C-50E56F101720&displaylang=en
Working with the Office Web Components
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/htm
l/deovrworkingwithofficewebcomponents.asp
The OWC version for OfficeXP is 10 version. And the newest version is OWC
11 in Office2003. This kb article 828950 will introduce the deploy method
for OWC 11.
828950 How to Deploy the Office 2003 Web Components in an Office 2003
Program
http://support.microsoft.com/?id=828950

Furthermore, if you are not going to use the spreadsheet now, I'd suggest
you can build one demo project with some necessary note for it. Then zip
them and mail the zipped pacakge to me. I will test this and try to provide
more assistance for you regarding this issue. To send me the file, please
remove "online." from my no spam alias "(e-mail address removed)". After
your delievering, please post in this thread to let me know. If I haven't
received this file, I will tell you my another email address. Thank you in
advance!

Please feel free to let me know if you have any further questions. I am
standing by to be of assistance.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
We've looked at OWC before and decided that they are not suitable. The main reason for this is that the Excel workbook that we are wanting to display (and into which we are wishing to insert data from the CSV file) is preconfigured with a number of formatted tables and graphs which update themselves from the CSV data. It would not be feasible for us to rebuild all these tables and charts in OWCs

We have managed to work around not being able to call the open method by using VBA to open the file and read it in line by line and then copy it into appropriate worksheet

Unfortunately I can't send you a demo project because I haven't got time to cut down my code

What is your opinion about the advisability of using Excel in this way? Is it likely to prove probablematic when we deploy it to clients

Thanks for your help
Sam
 
Hi samuel,

Thank you for replying and more information about the troubleshooting!

We have discussed several designs for your scenario:
1) Your original design, open another excel workbook directy form one
IE-hosted workbook
2) provide one link(or other method(s), for example client jscript) to open
another workbook in a new IE window
3) Open the workbook in Excel at client side
4) using OWC

It is limited to open another one directly from the IE-hosted Excel
workbook because IE is one single-document interface, not a multi-document
interface which means the first one is not applicable. The third design is
not suitable for your scenario. And the owc will also not be suitable for
the scenario.

Based on my experience and your scenario, I'd suggest you can choose the
second design. In this ways, you will only need to provide one link or
button for the client user to open another IE to host the new workbook
without modifying a lot of existing codes. Furthermore, there is many ways
for you to open one new IE window at client side which will be easy for
your implementation.

Please feel free to let me know if you need further assistance.

Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top