Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    42

    Unanswered: Access code to query Excel

    Hi! I am interested in querying an Excel workbook from Access. Currently the spreadsheet that I am querying is in all text format(including the numbers) and I want to query the spreadsheet from Access and append the data to a table with the correct data types. Is this possible? Know any where I can take a look at some sample code that does this?

    Thanks!

  2. #2
    Join Date
    Jul 2003
    Posts
    48

    Re: Access code to query Excel

    Here is some code to read an excel spreadsheet, change the cell range, path, file name and sheet name to match yours. I have printed the results to the immediate window so change this to write to your table.

    Sub ImportDataExcel()
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim strPath As String
    Dim strWSht As String
    Dim strWBk As String
    Dim n As Long

    Set rst = New ADODB.Recordset

    strPath = "c:\programming\access\book1.xls"
    strWSht = "[Sheet1$A1:C8]"
    strWBk = "'" & strPath & "'"
    strWBk = strWBk & " ""Excel 8.0;HDR=Yes;"""

    strSQL = "SELECT * FROM " & strWSht & " IN " & strWBk

    rst.Open strSQL, CurrentProject.Connection

    Do While rst.EOF = False
    For n = 0 To rst.Fields.Count - 1
    Debug.Print rst.Fields(n).Value & ",";
    Next n
    Debug.Print
    rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
    End Sub

  3. #3
    Join Date
    Aug 2003
    Posts
    42
    I am having trouble getting Access to recognize the ADO. I added the Microsoft ADO Ext. 2.5 for DLL and security reference(the only one I could find). Any thoughts? Is that the correct refernce?

    Thanks for the help!

    Could I use DAO? What would be the difference?

  4. #4
    Join Date
    Jul 2003
    Posts
    48
    I did this code in Access 2000. Add Microsoft ActiveX Data Object 2.1 Library to the references. To add references, from code module select tools->references.

  5. #5
    Join Date
    Aug 2003
    Posts
    42
    I found it. I am however having another issue with the code. In this statement:

    rst.Open strSQL, CurrentProject.Connection

    what is "CurrentProject"? I cannot figure that out.

    Thanks for the help so far!

    Regards

  6. #6
    Join Date
    Jul 2003
    Posts
    48
    It contains the connection string for the current access database. Although we are bringin back data from excel the syntax expects a connection string, so the CurrentProject.Connection is simply the connection settings of the database

Posting Permissions

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