Automate import from excel or csv into Access

[Anguscat]

Does anyone know of an easy way to import other than the manual way of file-get external data-import.

I would like to be able to set something up that has a file location saved and when a new file is put in that directory I can just click a button and pull it into Access.

Thanks!

 

[Anguscat]

Can I modify the code to have each table imported maintain the original file name?

 

[Anguscat]

I'm not sure what happened, but for some reason. The code is not importing from the designated path. It is looking in my C: drive??

 

[SydneyGeek]

This is the code that sets the search folder; did you change anything here?

    Const TOP_FOLDER = "H:\Test" 'adjust folder name to suit 
    Const ARCHIVE_FOLDER = "H:\Test\Imported" 'adjust folder name to suit

Denis

 

[Anguscat]

I did, I changed it to a network path (maybe I can;t do that):

Const TOP_FOLDER = "\\Midp-sfs-004\analysis\2007data"

I think I found my error. When it gets to the code

DoCmd.TransferSpreadsheet acImport

I didn't specify the path, it must be defaulting to my C: drive

 

[SydneyGeek]

I haven't tried using UNC naming for defining the import folder; maybe you need to use your mapped drive path instead.

Denis

 

[Anguscat]

okay, I have everything working great and I notice that my code is appending the data instead of overlaying the data, I can run a delete query initially to fix this, but might the be a simple fix. Am I using the wrong command here:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, filenm, .FoundFiles(i), True


Thanks,
Bill

 

[SydneyGeek]

Hi Bill, add this before the import line:

On Error Resume Next
DoCmd.RunSQL "DELETE * FROM " & filenm
On Error GoTo 0

I'm pretty sure that TransferSpreadsheet will append to existing tables, so you will need to delete the existing records first.

Denis

 

[Anguscat]

That did it. Thanks again!!!