Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    268

    Unanswered: Import Excel to RS

    Does anyone know how I would go about trying to import a range of data from excel to a recordset?

    The data needs some cleaning so it is easier to just apply the changes to the data in the RS rather than run a series of update queries.

    Any help is greatly appreciated.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Excel ADO Recordset

    Hi

    Sorry it has taken so long to reply but it took a bit of time to get round to looking at it, mainly because I have pondered on it my self as a possible requirement. I normally write VBA code in Excel and copy data into the database using ADO connection to a Database. You have more flexibility with spreadsheet manipulation that way (ie. you can have the data arranged how you like and read it into a recordset – see later).

    I digress, I have posted below code written in Access to connect to an Excel ‘Database’ using ADO and a DSN-less connection string that returns an ADO recordset.

    The Table object specified in the SQL statement can either be a sheet name followed by a $ sign OR a predefined Range Name in the workbook (without a $ sign), OR a sheet name with range address (examples in code).

    These can be included in an SQL string OR allocated to a string variable and concatenated into the SQL if you want to loop through a number of sheets.

    Note, As it stands the first row of the Worksheet or Range will be used for the recordset field names, so this will need setting up (or at least allowed for).

    The code below connects to a Sheet (or Range) and cycles through the field names and then through the records.

    Code:
    Sub ADOExcelRecordset_Referenced()
        Dim cn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim ConString As String
        Dim SQL As String
        Dim TableName As String
        Dim j As Integer
        
        Set cn = New ADODB.Connection
        Set rs = New ADODB.Recordset
            
        ConString = "DRIVER=Microsoft Excel Driver (*.xls);" & _
                    "DBQ=C:\My Documents\Phone Book.xls;" & _
                    "DefaultDir=C:\My Documents;" ' & _
    
            
        cn.Open ConString
        
        TableName = "BMS - TREND$" ' THIS IS A WORKSHEET NAME (WITH $)
        'TableName = "BMS" ' THIS IS A RANGE NAME (WITHOUT $)
        'TableName = "BMS - TREND$A1:C6" ' THIS IS A WORKSHEET NAME (WITH $) WITH RANGE REFERENCE
        
        SQL = "SELECT * FROM [" & TableName & "]" ' CONCATENATED TABLE NAME
        'SQL = "SELECT * FROM [BMS - TREND$]" ' TABLE NAME IN STRING
        
        rs.Open SQL, cn
    
        j = 0
        For j = 0 To rs.Fields.Count - 1
            MsgBox rs(j).Name
        Next j
        
        j = 0
        Do Until rs.EOF
            For j = 0 To rs.Fields.Count - 1
                MsgBox Nz(rs(j), "Null")
            Next j
            rs.MoveNext
        Loop
        
        rs.Close
        cn.Close
        Set rs = Nothing
        Set cn = Nothing
    End Sub
    To use this code you will need to set a reference to the ‘Microsoft ActiveX Data Object Library’ rather than using Set cn = CreateObject("ADODB.Connection") etc, for the connection and recordset as some seem to do. This has the advantage of early binding and providing Auto List Members and Auto Quick Info while writing the code.


    You set a reference to the ADO object library in Tools -> References when in a code module.

    Hope this is what you require, but I still prefer to transfer data using code in the Excel sheet.

    Any questions let me know.


    MTB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •