Read Excel using ADO in VB.NET 2005
Read Excel using ADO in VB.NET 2005
Read Excel using ADO in VB.NET 2005
Read Excel using ADO in VB.NET 2005
Read Excel using ADO in VB.NET 2005
Read Excel using ADO in VB.NET 2005 Read Excel using ADO in VB.NET 2005 Read Excel using ADO in VB.NET 2005 Read Excel using ADO in VB.NET 2005 Read Excel using ADO in VB.NET 2005 Read Excel using ADO in VB.NET 2005 Read Excel using ADO in VB.NET 2005 Read Excel using ADO in VB.NET 2005
Read Excel using ADO in VB.NET 2005 Read Excel using ADO in VB.NET 2005
Read Excel using ADO in VB.NET 2005
Go Back  Xtreme Visual Basic Talk > > > Read Excel using ADO in VB.NET 2005


Reply
 
Thread Tools Display Modes
  #1  
Old 04-27-2009, 12:30 PM
rajeevbhatt17 rajeevbhatt17 is offline
Freshman
 
Join Date: May 2008
Posts: 38
Default Read Excel using ADO in VB.NET 2005


Hi,

I am trying to read data off an excel file, using ADO and everything works fine ....EXCEPT:

I get an error opening some files, if they are not open otherwise. The error is - "External table is not in the expected format". But if the same file is already open, then I do not get any error, and everything works fine.

If I copy paste the contents of the same file into a new file, and save it under a new name, I still get the error.

Something strange I have noticed with this file is that if I open this file and try to close it, without making any changes, it still asks me if I want to save the changes.

This is a file I get from an external source, so I have no control over the contents/format etc.

The following code gives a part where I try to read all the sheet names in the file.

Imports System.Data.OleDb
Public Function GetExcelSheetNames(ByVal _FileName As String) As String()

GetExcelSheetNames = Nothing
Dim strNames As String()
Dim collSheetNames As New Collection

Try
' Connect to the Excel Spreadsheet
Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _FileName & ";" & _
"Extended Properties=""Excel 8.0;IMEX=1;HDR=YES"""

' create the excel connection object
Dim objXConn As New OleDbConnection(xConnStr)
objXConn.Open()

Dim dt As New DataTable
dt = objXConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "Table"})

For i As Integer = 0 To dt.Rows.Count - 1
If dt.Rows(i)("TABLE_NAME").ToString.Contains("$") Then
collSheetNames.Add(dt.Rows(i)("TABLE_NAME").ToString.Replace("$", ""))
End If
Next
ReDim strNames(collSheetNames.Count - 1)
For i As Integer = 0 To collSheetNames.Count - 1
strNames(i) = collSheetNames.Item(i + 1)
Next
objXConn.Close()
Return strNames

Catch ex As Exception
Stop
Finally

End Try
End Function
Attached Files
File Type: zip DAM Historical data - 2008.zip (103.9 KB, 14 views)
Reply With Quote
  #2  
Old 04-28-2009, 05:59 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi Rajeev,

I'm not an expert in ADO so I don't really know. More or less, the required format is that there be a row of headers across the top and a set of data below that in a block. As far as I understand it, it looks like your data meets that requirement.

So what is wrong must be more subtle. And why this subtlety is corrected by opening the workbook is yet another mystery.

I would try creating a new workbook with the same data and headers and try making changes to see which one(s) might fix it. Some things to consider:

(1) Some of your cells are blank. I would think that a blank cell should be seemlessly converted to zero (0) or empty string (""), but maybe not.

(2) Some of your headers have some characters that *might* be a problem. I don't think so, but maybe. Try avoiding parentheses, hyphens and commas and see if that helps. Try avoiding spaces, even, in the names.

(3) Some of your cells contain formulas and not just data. I don't think that that should matter, but it could be worth a try to convert all this to data.

If all this fails, then I would start with a much smaller, much simpler set of data and get that working. (And when I mean smaller, I mean a 4x4 block of cells and thats it.) Then once you have that working, build back up in stages -- when you add something that causes a problem, you'll then know.

Sorry I can't help with specific advice, but I'm not an expert enough in ADO to know what might be your trouble is here. So I hope that this *general* advice is of some help...

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 04-29-2009, 12:23 AM
rajeevbhatt17 rajeevbhatt17 is offline
Freshman
 
Join Date: May 2008
Posts: 38
Default

Thanks, Mike.

I tried all these things.....the only way i got the thing to work was when i took just a small subset of the data, about 10X10, and saved it i n a new file. Apparently, there is no difference between this part and the rest of the sheet.

And in the the example above, I am just trying to get the sheet names....the data comes in much later. I guess there is perhaps something in the rest of the file....some error, or some link, which corrupts it.

Why I am trying all this is because this application is running unattended on a server, and I cannot get Excel (and outlook) to open and close "properly"

Any further suggestions.....?

Is there a way of closing and Excel file by name? The idea is that i open this file via a shell command, without invoking excel from the code, and then once i have read teh data using ado, close the file via.....(?) a kill method?

Last edited by rajeevbhatt17; 04-29-2009 at 12:28 AM. Reason: additional info
Reply With Quote
  #4  
Old 04-29-2009, 02:40 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Hi,

If You drop the "IMEX=1" in the connection string will that make any change?

The "HDR=YES" expects to find column names in all worksheets. Is that correct?
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
  #5  
Old 04-29-2009, 04:37 AM
rajeevbhatt17 rajeevbhatt17 is offline
Freshman
 
Join Date: May 2008
Posts: 38
Default

Hi Dennis,

I tried with all possible permutations.....nothing seems to work when the file is closed.

Regards,
Rajeev
Reply With Quote
  #6  
Old 04-29-2009, 05:15 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Rajeev,

OK, let use if we can resolve it or not. I have seen this issue before but cannot recall if it possible to solve it or not.

Are there any obvious differences between the workbooks and the worksheets?

Added:
See if the solution at the following URL can be of any assistance:
http://www.excelkb.com/Article.aspx?id=10099
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP

Last edited by DennisW; 04-29-2009 at 05:20 AM. Reason: Spelling
Reply With Quote
  #7  
Old 04-29-2009, 06:41 AM
rajeevbhatt17 rajeevbhatt17 is offline
Freshman
 
Join Date: May 2008
Posts: 38
Default

Thanks, Dennis,

The code in teh linked page is pretty much the same as the one used by me.

As for teh differences between teh worksheets/books that do not work and those that do work, there is nothing discernible....no external links, formulae, nothing.

Regards,
Rajeev
Reply With Quote
  #8  
Old 04-29-2009, 08:23 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Rajeev,

If You open one of the files that generate the error and save it under a new name. Will that make any change?

BTW, which version of Excel are You using?
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
  #9  
Old 04-29-2009, 09:44 AM
rajeevbhatt17 rajeevbhatt17 is offline
Freshman
 
Join Date: May 2008
Posts: 38
Default

The file saved under a different name, or the sheet moved and saved into another (new) file....none of this works.

Only when I copy all the cells of the worksheet and paste them in a new workbook, as values, and try reading that, then it works.

Thanks
Rajeev
Reply With Quote
  #10  
Old 04-29-2009, 11:31 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Well, it's possible that you have some sort of corruption then... You should probably copy in the values into the new workbook and then use the new workbook from then on.

If this solves it, then it's not *really* an ADO issue, although, how your cells are formatted could potentially be having an effect here. Anyway, you should probably just "use what works" here, right?
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #11  
Old 04-30-2009, 04:22 AM
rajeevbhatt17 rajeevbhatt17 is offline
Freshman
 
Join Date: May 2008
Posts: 38
Default

Thanks, Mike...coming back to the question I asked earlier.....
"...Is there a way of closing and Excel file by name? The idea is that i open this file via a shell command, without invoking excel from the code, and then once i have read the data using ado, close the file via.....(?) a kill method?..."

Do you have any views on this?

Thanks,
Rajeev
Reply With Quote
  #12  
Old 04-30-2009, 07:01 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi Rajeev,

Yes this can be done. I'm not sure how you are creating your Excel instance, but of you are using Automation, then it could look something like this:

Code:
Dim xlApp As Excel.Application = CreateObject("Excel.Application")

' ... The rest of your code here ... 
' ... The rest of your code here ... 
' ... The rest of your code here ... 

' Cleanup:
GC.Collect()
GC.WaitForPendingFinalizers()
xlApp.Quit()
Marshal.FinalReleaseComObject(xlApp)
Using the System.Diagnostics.Process class is much more like using a shell command however:
Code:
Dim excelProcess As Process = Process.Start("Excel.exe")

' ... The rest of your code here ... 
' ... The rest of your code here ... 
' ... The rest of your code here ... 

