Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Treating WorkSheet As MS Access Table?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Treating WorkSheet As MS Access Table?
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Treating WorkSheet As MS Access Table?
On Jul 2, 9:24*am, "Patrick Molloy"
wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table treating numbers as text in value field | Excel Worksheet Functions | |||
excel worksheet export into access table | Excel Worksheet Functions | |||
Exporting excel worksheet to access table | Excel Programming | |||
Exporting excel worksheet to access table | Excel Programming | |||
Exporting excel worksheet to access table | Excel Programming |