Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

reading open Excel file

Posted on 2004-04-29
6
Medium Priority
?
73,020 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 500 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
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!

 
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: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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

721 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