' Cleanup:
excelProcess.Kill()
If you *really* want to use a shell command (and I'm not entirely sure what the difference is), then make use of the System.Diagnostics.ProcessStartInfo class, set the UseShellExecute property to true:

Code:
Dim psi As ProcessStartInfo = New ProcessStartInfo("Excel.exe")
psi.UseShellExecute = True
Dim excelProcessProcess  As Process = Process.Start(psi)

' ... The rest of your code here ... 
' ... The rest of your code here ... 
' ... The rest of your code here ... 

' Cleanup:
excelProcess.Kill()
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #13  
Old 05-04-2009, 01:32 AM
rajeevbhatt17 rajeevbhatt17 is offline
Freshman
 
Join Date: May 2008
Posts: 38
Default

Thanks, Mike.

What I actually want to do is - open the excel file via Process.start(abc.xls), which opens the excel file, read the data in it via ADO.NET, and then close the file by terminating the process which was called above....{process.start(abc.xls)}

is anything like this possible / recommended? As stated in earlier posts, I face a problem in reading excel via ADO, in this particular file, only when I have this file closed. In case it is open the code works fine.

Thanks in advance,
Rajeev
Reply With Quote
  #14  
Old 05-04-2009, 06:13 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

My final two examples in my previous post make use of Process.Start() and then Process.Kill() at the end. I'll repeat one of the examples here:

Note, requires "Imports System.Diagnostics.Process" at the top of your code module:
Code:
' Imports System.Diagnostics.Process

Dim excelProcess As Process = Process.Start("Excel.exe")

' ... The rest of your code here ... 
' ... The rest of your code here ... 
' ... The rest of your code here ... 

' Cleanup:
excelProcess.Kill()
Give it a try...

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #15  
Old 05-05-2009, 01:12 AM
rajeevbhatt17 rajeevbhatt17 is offline
Freshman
 
Join Date: May 2008
Posts: 38
Default

Thanks, Mike...yes this is fine, and it works.

I tried the following code. the problem is that in spite of all instructions, the Excel file is opened in visible state.
Dim pVB As New Process
pVB.StartInfo.FileName = "c:\MARKETPRICES.XLS"
pVB.StartInfo.CreateNoWindow = True
pVB.StartInfo.WindowStyle = ProcessWindowStyle.Hidden
pVB.StartInfo.UseShellExecute = True

pVB.Start()
'work here
pVB.Kill()

Am I missing something...in order to have the excel instance open in the background, without being visible?

Thanks,
Rajeev
Reply With Quote
  #16  
Old 05-05-2009, 05:28 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

It does seem to be ignoring that setting.

I guess you'll have to go with the Automation example I gave:
Code:
Dim xlApp As Excel.Application = CreateObject("Excel.Application")

' ... The rest of your code here ... 
' ... The rest of your code here ... 
' ... The rest of your code here ... 

' Cleanup:
GC.Collect()
GC.WaitForPendingFinalizers()
xlApp.Quit()
Marshal.FinalReleaseComObject(xlApp)
Automation code will start Excel in hidden mode by default. You can set the Visible property to False in addition, if you want, but it isn't necessary.

- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #17  
Old 05-05-2009, 07:31 AM
rajeevbhatt17 rajeevbhatt17 is offline
Freshman
 
Join Date: May 2008
Posts: 38
Default

Thanks, for your help Mike.
the reason I need to go the oledb way is that this application is to run in an unattended mode, on a server. This is not the best of things for Excel to handle. I keep getting the case when excel, in spite of all the cleanup code, does not close.

Would appreciate of you can think of something else.

Cheers/Rajeev
Reply With Quote
  #18  
Old 05-05-2009, 08:26 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi Rajeev,

I have no real solutions around this then. You are right, you should not be automating Excel from a server environment. In fact, the only reason you need to "automate" Excel at all in this case is due to a strange bug that does not allow ADO to access the Excel workbook correctly.

I think you need to fix the problem at the source. Either:

(1) Copy the data from the misbehaving workbook to a new workbook that does work. (It did appear that you had some initial success doing this earlier?) Copy pieces in one section at a time and test at each phase. If you do it this way, you can isolate which area is causing the problem. Is it the use of a formula instead of dead data? It is the formatting of the cell?

(2) Avoid Excel as a data source altogether. Copy the Excel data into a data table and use ADO.NET against that. You could start off with something small and light like SQL Server Express or MySQL.

This is about all I can think of at the moment...

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #19  
Old 05-07-2009, 03:40 AM
rajeevbhatt17 rajeevbhatt17 is offline
Freshman
 
Join Date: May 2008
Posts: 38
Default

Thanks, Mike...will try out.

Thanks to you Dennis as well.

Regards,
Rajeev
Reply With Quote
  #20  
Old 05-07-2009, 06:05 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Sure, no problem... Good luck!
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Read Excel using ADO in VB.NET 2005
Read Excel using ADO in VB.NET 2005
Read Excel using ADO in VB.NET 2005 Read Excel using ADO in VB.NET 2005
Read Excel using ADO in VB.NET 2005
Read Excel using ADO in VB.NET 2005
Read Excel using ADO in VB.NET 2005 Read Excel using ADO in VB.NET 2005 Read Excel using ADO in VB.NET 2005 Read Excel using ADO in VB.NET 2005 Read Excel using ADO in VB.NET 2005 Read Excel using ADO in VB.NET 2005 Read Excel using ADO in VB.NET 2005
Read Excel using ADO in VB.NET 2005
Read Excel using ADO in VB.NET 2005
 
Read Excel using ADO in VB.NET 2005
Read Excel using ADO in VB.NET 2005
 
-->