Solved

reading open Excel file

Posted on 2004-04-29
6
73,009 Views
Last Modified: 2012-06-21
Is there anyway to read an OPEN excel file into a datatable (readonly is ok)






0
Comment
Question by:RonaldBiemans
6 Comments
 
LVL 5

Expert Comment

by:tgannetts
ID: 10950570
Use ADO.Net:

Dim cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.xls;Extended Properties=""Excel 8.0;HDR=YES;""")
Dim da As New OleDb.OleDbDataAdapter("Select * from [Sheet1$]", cnn)
Dim ds As New DataSet("TestExcel")

da.Fill(ds)

Tom
0
 
LVL 25

Author Comment

by:RonaldBiemans
ID: 10956926
Tgannetts, I know how to open a closed excel file the problem reading an OPEN excel file.
The problem is the excel file I need to read is opened by an external program and is constantly updated by that program.
0
 
LVL 5

Accepted Solution

by:
tgannetts earned 125 total points
ID: 10957569
How about creating a reference to the already open application using the GetObject function.

Something along the lines of:

        Dim objapp As Object
        Dim objwb As Object
        Dim objws As Object

        Try
            'Get open application
            objapp = GetObject(, "Excel.Application")

            For Each objwb In objapp.Workbooks

                If objwb.Name = "test.xls" Then

                    For Each objws In objwb.Worksheets

                        'Read values into datatable for this worksheet
                        ....

                    Next

                End If

            Next

            objapp = Nothing

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

I don't think this causes a conflict with the open worksheet as you are creating a reference to the application which has opened the worksheet and made the changes.

Hope this is of some help

Tom
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 25

Author Comment

by:RonaldBiemans
ID: 10957788
Tgannetts, I though about that solution myself , but it is too slow.

I found another solution though, I just made a macro in the excel file that saves itself to another file every 10 minutes.

Thanks for your time though, I will award you the points.
0
 

Expert Comment

by:himadrish
ID: 11712701
0
 

Expert Comment

by:Smariga
ID: 12211463
This is a bit off topic, but related to this code.  
I put the code in exactly as listed in the first note (changed for my file names).  When I run the code, I get an "Unhandled Exception" error when running the Fill command.  And it does not appear that I have valid access to the excel data, from looking at the watchlog.  I'm new to VB net, and do not know how to determine what is not working right.  

These are the error logs:
'DefaultDomain': Loaded 'e:\windows\microsoft.net\framework\v1.1.4322\mscorlib.dll', No symbols loaded.
'My Excel Automation': Loaded 'E:\Documents and Settings\D865\My Documents\Visual Studio Projects\My Excel Automation\bin\My Excel Automation.exe', Symbols loaded.
'My Excel Automation.exe': Loaded 'e:\windows\assembly\gac\system.windows.forms\1.0.5000.0__b77a5c561934e089\system.windows.forms.dll', No symbols loaded.
'My Excel Automation.exe': Loaded 'e:\windows\assembly\gac\system\1.0.5000.0__b77a5c561934e089\system.dll', No symbols loaded.
'My Excel Automation.exe': Loaded 'e:\windows\assembly\gac\system.drawing\1.0.5000.0__b03f5f7f11d50a3a\system.drawing.dll', No symbols loaded.
'My Excel Automation.exe': Loaded 'e:\windows\assembly\gac\microsoft.visualbasic\7.0.5000.0__b03f5f7f11d50a3a\microsoft.visualbasic.dll', No symbols loaded.
'My Excel Automation.exe': Loaded 'e:\windows\assembly\gac\system.data\1.0.5000.0__b77a5c561934e089\system.data.dll', No symbols loaded.
'My Excel Automation.exe': Loaded 'e:\windows\assembly\gac\system.xml\1.0.5000.0__b77a5c561934e089\system.xml.dll', No symbols loaded.
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll


I expect that I have not defined something, but am at a ooss where to look.  

Any suggestion where to look or read would be appreciated.

ALex

0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question