Treating WorkSheet As MS Access Table?

[PeteCresswell]

Within a VBA module in the same .XLS, is there any way to open up one
of the worksheets as an MS Access table?

My thinking is that then I could use of SQL against the worksheet
directly - without writing all the code to create a temporary MS
Access DB and write the worksheet's contents to a temp table within
said DB.

 

[Patrick Molloy]

sure - you can treat a sheet/named range as if were a database...
here's and example where every column is read from a table range named
Instruments (its on Sheet1)

in the development environment you need to set a Reference to Microsoft
Active Data Objects 2.6 Library dll (or 2.7) (menu: Tools/References)




Sub LoadFromExcelDatabase()

Dim Conn As ADODB.Connection
Dim RST As ADODB.Recordset
Dim strConn As String
Dim SQL As String
Dim ws As Worksheet, wb As Workbook, cl As Long

Dim sExcelSourceFile As String

sExcelSourceFile = "C:\Temp\XL_Database.xls"

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel
8.0;"
strConn = strConn & "Data Source="
strConn = strConn & sExcelSourceFile

Set Conn = New ADODB.Connection
Conn.Open strConn

Set RST = New ADODB.Recordset
SQL = "SELECT * FROM [Instruments]"

RST.Open SQL, Conn, adOpenStatic

If Not RST.EOF Then

Set wb = Workbooks.Add(xlWBATWorksheet)
Set ws = wb.ActiveSheet

For cl = 1 To RST.Fields.Count
ws.Cells(1, cl).Value = RST.Fields(cl - 1).Name
Next
ws.Range("A2").CopyFromRecordset RST

Set ws = Nothing
Set wb = Nothing


End If



RST.Close

Conn.Close

Set RST = Nothing
Set Conn = Nothing

End Sub

 

[PeteCresswell]

sure - you can treat a sheet/named range as if were a database...
here's and example where every column is read from a table range named
Instruments (its on Sheet1)

in the development environment you need to set a Reference to Microsoft
Active Data Objects 2.6 Library   dll (or 2.7)   (menu: Tools/References)

That's exactly, *precisely* what I was hoping for.

Thanks.