File Import Macro

[giacomo]

Can someone help me write a macro that will import the most recently modified file from a specific directory? I wish to import an Excel file into Access. It's a daily report and the file name is always changing, there's no naming logic to it, so the only way I can think of identifying it programmatically is by modified date.

I'm using Access 97 & Excel 97.

Thanks,
Giacomo

 

[Parra]

In the Macro Tab Click on New
On the Action Field choose "Transfer Spreadsheet"
Then just fill in the criteria in the Action Arguements

The only thing is that the file name and location always has to be the same. I'm sure there can be more advance ways of determining it by date like you said, but I don't know how.

 

[guybrush]

FileSearch Method

The following function will help you grab a path to the last modified excel file, in a particular folder, and write it to a field titled PathName in a table titled tbl_FilePath

Public Function GetFileNames() As String
    Dim varItem As Variant
    Dim rst As ADODB.Recordset 

    Set rst = New ADODB.Recordset
    rst.Open "tbl_FilePath", CurrentProject.Connection, _
            adOpenDynamic, adLockOptimistic, adCmdTableDirect
    
    With Application.FileSearch
        .NewSearch
        .fileName = "*.xls"
        .SearchSubFolders = True
        .LastModified = msoLastModifiedAnyTime
        .LookIn = "C:\My Documents\Excel"
        .Execute
            For Each varItem In .FoundFiles
                rst.AddNew
                rst.Fields("PathName") = varItem
                rst.Update
            Next varItem
        End With
    
End Function

You could then import the file(s) using the docmd.TransferSpreadsheet command.

(Set the parameters in the function to meet your needs, i.e. the .Lookin = "Your Path")

Good Luck

Acually, I just noticed you are using 97. I know this works in 2000, but I definentally don't know about 97. (sorry)

 

[Parra]

Guybrush, So the code you wrote will look at a particular folder, get the most recent file and copy it to another folder. Then you would run the macro in Access?

This is what I understand. Great code.

 

[guybrush]

Yes, Partially

It will look in the folder of your choice (in this example, the folder it looks in is "c:\My Documents\Excel", then it copies the path (C:\My Documents\Excel) into a table.

Here is the same code, modified a bit to import the most recent excel file into your database. (Once again, this works in 2000, I am not sure about 97).

Public Function ImportExcelSpreadsheet()
    Dim strPath As String
    
    With Application.FileSearch
        .NewSearch
        .fileName = "*.xls"
        .SearchSubFolders = True
        .LastModified = msoLastModifiedAnyTime
        .LookIn = "C:\My Documents\Excel"
        .Execute

        'Once the above code has run, a list of files is available
        'in the Application.FileSearch.FoundFiles Collection.
        'You can grab the first file it found, and place it in a string...

        strPath = .FoundFiles(1)
    End With
    
    'Then, call on the docmd.TransferSpreadsheet command, and pass it the string...

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table Name Here", strPath
    
End Function

Place this code on the "onClick" event from a button on a form. Then when the user presses the button, the file will import. (Call the ImportExcelSpreadsheet() function on the onclick event).

Be advised, you will need to "prepare" the excel spreadsheet for importing, by placing column headings, and making sure the first few records in the spreadsheet are filled out. (Access makes data type decisions based on the first few records in an import file).

This may be overkill for what you are trying for, but I know it is what I would do.

Good luck :D

 

[giacomo]

guybrush,

Thank you for the code! I had a little trouble with the the last one... I can't remember what the error was exactly but I made some modifications and ended up with something similar to your last post.

I really apprecite your help.

Giacomo