ADO connection with Excel

[LittleDaisy13]

Hi,

For a web project that I am working on I need to be able to read in an excel file using a DNS-less connection. Right now I am able to get this to work but only when the Excel file is closed. I need to be able to read the file even when someone has it open and is editing it. I know that this is possible when you use an access file and I was wondering if anyone knew a way to do it with an excel file. This is the error that I get when I try to run the page with the excel file open.

[Microsoft][ODBC Excel Driver] Can't open database '(unknown)'. It may not be a database that your application recognizes, or the file may be corrupt.

Thanks

[Nitro]

I am not sure what you are looking for but this might help. It connects to an Access database from Excel module.

Option Explicit
'Make sure you reference the Microsoft ActiveX Data Object
Public cn As ADODB.Connection
Public rs As ADODB.Recordset

Sub p_Fill_Listbox(strFormCaption As String, objMultiSelect, strSQL As String, strField As String)
  'PURPOSE: Uses ADO to retrieve an SQL Server recordset
  Set cn = CreateObject("ADODB.Connection")
  cn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
                   "DBQ=" & gstr_Database_Name & ";" & _
                   "DefaultDir=" & gstr_Database_Location & ";" & _
                   "UID=admin;PWD=;"
  cn.Open
  
'_______________________________________________________________
  frmAccessDB.Caption = strFormCaption
    
  'PURPOSE: Refresh the listbox
  frmAccessDB.lstAccessDatabase.Clear
  frmAccessDB.lstAccessDatabase.MultiSelect = objMultiSelect
  
  Set rs = New Recordset
  With rs
    'PURPOSE: Query to get Ops Review Code
    .Open strSQL, cn, adOpenStatic, adLockOptimistic
  
'____ Method 1 - Fastest ___________________________________________
    Dim int_Rec_Count As Integer
    Dim int_Counter As Integer
    .MoveLast
    int_Rec_Count = .RecordCount
    .MoveFirst

    For int_Counter = 1 To int_Rec_Count
      If Trim(.Fields(strField)) <> "" Then
        frmAccessDB.lstAccessDatabase.AddItem Trim(.Fields(strField))
      End If
      .MoveNext
    Next
    
''____ Method 2 - _________________________________________________
'    Do Until .EOF
'      'PURPOSE: Populate the listbox with the
'      '         new result from the recordset.
'      '         Cannot be null!
'      If Trim(.Fields(strField)) <> "" Then
'        frmAccessDB.lstAccessDatabase.AddItem Trim(.Fields(strField))
'      End If
'
'    'PURPOSE: Move to the next record
'      .MoveNext
'    Loop
''__________________________________________________________________
    
    
    'PURPOSE: Total number of record
    frmAccessDB.lblRecordCount.Caption = "Total Records: " & .RecordCount
    
    'PURPOSE: Close the recordset
    .Close
  End With
End Sub