How to connect to current active Excel Workbook from standalone Visual Basic program?

I have an application that needs to verify if Excel is running ( doing this through System.Management.ManagementObject iteration) and if it is, to connect to the currently active workbook and fetch the list of worksheets.

I cannot for the life of me figure out how NOT to say

Dim xlApp As Microsoft.Office.Interop.Excel.Application

xlApp = New Microsoft.Office.Interop.Excel.Application

xlApp.Visible = False

xlBook = xlApp.Workbooks.Open(xxx)

but rather something like

xlBook = Microsoft.Office.Interop.Excel.Application.ActiveWorkbook

(I know that is wrong but that's the best I could think of). Can you please show me how to set xlBook to the currently active Excel Workbook

Answer this question

How to connect to current active Excel Workbook from standalone Visual Basic program?

Duzinga

Found the answer after extensive search

'get name of active workbook

xlApp = GetObject(, "Excel.Application")

xlBook = xlApp.ActiveWorkbook

ComboBox1.Items.Add(xlBook.FullName)
May May
But if you want just to open and insure the excel sheet is visible, you can do the following:

Dim prcProcess As New System.Diagnostics.Process

With prcProcess

With .StartInfo

.FileName = "C:\book1.xls"

.UseShellExecute = True

End With

Call .Start()

End With

prcProcess.Dispose()

prcProcess = Nothing

Excel will not open it again if it's active and hence it will automatically active this sheet.

You may connect to excel afterwards.

Hope this can help.

Best Regards,

Amr Ouf

If you see this is the answer to your question please mark it as an Answer
Tania-chan

I see that you use excel in the background and you have no intent to view it.

If you use excel to fetch data there's a better way you can do.

You can Use JET 4 connection to open the excel sheet via OLEDB Connection and use the command to open the sheet.

Here's the workbook is considered a database , the sheet is a table , the column is field and the row is a record.

It's faster and stand alone just need the jet which is installed with MDAC 2.x (Shipped with Windows XP and installed without any other patch).

You can also write to fields and execute DMLs as if you are using a database engine.

This is a sample code illustrates the process:

Dim strDatabaseFileName As String = "C:\Book1.xls"

Dim conConnection As New Data.OleDb.OleDbConnection

Dim cmdCommand As Data.OleDb.OleDbCommand

Dim rdrReader As Data.OleDb.OleDbDataReader

Dim intFieldCounter As Integer

'Open Database file

conConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabaseFileName & ";Persist Security Info=False;Extended Properties=Excel 8.0"

conConnection.Open()

'Open Sheet1

cmdCommand = conConnection.CreateCommand()

With cmdCommand

.CommandText = "Select * from [Sheet1$]"

rdrReader = .ExecuteReader()

'Loop on records

While rdrReader.Read()

For intFieldCounter = 0 To rdrReader.FieldCount - 1

MsgBox(rdrReader.GetName(intFieldCounter) _

& "=" _

& rdrReader.GetValue(intFieldCounter).ToString())

Next

End While

Call .Dispose()

End With

'Terminate Objects from memory

rdrReader.Close()

conConnection.Close()

conConnection.Dispose()

cmdCommand = Nothing

rdrReader = Nothing

conConnection = Nothing

Hope this can Help!

Best Regards,

Amr Ouf

If you see this is the answer to your question please mark it as an Answer
LiquidAsh

Thanks for the replies but I have been there before.

I had the OLE access in there initially but was always left with Zombie Excel instances that skewed the application count on the next run, and required a task manager intervention... (I did release everything to the best of my knowledge but the OS seems to cling on to OLE sources)

My problem is that I DO NOT KNOW the name of the file for the ActiveWorkbook. This is dynamic... If I can simply attach to the ActiveWorkbook then I don't need to know the name. But I will not complain if I can find the name and then use the method you describe above.
How to connect to current active Excel Workbook from standalone Visual Basic program?

Answer this question