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)


        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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

By the way, the conclusion in the given link is

"I don't see any way to run a SQL query on unsaved data."
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Lee SavidgeCommented:
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.

reservoircapAuthor Commented:
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?
Given the conditions that you have(dont want to use temp file etc), I think you have to go with the looping. 1400x265 is not huge. Why would you populate the array or listview? Why not directly populate the datatable within a loop?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.