Solved

reading open Excel file

Posted on 2004-04-29
6
73,007 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now