[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 805
  • Last Modified:

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

  • 3
  • 2
1 Solution
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."
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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?

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now