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.
Similar Threads
  1. Guest
    Replies:
    4
    Views:
    302
    Guest
    Jun 26, 2007
  2. Sietske

    Search memo fields by entering multiple search terms in one search

    Sietske, Jan 3, 2008, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    657
    Sietske
    Jan 3, 2008
  3. ttaylor993

    Showing combo box results from the results of another combo box

    ttaylor993, May 1, 2008, in forum: Microsoft Access VBA Modules
    Replies:
    4
    Views:
    177
    ttaylor993
    May 2, 2008
  4. BTU_needs_assistance_43

    Narrow Search Results in Access

    BTU_needs_assistance_43, Jul 8, 2009, in forum: Microsoft Access VBA Modules
    Replies:
    3
    Views:
    286
    David H
    Jul 8, 2009
  5. BTU_needs_assistance_43

    Search for a specific value in an Excel table to start importing c

    BTU_needs_assistance_43, Jul 23, 2009, in forum: Microsoft Access VBA Modules
    Replies:
    0
    Views:
    128
    BTU_needs_assistance_43
    Jul 23, 2009
Loading...

Share This Page