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

    Hi

    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
    Else
    MsgBox "The file does not exist."
    End If
    End With
    End Sub

    help!
    --
    Cheers

    Scotty
     
    scottyboy, Mar 31, 2008
    #1
    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