Need Help on VBA code

[timark]

Hi,

I am a novice in access. I would like to

a.)import data from an excel worksheet which the file named is not fixed but the file directory is.(c:\import)

b.) After importing the file to a temp table named "Sheet 1". I want to append into the main Table " Table MAIN" and then I will want to delete all records in "Sheet 1" to prepare the table for the next importing process....

Thanks in advance...

 

[c_smithwick]

Sub ImportExcel()
    
    Dim strSpreadsheet As String
    Const strImportDir As String = "C:\Import\"
    
    strSpreadsheet = Dir(strImportDir & "*.xls") ' Will return the name of the first spreadsheet file in the directory
    DoCmd.TransferSpreadsheet acImport, 8, "Table MAIN", strImportDir & strSpreadsheet, True, ""
    'Transfers data and appends it to Table MAIN - no need to use your temporary Sheet 1 table
End Sub

 

[timark]

Hi,

Where must I input this code? I must create a button and go to the event procedure? If there are quite a number of files with almost the sam filename saved in that directory but I wanted import only the latest based on the date modified how should I input the codes?



Thanks

 

[c_smithwick]

Retrieving the newest file is not a problem, but will require that you reference the file scripting runtime and look at all the files in the directory to determine the newest. Since you asked the question, "where must I input this code?" I can assume that any explanation of how to reference and use file scripting will be met with a torrent of new questions. Before you tackle VBA code, perhaps you should pick up a reference and study up on the difference in class modules vs. standard modules, Functions vs. Subroutines and how to call them, Public vs. Private scope, etc ... Just diving into Access and trying to write VBA code can be very frustrating to a novice without a little study on just what is going on "under the hood" first.

 

[boblarson]

See my signature for some FREE VBA Tutorials (from FunctionX.com) which would probably be a good starting point.