Excel and Automation

[Mattm]

This may be a Visual Studio question, but I thought I give you all a
try...

We're currently using VS 2005 to develop some of our apps. I have an
app that I developed that uses Automation to spit some data out to an
Excel spreadsheet. I have Visual Studio on two different Windows
machines, both XP, and I want to get my app to build on both (two
different evironments). It's currently working on one, but not the
other. I'm trying to build the app on the one machine that isn't
working and it appears it doesn't like some of the references. In the
solution explorer of Visual Studio, I open up the references under my
project, and the one reference labeled "Excel", has a little yellow
warning by it. I double click on it and it gives me a message that
says "This project cannot be viewed in the object browser because it
is unavailable or not yet built. Please ensure that the project is
available and built."

At first I thought that Excel wasn't installed on that machine, but it
looks like it is. I can bring up Excel, and it looks fine.

I'm not sure why I can't reference any of the Excel COM library info
from my application in Visual Studio. I'm not sure what it means in
the error message, when it says "unavailable or not yet built.". I
can run Excel on that system, so it seams like it should be
available. Do I need to do something in Excel to open up it's objects
to other applications. Any help would be appreciated. Thanks...

 

[GS]

Could this be an early binding issue?

Does your app create its own instance of Excel or try to use the
default instance?

Is your app testing to be sure Excel is available?
(ie: using CreateObject("Excel.Application")

 

[Mattm]

Thanks for responding...

Could this be an early binding issue?

Not sure. I try to compile using Visual Studio, so it's trying to
verify all my references ahead of time.

Does your app create its own instance of Excel or try to use the
default instance?

Here's some of my code (VB). I'm still getting my feet wet with
Automation, but what I'm trying to do is check for an instance if one
exists, and connect to that. If it doesn't, I create a new one.

Dim myExcel As Object
Try
myExcel = GetObject(, "Excel.Application")
Catch ex As System.Exception
' Do nothing, it's OK...
Exit Try
End Try

then later...

If TypeName(myExcel).Contains("Application") <> True Then
myExcel = New
Microsoft.Office.Interop.Excel.Application
End If

The error when I try to compile in Visual Studio 2005 is "Type
'Microsoft.Office.Interop.Excel.Application' is not defined". Visual
Studio doesn't seem to be recognizing the
Microsoft.Office.Interop.Excel.Application references and all the
references to anything in Microsoft.Office. Not sure why.

 

[GS]

Mattm formulated on Friday :

Thanks for responding...



Not sure. I try to compile using Visual Studio, so it's trying to
verify all my references ahead of time.


Here's some of my code (VB). I'm still getting my feet wet with
Automation, but what I'm trying to do is check for an instance if one
exists, and connect to that. If it doesn't, I create a new one.

Dim myExcel As Object
Try
myExcel = GetObject(, "Excel.Application")
Catch ex As System.Exception
' Do nothing, it's OK...
Exit Try
End Try

then later...

If TypeName(myExcel).Contains("Application") <> True Then
myExcel = New
Microsoft.Office.Interop.Excel.Application
End If

The error when I try to compile in Visual Studio 2005 is "Type
'Microsoft.Office.Interop.Excel.Application' is not defined". Visual
Studio doesn't seem to be recognizing the
Microsoft.Office.Interop.Excel.Application references and all the
references to anything in Microsoft.Office. Not sure why.

Okay, Matt. There's things you need to get straight about automation
from a 'good practices' perspective.

The first of these is that you never 'hijack' a users instance of
Excel.

Also, by creating your own instance you'll have more control over what
the user can access (or not access) AND whether the instance needs to
be visible or just use it 'behind the scenes'. In this case you'll want
to use CreateObject() as I've exampled if you don't use early binding.
If you do use early binding then you should set a ref to the earliest
version of Excel you expect your users to use. If you only have one
version installed on your dev machine then you won't have a choice as
that's the one you'll have to use.

Also, it would be a good idea to check if a user has Excel available.
Here's a function to do that:

Public Function bExcelAvailable() As Boolean
' Determines whether Excel is available for automation on user
computer.
Dim xlApp As Object

'Attempt to start an instance of Excel.
On Error Resume Next
Set xlApp = CreateObject("Excel.Application")
On Error GoTo 0

'Return the result of the test.
If Not xlApp Is Nothing Then _
bExcelAvailable = True: xlApp.Quit: Set xlApp = Nothing
End Function 'bExcelAvailable()

To use it:

If Not bExcelAvailable Then Exit Sub
'Abort the procedure, and/or display a message to users, and so on...


Since you appear to be using VB.Net, I suggest you ask about MSO
automation in one of the VB.Net forums. You can get a great book that
shows how to do this (and lots more) here:

http://www.appspro.com/Books/ProExcelDev.htm

 

[Mattm]

Thanks Garry, That helps. I appreciate you getting back to me. And
thanks for the book tip, I'm going to look into getting it.

One of the issues with my program is that it actually connects to
another program (not a Microsoft product), pulls data from it, and
then displays it in Excel. The problem is that if the user display
some of the data, and then closes Excel, the Excel process is still
running in the background (because my prorgram created the instance).
It's possible (and probable) that a user might exit out of Excel, then
with my program still running, want to create some more charts with
new data. Connecting to the same instance of Excel allows a more
manageable way to keep too many instances of Excel from getting out of
hand.

I have another quick question that you can probably help with. I'm
trying the set a default value in the directory for when the user
saves the data. I'm having trouble trying to do that.

I have an instance of the application in myExcel as in the above code,
and I'm trying something like:

myExcel.FileDialog().InitialDirectory = "D:\"

Am I on the right track...

Matt

 

[GS]

Mattm formulated the question :

I have another quick question that you can probably help with. I'm
trying the set a default value in the directory for when the user
saves the data. I'm having trouble trying to do that.

I have an instance of the application in myExcel as in the above code,
and I'm trying something like:

myExcel.FileDialog().InitialDirectory = "D:\"

Am I on the right track...

Hey Matt,

There is no property named "InitialDirectory". This is as close as it
gets:

myExcel.FileDialog(msoFileDialogSaveAs).InitialFileName
...which opens the dialog in the full path folder.

What you can do is use standard VB[A] syntax like:

CurDir OR ChDir
...before invoking a file dialog. (ie: myExcel.GetSaveAs

 

[GS]

GS wrote :

..before invoking a file dialog. (ie: myExcel.GetSaveAs

I twitched passing over the Send button! -Geez...! Above line should
be:

..before invoking a file dialog. (ie: myExcel.GetSaveAsFilename)

 

[Mattm]

Gary,

I tried to run

myExcel.FileDialog(msoFileDialogSaveAs).InitialFileName = "D:\"

But when I run it I get a runtime error. It says

Unhandled exception of type
"System.Runtime.InteropServices.COMException" occured in
Microsoft.VisualBasic.dll
Additional information: The server threw an exception. (Exception
from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))

Not sure what the problem is. Any Ideas...

Matt

 

[GS]

Mattm was thinking very hard :

Gary,

I tried to run

myExcel.FileDialog(msoFileDialogSaveAs).InitialFileName = "D:\"

But when I run it I get a runtime error. It says

Unhandled exception of type
"System.Runtime.InteropServices.COMException" occured in
Microsoft.VisualBasic.dll
Additional information: The server threw an exception. (Exception
from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))

Not sure what the problem is. Any Ideas...

Matt

Well, where's the filename in that?<g> Looks like a drive name only!

 

[Mattm]

I tried changing it to a file name, and it gave me the same runtime
error. I used the following

myExcel.FileDialog(MsoFileDialogType.msoFileDialogSaveAs).InitialFileName
= "C:\book1.xls"

Still no luck. I'm not sure what I'm doing wrong...

Matt

 

[GS]

After serious thinking Mattm wrote :

I tried changing it to a file name, and it gave me the same runtime
error. I used the following

myExcel.FileDialog(MsoFileDialogType.msoFileDialogSaveAs).InitialFileName
= "C:\book1.xls"

Still no luck. I'm not sure what I'm doing wrong...

Matt

Matt, there's more to it than just that. Look in online help for
example usage. Here's how I would use it...

====================== <air code>
Dim fDlg As FileDialog
Set fDlg = myExcel.FileDialog(msoFileDialogSaveAs)

With fDlg
.InitialFileName = "D:\" '//set the drive to open
If .Show = -1 Then '//"Show" method displays it
'Iterate the selected items as per online example...
Else
'User cancels
End If
End With
Set fDlg = Nothing
====================== </air code>

Use the VBE Object Browser. Select "FileDialog" in the 'Classes' list.
Select "InitialFilename" in the 'Members' list. Press F1.

 

[GS]

GS was thinking very hard :

Dim fDlg As FileDialog

This should read...

Dim fDlg As myExcel.FileDialog

 

[Mattm]

Thanks Garry, you've been very helpful (and patient). I'll give that
a try...

Matt