Excel Addin- Processing of data while opening excel files in EXCEL

[Rp007]

HI

while opening any excel files (.xls files) in MS EXCEL.EXE, I need to do
some data existence check in that xls file. How do I do this. Can anybody
shares the brief details.

I tried developing COM Addin using C# but could not get the corresponding
event for opening the exisitng workbook.

Any help would be great and appreciated.

I am using OFFICE 2003 and Visual Studio .Net 2003 on FrameWork v1.1



thanks,
-Ram.

 

[XL-Dennis]

Hi Ram

I'm not yet a C#-person but it's my understanding that reading VB-code for
C# is rather easy compared with opposite situation.

Below is just a snippet code on how to open a specific workbook and don't
forget to add the reference to the Excel:

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
Dim xlApp As New Excel.Application
Dim xlWBook As Excel.Workbook = xlApp.Workbooks.Open("c:\test.xls")

'Add Your code to do the data check here...

'Make Excel and the workbook available for the user.
With xlApp
.Visible = True
.UserControl = True
End With

'Housecleaning
xlWBook = Nothing
xlApp = Nothing

'Force garbage collect
GC.Collect()
GC.WaitForPendingFinalizers()

End Sub

 

[Rp007]

Hi Dennis,

Thanks for the reply. But I am looking for the different scenario, the user
can open any .xls file and launch them in EXCEL.exe. In your code snippet
you are refering a particular xls file (c:\test.xls) and logic will be
limited to only on that xls file unless you hard code with some other xls
file.

The reuirement scenario what I have is, the user can open any xls file in
EXCEL.EXE and my addin/application should work in back ground and look for
the existence of particular type of data (specific data pattern) then update
some [m,n] cell with YES/ NO values etc.,

ALL: Please share any of your thoughts if you come across this kind of
scenario.

thanks,
-Ram.

 

[XL-Dennis]

Ram,

OK and thanks for the feedback.

If You want to show the built-in open dialog in Excel the following snippet
VB.NET code gives the basic for it:

Dim xlApp As New Excel.Application
Dim vaFile As Object

With xlApp
.Visible = True
.UserControl = True
End With

vaFile = xlApp.GetOpenFilename("Excel Files (*.xls),*.xls", , "Open
Excel file", "Open", False)

Try
'Open the selected file.
xlApp.Workbooks.Open(CStr(vaFile))

'Do your stuff

Catch

Finally
'House cleaning
xlApp = Nothing
End Try

The above approach can also allow the users to select several files at one
time (if wanted) by setting the parameter MultiSelect to True. The variable
'vaFile' always return an array which allow you to iterate through it.

An alternative approach would be to use the OpenFileDialog component in a
form.

Let me know if the above is doable for You.

 

[Cindy M.]

Hi =?Utf-8?B?UnAwMDc=?=,

while opening any excel files (.xls files) in MS EXCEL.EXE, I need to do
some data existence check in that xls file. How do I do this. Can anybody
shares the brief details.

I tried developing COM Addin using C# but could not get the corresponding
event for opening the exisitng workbook.

This would certainly be the right approach...

Can you be more specific about "could not get the corresponding event for
opening the workbook"? Do you mean you couldn't find the event?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Rp007]

Hi Dennis,

I just followed the approach you mentioned below but still not satisfying
for the solution. Reason is, we are forcing the user to choose .xls file in
Excel FileOpen dialog so as a result, two files are getting opened in
EXCEL.exe. One file which got opened was the one user choosen by dubble
clicking from Windows explorer or desktop etc., and the second file got opend
was the file which user selects through Excel file Open dialog. The
interesting part is, in my Excel COM Addin code and OnConnection event the
Excel.Application object has Workbooks count is only 1 and which refers the
file choosen from Excel FileOpen dialog. The file which has been choosen by
double clicking the .xls from Windows explorer or desktop etc., was totally
not traceable in the OnConnection event.

The requirement scenario is, Use can open xls file in the following manner:
1. User can choose xls file from windows explorer from the path where ever
it exists.
2. User can choose xls file from their desktop, if any xls files exists.
3. User can just launch EXCEL.exe then user can choose menu option FILE
---> OPEN then chooses the xls file.
So all the above scenarios I need to see some data existence in that xls
file then process and update some cell in that file before showing it up in
EXCEL.exe to the user.

I am thankful for your time for replying to the above.

regards,
-Ram.

 

[Rp007]

Hi Cindy,

Yes, I could not able to figure out the corresponding event which occurs on
opening of the existing xls file in EXCEL.exe. My COMAddin code which
implements IDTExtensibility2 interface has the events (OnConnection,
OnAddinUpdate, OnStartUpComplete, OnBeginShutDown, OnDisconnection)which
refers for the host application i.e. EXCEL.exe and there is no event listed
for referencing on opening the xls file (Excel Workbook).

Interesting part is, when I choose the existing xls file from windows
explorer (or desktop etc.,) by doubble clicking, I am not getting the
reference to this file in OnConnection event code. i.e the Excel.Application
object's Workbooks.Count shows '0'. The same is not true if I just launch
EXCEL.exe, the count shows as 1 and refers to book1 (book1.xls) as this is
the default.

The requirement scenario is, User can open xls file in any of the following
manner:
1. User can choose xls file from windows explorer from the path where ever
it exists.
2. User can choose xls file from their desktop, if any xls files exists.
3. User can just launch EXCEL.exe then user can choose menu option FILE
---> OPEN then chooses the xls file.

All the above scenarios I need to see some data existence verification in
that xls file then process and update some cell in that file before showing
it up in EXCEL.exe to the user.

Any details towards the solution, great to hear.

thanks,
-Ram.

 

[XL-Dennis]

Ram,

Thanks for the detailed information - highly appreciated.

In the connection class add the following on top of it, which will give you
access to all events on the application level (choose xlApp in the top left
drop down and then select the right top drop down to choose the wanted
event(s) in the IDE)

Private WithEvents xlApp As Excel.Application

Public Sub OnConnection(ByVal application As Object, ByVal connectMode As
Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom As
System.Array) Implements Extensibility.IDTExtensibility2.OnConnection

xlApp = CType(Application, Excel.Application)

End Sub

Public Sub OnDisconnection(ByVal RemoveMode As
Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements
Extensibility.IDTExtensibility2.OnDisconnection

xlApp = Nothing

End Sub

Private Sub xlApp_WorkbookOpen(ByVal Wb As
Microsoft.Office.Interop.Excel.Workbook) Handles xlApp.WorkbookOpen
'This event is fired whenever a workbook is open.
End Sub

Private Sub xlApp_NewWorkbook(ByVal Wb As
Microsoft.Office.Interop.Excel.Workbook) Handles xlApp.NewWorkbook
'This event is fired whenever a new workbook is created.
End Sub

Hopefuly the above will give You some guideline.

 

[Rp007]

Hi Dennis,

Thanks much for the valuable details.

The below resolution works fine with the requirement scenario what I have.
However there is no WithEvents in C#, this I need to take care by
implementing delegates.

thanks a lot,
-Ram.

 

[XL-Dennis]

Ram,

Glad we finally solved it and thanks for letting us know.

Delegates is yet another area that, at least I, need to get a better
understanding of.