Solved

reading open Excel file

Posted on 2004-04-29
6
73,015 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB.NET Inline If statement 4 56
Need help with a query 14 54
Variable 'id' is used before it has been assigned a value 29 45
I need to clear List Box in MVVM panel. 15 27
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

739 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