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. Replies:
    1
    Views:
    633
    Allen Browne
    Dec 7, 2005
  2. Guest
    Replies:
    4
    Views:
    351
    Guest
    Jun 26, 2007
  3. 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:
    1,008
    Sietske
    Jan 3, 2008
  4. 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:
    312
    ttaylor993
    May 2, 2008
  5. 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:
    306
    David H
    Jul 8, 2009
  6. BTU_needs_assistance_43

    Move Excel file to specified folder after importing to Access Tabl

    BTU_needs_assistance_43, Jul 20, 2009, in forum: Microsoft Access VBA Modules
    Replies:
    1
    Views:
    295
    Ken Snell [MVP]
    Jul 21, 2009
  7. 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:
    154
    BTU_needs_assistance_43
    Jul 23, 2009
  8. Erin

    Importing Excel file into Access Table.

    Erin, Sep 11, 2009, in forum: Microsoft Access VBA Modules
    Replies:
    1
    Views:
    251
    Ken Snell [MVP]
    Sep 11, 2009
Loading...