Problem Opening Excel Document

[MethadoneBoy]

Hi guys,

I've been scanning through a few previous threads on accessing and manipulating Excel documents via VBA. I've found a lot of useful information (thanks, btw!) but I've come up against a problem that doesn't seem to be dealt with by anything previously.

I'd be delighted if anyone here could help. My problem involves opening an Excel document. This is the code I'm currently using -

Public Sub ReadExternal()

Dim appExcel As Excel.Application
Dim appBook As Excel.Workbook
Dim appSheet As Excel.Worksheet

End Sub

Whenever I try to call that method (from a button on a form) I receive the following message -

Compile Error - User-defined type not defined.

The compiler seems to take exception to the "Dim appExcel As Excel.Application" line.

Any advice would be most welcome.

Thanks.

[kishore.kr]

hi

in references select "microsoft excel 9.0 object library". or any version(9.0) will do. the error occurs the type is not defined. in this case the excel dll library is not referenced by the project.

[MethadoneBoy]

hi

in references select "microsoft excel 9.0 object library". or any version(9.0) will do. the error occurs the type is not defined. in this case the excel dll library is not referenced by the project.

Hi,

Thanks for the help. Sadly, my version of VBA (I'm working from Microsoft Access Office 97) doesn't appear to have a References option in any of the menus. Is there an alternate way of doing it for Office 97 applications?

[kishore.kr]

are you sure office 97 has no references option. bcos in 2000 its in tools. check help if you can find any other alternative. i am not used to in vba so i dont have exact idea.

[kishore.kr]

hey !!

i checked google, as far i get it from them., its in the tools->references., the version of the library is 8.0 or lesser. check it out again

[MethadoneBoy]

I only realised now that the References feature appears to be disabled on my machine.

I'll report back once I hear from the system administrators here ;-)

Thanks again!

[MethadoneBoy]

Ok. Turns out the feature wasn't disabled - I was still running the program when I tried to access it. *Slaps self on the head*

Sorry about that. I've now added the reference for Microsoft Excel 8.0 Object Library.

Still the same error occurs.

I then tried to add the reference for the Excel 5.0 Library. It then returned an error saying "Name conflicts with existing module, project, or object library."

Any ideas?

[kishore.kr]

hi
try removing all the excel object libraries and then selecting the excel 5.0 library.

[MethadoneBoy]

Tried that as well and no such luck, unfortunately. It's very perplexing.

[Ecniv]

Does the workbook have any sensitive data? If not, put it in a zip file and post up. We could perhaps check it to our versions.

References are in the VBA editor. The Excel one should already be loaded as you are in Excel. You may need the New command word:

Dim appExcel As New Excel.Application
Dim appBook As new Excel.Workbook
Dim appSheet As new Excel.Worksheet

This is if you want a new instance.

If you are in Excel and want to open another workbook, use application.workbooks.add (check the help file on it).