Link to home
Start Free TrialLog in
Avatar of reservoircap
reservoircap

asked on

Can I export data from an unsaved excel spreadsheet (using ADO.Net/VB.Net)?

I have an application that uses Excel as a number cruncher. I need for Excel to spit out all its calculated output into a format that I can easily "slap" into a SQL Database. I have been able to do this successfully if I save the Excel file, however the file is so enormous that this is simply not an option due to performance concerns. I also do not want to copy and paste data into a temporary Excel file and read from there.

Is there a way to connect to an already open Excel spreadsheet and read data from it without having to save the file? I've pasted the code that I've been using - this is the code that successfully reads from an already saved Excel file. Thanks EE community!
Dim connXL As New OleDbConnection
        Dim sXLConnectionString As String = "Provider=" & g_sXLConnectionString_Provider & ";Data Source=" & sFilePathString & ";Extended Properties=""" & g_sXLConnectionString_ExtendedProperties & ";HDR=" & sHDR & """"
        'Create Connection to Excel Workbook
        connXL = New OleDbConnection(sXLConnectionString)

        'Excel Workbook "database" command
        Dim commandXL As New OleDbCommand("Select '" & iRunID & "', '" & iFileID & "', '" & CDate(sMonthDate) & "', * FROM [" & sRange & "]", connXL)

        connXL.Open()

        Dim drXL As DbDataReader
        drXL = commandXL.ExecuteReader
        If xlConnAndData.drXL Is Nothing Or Err.Description <> "" Then
            g_sErrorMessage = "Error occured in GrabExcelData function while creating the datareader object for Excel. MSError: " & Err.Description
            g_bErroredOut = True
            Return xlConnAndData
            Exit Function
        End If

Open in new window

Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

This page explains how to select data out using ADODB on an unsaved open Excel spreadsheet.

http://stackoverflow.com/questions/2031207/is-there-a-way-to-use-ole-db-provider-for-jet-on-an-unsaved-excel-workbook

Lee
By the way, the conclusion in the given link is

"I don't see any way to run a SQL query on unsaved data."
Hmm, correct. I have done this before, a while ago with Excel 2000 so hopefully you can still do it in Excel 2007, as you need to find the location of the temp file that is created. I thought this page was the one that told me. Seems not. I'll dig around to see if I can find the page.

Lee
Avatar of reservoircap
reservoircap

ASKER

To CodeCruiser:

The problem with interop is that whether I suck in the Excel values using a giant array or a List (of arrays of values), at some point I will need to loop through the elements to place into a dataset or datatable (or list items to insert directly into the SQL database) . I'm trying to look for a solution where I would not have to use a loop since this process will need to be repeated each time the application sucks in data.. and in this partiuclar group of workboks, each worksheet is about 1400 rows long and 265 columns wide. It would take awhile for VB.Net to loop through each element or row.

Maybe there's a way for me to directly slap an entire array or listview created using Excel Interop into a SQL database that I'm unaware of?
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial