Reading Data using OLEDB from opened Excel File

Question

I have an excel file(Lets' say File X) with 2 sheets. In first sheet I display charts. Second I have data for the chart. In order to get data from chart, I need to process that data as we do in SQL like Group by, order by. Is there any way I can use oledb to read data from second sheet using VBA code in same excel file(file X)?

Thanks!!

Did you try googling "OLEDB Excel"? Lots of examples will be found... — Tim Williams
– Tim Williams, Commented Mar 25, 2013 at 16:55
Hi Tim, I am already using oledb right now to fetch data but the file i am reading is not the file I have VBA code in. So it works. But I want to read from already opened file and VBA code is also in the same opened file. — NetDeveloper
– NetDeveloper, Commented Mar 25, 2013 at 17:57

Tim Williams · Accepted Answer · 2013-03-25 18:10:23Z

3

Here's an example of using SQL to join data from two ranges: it will work fine if the file is open (as long as it has been saved, because you need a file path).

Sub SqlJoin()

    Dim oConn As New ADODB.Connection
    Dim oRS As New ADODB.Recordset
    Dim sPath
    Dim sSQL As String

    sSQL = "select a.blah from <t1> a, <t2> b where a.blah = b.blah"

    sSQL = Replace(sSQL, "<t1>", Rangename(Sheet1.Range("A1:A5")))
    sSQL = Replace(sSQL, "<t2>", Rangename(Sheet1.Range("C1:C3")))

    If ActiveWorkbook.Path <> "" Then
      sPath = ActiveWorkbook.FullName
    Else
      MsgBox "Workbook being queried must be saved first..."
      Exit Sub
    End If

    oConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & sPath & "';" & _
                 "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"

    oRS.Open sSQL, oConn

    If Not oRS.EOF Then
        Sheet1.Range("E1").CopyFromRecordset oRS
    Else
        MsgBox "No records found"
    End If

    oRS.Close
    oConn.Close

End Sub

Function Rangename(r As Range) As String
    Rangename = "[" & r.Parent.Name & "$" & _
                r.Address(False, False) & "]"
End Function

answered Mar 25, 2013 at 18:10

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NetDeveloper Over a year ago

Hey Tim, I have one more issue where If file is open from SharePoint, it says invalid internet address for ActiveWorkBook.FullName. Do you have any idea how to get rid of that?

NetDeveloper Over a year ago

Hey Tim, Sharepoint is just an example, think of any excel file on internet

Tim Williams Over a year ago

You could always save a local temp copy and query that instead.

Collectives™ on Stack Overflow

Reading Data using OLEDB from opened Excel File

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related