Importing excel to access as the results of a file search

Discussion in 'Microsoft Access VBA Modules' started by scottyboy, Mar 31, 2008.

  1. scottyboy

    scottyboy Guest


    i'm struggling with the below code, what i would like to do is to search for
    the workbook "dailyfile310308.xls" and import it into access.

    my problem is the filename changes and the folder changes.

    so far i have..

    Sub FindMaxFileandImport()
    Dim iSearch As Variant
    Dim iCount As Long
    Dim max_file As String

    max_file = "dailyfile" & Format(Date, "ddmmyy") & ".xls"
    Set iSearch = Application.FileSearch

    With iSearch
    .LookIn = "Mypath"
    .SearchSubFolders = True
    .Filename = max_file
    If .Execute > 0 Then
    For iCount = 1 To .FoundFiles.Count
    DoCmd.TransferSpreadsheet acImport, 9, "Max_csv_table",
    ..FoundFiles(i), True

    Next iCount
    MsgBox "The file does not exist."
    End If
    End With
    End Sub


    scottyboy, Mar 31, 2008
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.

Share This Page