Results 1 to 7 of 7

Thread: DAO recorset with Excel 2007

  1. #1
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0

    DAO recorset with Excel 2007



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Greetings. I am currently using a DAO recordset in Excel 2007 to import a table from another Excel workbook. The only way I can get it to work is by saving the source workbook in 2003 format, and using the below code.

    Dim DB As DAO.database
    Dim RS As DAO.Recordset

    FilePath = "\\ojrfscdata1\ojcsfs01\share\Billing Services Planning and Analysis\Greg\"
    Obj = "2011 Spec Limits Template.xls"

    Set DB = OpenDatabase(FilePath & Obj, False, True, "Excel 8.0;HDR=Yes")
    Set RS = DB.OpenRecordset("SELECT * FROM [VA$]")

    This works like a charm, as long as I have "Excel 8.0" in the extended properties argument. However, I would like to be able to do this using Excel 2007 as the source, since my company has transitioned all pc's to Office 2007. From what I can gather, I would need to use "Excel 12.0". This, predictably, does not work. Does it have anything to do with the ACE OLEDB provider? Is there a way I could change my OpenDatabase statement to accomodate an Excel 2007 file a the data source? Thank you.

    Greg

  2. #2
    Show us all of the code, including the connection.

  3. #3
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0
    Quote Originally Posted by Bob Phillips View Post
    Show us all of the code, including the connection.
    Bob, here is the whole sub. Also, I'm new to this forum, and I'm not sure if there is a way to post code as a code block. Thanks.

    Sub GetSpecs()
    Dim WS As Worksheet
    Dim TopRow, i As Integer
    Dim DB As DAO.database
    Dim RS As DAO.Recordset
    Dim FilePath As String
    Dim Obj As String
    Dim NoRecords As Boolean
    Dim RSCount As Integer
    Dim MonthYR As String

    Set WS = ActiveWorkbook.ActiveSheet

    MonthYR = GetMonthFromSheet()

    FilePath = "\\ojrfscdata1\ojcsfs01\share\Billing Services Planning and Analysis\Greg\"
    Obj = "2011 Spec Limits Template.xls"
    'Get data from 2011 Spec Limtits Template.xls
    Set DB = OpenDatabase(FilePath & Obj, False, True, "Excel 8.0;HDR=Yes")
    Set RS = DB.OpenRecordset("SELECT * FROM [VA$]")

    'Populate spec limits table
    TopRow = WS.Range("C20000").End(xlUp).Row + 2
    NoRecords = False
    With RS
    If Not (.BOF And .EOF) Then
    NoRecords = False
    .MoveFirst
    While Not .EOF
    .MoveNext
    RSCount = RSCount + 1
    Wend
    Else
    NoRecords = True
    MsgBox ("No records in query " & Obj)
    Exit Sub
    End If
    .MoveFirst
    While Not .EOF
    For i = TopRow To TopRow + RSCount - 1 Step 1
    WS.Cells(i, 3) = .Fields(0)
    WS.Cells(i, 4) = .Fields(1)
    .MoveNext
    Next i
    Wend
    End With
    'Close the recordset
    Set RS = Nothing
    DB.Close
    Set DB = Nothing

    With ActiveWorkbook
    RangeName = "='" & ActiveSheet.Name & "'!R" & TopRow & "C3:R" & WS.Cells(20000, 3).End(xlUp).Row & "C4"
    .Names.Add Name:="specs_" & MonthYR, RefersToR1C1:=RangeName
    End With
    End Sub

  4. #4
    Quote Originally Posted by gsnidow View Post
    Also, I'm new to this forum, and I'm not sure if there is a way to post code as a code block.
    Precede it with <open square bracket>code<close square bracket> and end with <open square bracket>/code<close square bracket>.

  5. #5
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0
    Quote Originally Posted by Bob Phillips View Post
    Precede it with <open square bracket>code<close square bracket> and end with <open square bracket>/code<close square bracket>.
    Thanks, Bob. Maybe this will be better...
    Code:
    Sub GetSpecs()
        Dim WS As Worksheet
        Dim TopRow, i As Integer
        Dim DB As DAO.database
        Dim RS As DAO.Recordset
        Dim FilePath As String
        Dim Obj As String
        Dim NoRecords As Boolean
        Dim RSCount As Integer
        Dim MonthYR As String
        
        Set WS = ActiveWorkbook.ActiveSheet
        
        MonthYR = GetMonthFromSheet()
        
        FilePath = "\\ojrfscdata1\ojcsfs01\share\Billing Services Planning and Analysis\Greg\"
        Obj = "2011 Spec Limits Template.xls"
        'Get data from 2011 Spec Limtits Template.xls
        Set DB = OpenDatabase(FilePath & Obj, False, True, "Excel 8.0;HDR=Yes")
        Set RS = DB.OpenRecordset("SELECT * FROM [VA$]")
        
        'Populate spec limits table
        TopRow = WS.Range("C20000").End(xlUp).Row + 2
        NoRecords = False
        With RS
            If Not (.BOF And .EOF) Then
                NoRecords = False
                .MoveFirst
                While Not .EOF
                    .MoveNext
                    RSCount = RSCount + 1
                Wend
            Else
                NoRecords = True
                MsgBox ("No records in query " & Obj)
                Exit Sub
            End If
            .MoveFirst
            While Not .EOF
                For i = TopRow To TopRow + RSCount - 1 Step 1
                    WS.Cells(i, 3) = .Fields(0)
                    WS.Cells(i, 4) = .Fields(1)
                    .MoveNext
                Next i
            Wend
        End With
        'Close the recordset
        Set RS = Nothing
        DB.Close
        Set DB = Nothing
        
        With ActiveWorkbook
            RangeName = "='" & ActiveSheet.Name & "'!R" & TopRow & "C3:R" & WS.Cells(20000, 3).End(xlUp).Row & "C4"
            .Names.Add Name:="specs_" & MonthYR, RefersToR1C1:=RangeName
        End With
    End Sub

  6. #6
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0
    If anyone is still interested in this one, I thought I would give ADO a whirl, and, lo and behold, it worked. I'm not saying DAO won't work, I just got tired of trying to figure out the syntax. It is quite possible that if I could have made it work, it might have been better somehow, but I'm going with what works for me. Anyhow, I created directory C:\Test, and put a file 'Data.xlsx' in there with just a few rows of data for testing. It's basically the same as the DAO option, with some minor tweaking for ADO. Admittedly, I'm not well schooled in the differences between the two, I'm more of a trial and error, ad adsurdum, kind of guy. I found some help at http://support.microsoft.com/kb/257819. Below is the code. Thanks, Bob, for taking time to consider my problem.

    Code:
    Sub RSTest_2007_ADO()
    '   This sub will pull data from an external .xlsx file.  The ADO object
    '   library reference must be loaded.
        Dim cn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim strSQL As String
        Dim WS As Worksheet
        Dim RSCount As Integer
        Dim FieldCount As Integer
        Dim i, j As Integer
        Dim NoRecords As Boolean
        Dim FilePath As String
        Dim FileName As String
            
        Set WS = ActiveWorkbook.ActiveSheet
        Set cn = New ADODB.Connection
        
        FilePath = "C:\Test\"
        FileName = "Data.xlsx"
        strSQL = "SELECT * FROM [Sheet1$]"
        
        With cn
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .ConnectionString = "Data Source=" & FilePath & FileName & ";" & _
                "Extended Properties=Excel 12.0;"
            .Open
        End With
        
        Set rs = cn.Execute(strSQL)
        
        With WS
            TopRow = .Range("A20000").End(xlUp).Row
            NoRecords = False
            With rs
                FieldCount = .Fields.Count
                If Not (.BOF And .EOF) Then
                    NoRecords = False
                    .MoveFirst
                    While Not .EOF
                        .MoveNext
                        RSCount = RSCount + 1
                    Wend
                Else
                    NoRecords = True
                    MsgBox ("No records in target")
                    Exit Sub
                End If
                .MoveFirst
                While Not .EOF
                    For i = TopRow To TopRow + RSCount - 1 Step 1
                        For j = 1 To FieldCount Step 1
                            WS.Cells(i, j) = .Fields(j - 1)
                        Next j
                        .MoveNext
                    Next i
                Wend
            End With
        End With

  7. #7
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    170
    Articles
    0
    FYI, DAO will work, as long as you set a reference to the 'Microsoft Office 12 (or 14 if using 2010) Access Database Engine Objects' library and then use the Excel 12.0 property as you tried originally. Having said that, although DAO is usually simpler to work with for Access data sources, I personally would generally prefer ADO anyway (as long as the source workbook isn't open).

Tags for this Thread

Posting Permissions

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