Solved

reading open Excel file

Posted on 2004-04-29
6
73,012 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Regex validation 2 28
write xml in vb.net 2 34
Help with opening combobox using VB.NET. 3 26
Crystal reports login after closing form 3 19
This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
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…

821 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