Using DAO to search Excel database

[Roderick]

I'm now working in Windows 7 and Word 2013. This is an absolutely clean machine set up and used only for this kind of work.

I've written an Initialize procedure in a userform to go and search an Excel database file. The end result when I try to run it it says that it cannot find the .xls file.

It works fine until this line and then tells me that it is an invalid argument.

Set db = OpenDatabase(inifileloc2, False, False, "Excel 8.0")

This is the code for the procedure I'm referring to:

Private Sub UserForm_Initialize()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim iNoOfRecords As Integer
    
    'Won't let the default button become active if the "Default is selected as the default.
    If cboOfficeLocation.Value = "My Default" Then
        cmdDefaultOffice.Enabled = False
        cmdOK.Enabled = True
    End If

    VarPathLocal = Options.DefaultFilePath(wdUserTemplatesPath)

    FullPath = VarPathLocal + "\" + "My Office Details.xls"

        inifileloc2 = FullPath

    ' Open the database
    cmdDefaultOffice.Enabled = False

    Set db = OpenDatabase(inifileloc2, False, False, "Excel 8.0")

    ' Retrieve the recordset
    Set rs = db.OpenRecordset("SELECT * FROM `MyOfficeRange`")

    ' Determine the number of retrieved records
    With rs
        .MoveLast
        iNoOfRecords = .RecordCount
        .MoveFirst
    End With

...and so on through the initialisation procedure.

The References set are as follows:

Visual Basic for Applications
Microsoft Word 16.0 Object Library
OLE Automation
Microsoft Office 16 Object Library
Microsoft Forms 2.0 Object Library
Microsoft DAO 3.6 Object Library

For the life of me I cannot see where the incorrect logic is being entered!

Can anyone suggest a method that might work, please?

[gmaxey]

Roderick

DAO is some pretty old stuff and there are several reports on Google that the Jet database engines have been adversely affected by an October update.

Try ADODB. Here is an example of code I use to populate a listbox with data from and excel file in the same folder.

Sub LoadFromExcel_ADODB(ByRef oListPassed As Object, ByRef strSource As String, Optional strColWidths = "100")
'Requires reference to the "Microsoft ActiveX Data Object 2.8 Library."
Dim oConn As New ADODB.Connection
Dim oRecSet As New ADODB.Recordset
Dim strConnection As String
Dim strRange As String
Dim lngCount As Long
  p_strPath = ThisDocument.Path & Application.PathSeparator
  strRange = "mySSRange"
  'If the source range is a worksheet use:
  'strRange = strRange & "$]"
  'Else
  strRange = strRange & "]"
  
  Set oConn = CreateObject("ADODB.Connection")
  'Suppress first row.
  strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & p_strPath & "sourceSpreadSheet.xls;" & _
                  "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
  'No suppression.
  'strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                   "Data Source=" & p_strPath & "sourceSpreadSheet.xlsx;" & _
                   "Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
  oConn.Open ConnectionString:=strConnection
  
  Set oRecSet = CreateObject("ADODB.Recordset")
  'Read the data from the worksheet.
  oRecSet.Open "SELECT * FROM [" & strRange, oConn, 2, 1
  With oRecSet
    .MoveLast
    'Get count.
    lngCount = .RecordCount
    .MoveFirst
  End With
  With oListPassed
    'Load the records into the columns of the named list/combo box.
    .ColumnCount = oRecSet.Fields.Count
    .Column = oRecSet.GetRows(lngCount)
  End With
  'Cleanup
  If oRecSet.State = 1 Then oRecSet.Close
  Set oRecSet = Nothing
  If oConn.State = 1 Then oConn.Close
  Set oConn = Nothing
lbl_Exit:
  Exit Sub
End Sub