Import Data from Excel into Access

[sauce1979]

I am tryng to create an access application that browses a folder for excel files with a certian naming convention and imports the first worksheet into an access tables. I am a sql server developer but have very little experience programming in Access. I would imagine this needs to be built with VBA.



Each valid excel sheet represents a buisness unit. The data in each excel spreadsheet is of the same format. There are 12 Business units altogether thus 12 files should be in the driectroy at a given time.

As the files are imported there should be some data validation to ensure that the data is correct. If the data is incorrect it will not be loaded into the database.

Files that are processed should be moved to a different folder. Failed files should remain in the original directory.
I would like to have a way of processing all files at once or an individual file. Therefore I would need some brwosing functionality.

Finally I would like to have some log info which will show what was processed and what was not processed. Maybe some sort of import report.

Any information or ideas would be very much appreciated.

[rpeare]

There are a bunch of things you're asking for here.

First, if your files are all in the same folder and always will be it's pretty simple to set up a program to browse the files that follow a certain naming convention.

look at filesystemobject commands to do this. Here's a reference:

http://www.4guysfromrolla.com/webtech/090599-1.shtml

Next you want a method to open an excel file and read specific cells:

http://www.justkez.com/vbscript-and-excel/

There are other ways to get specific cells like this

Dim xlApp 
Dim xlBook 
Dim xlSheet 
Set xlApp = CreateObject("Excel.Application") 
xlApp.visible = true 
Set xlBook = xlApp.Workbooks.open("FilePathandName") 
Set xlSheet = xlBook.Worksheets("SheetName") 
MsgBox  xlSheet.Cells(1, 1).Value

Once you retrieve the values and do your error checking you can use the filesystemobject to also move the file to a new location or leave it where it is.

FileSystemObject.CopyFile "filepathandfolder", "newfolder"

As you read each file names using the filesystemobject you can add the file to a log table and, if it passes your validation, update the processed flag, leaving it blank if it fails.

[sauce1979]

thanks for the info. will give it a go and let you know how it goes