Auto-linking in Access is it possible?

[navyguy59]

I need to know if I make an Access database that utilizes the Get External Data function to draw data from multiple Excel documents, can I do something to auto-update the database to accept new excel documents saved into the target folder and incorporate them into the database or do I have to enter each new document manually? The excel documents I plan to link into the database are monthly reports with a new excel document added to the folder every month. Is there anything I can do to accomplish this?

[JConsulting]

I need to know if I make an Access database that utilizes the Get External Data function to draw data from multiple Excel documents, can I do something to auto-update the database to accept new excel documents saved into the target folder and incorporate them into the database or do I have to enter each new document manually? The excel documents I plan to link into the database are monthly reports with a new excel document added to the folder every month. Is there anything I can do to accomplish this?

Is the layout of the spreadsheets always the same? Same columns and datatypes?

Do you wish to put everything into the same table each time?

What do you wish to do once a spreadsheet has been uploaded? Will you move it or rename it so it doesn't get imported twice?

will there be duplicate data from spreadsheet to spreadsheet?

[navyguy59]

Is the layout of the spreadsheets always the same? Yes it the same layout just with new figures added to reflect fule amounts and time flying used for the month.
Same columns and datatypes? Yes

Do you wish to put everything into the same table each time? I want to be able to track 12 months worth of data on fuel used and time in flight basically. I will manipulate this data in various ways to be sure but on that note I am waiting for the Maint Officer to clarify what all he needs done with the information. The data will comprise of 12 excel documents one generated each month and placed into a shared drive where we can access them. They will come out with a new one each month.
What do you wish to do once a spreadsheet has been uploaded? The Officer I am working on this project for has not yet decided what all he needs this database to be able to do with the data. Until he can clarify that part for me I am just trying to get an outline of what I can do.
Will you move it or rename it so it doesn't get imported twice? The database or the excel files? Database should stay in the network folder I create it in, I do not forsee the files name changing. The excel documents will be named by month and year...Example: Jan07.xls
Will there be duplicate data from spreadsheet to spreadsheet?

Duplicate data will be rare as the fuel quantities from month to month and flight times are always changing as we fly at the needs of the Navy not at a set schedule.

[JConsulting]

Duplicate data will be rare as the fuel quantities from month to month and flight times are always changing as we fly at the needs of the Navy not at a set schedule.

Take a look through this function. It uses a table that stores the path to the spreadsheets and loops through the directory importing the files one after another. It may be a bit much for what you're after, considering you'll have one per month, but the method is still valid.

Public Function LoopThroughPath()
Dim strPath As String
Dim strFile As String
Dim myFullFile As String
Dim ImportTable As String
Dim strDest As String
ImportTable = "MyImportTable"
'Clear old records out of the Import Table
CurrentDb.Execute "Delete * from " & ImportTable & ";"
strPath = "C:\Test\"   ' Set the path for the Source Directory.
strDest = "C:\Test\Imported\" ' Set the path for the Save Directory.
strFile = Dir(strPath, vbDirectory) 'Don't change this part
Do While strFile <> ""
    If Right(strFile, 4) = ".xls" Then ' Criteria, ie File Extension
        myFullFile = strPath & strFile
        'Import data to the Import table
        DoCmd.TransferSpreadsheet acImport, 8, strTable, myFullFile, True, ""
        'Now I want to move the selected excel file to the "done" directory
        FileCopy myFullFile, strDest & strFile
        Kill myFullFile  'Delete the spreadsheet from the Source Directory
    End If
    strFile = Dir()
Loop
End Function
 

J

[Wayne L]

Can this code be used to import .dbf files also? I am trying to import only the new .dbf files that are generated. The file all start the same but increment in numbers.