Access and Excel Datatransfer

[pcdaveh]

Situation: I have a workbook that can have as few as 5 worksheets or as many as 80 worksheets. Each worksheet has a range of 25 - 90 cells that have data that must be appended / copy pasted into an access data base. I'm not particular about the method except that it is done programatically. Also, I've just described the situation for one workbook. I have at least 50 workbooks that this must be done with. This task has be repeated twice each month. I'm not asking anyone to give me all the code but to give me a strategy.

 

[Nordyck]

There are lots of ways that this can be done. The approach that I would use. Create a table that lists the names of the different workbooks. Then as the code goes through the the different workbook within the list, you can extract the data from the worksheet either by linking or transfering the data to a temp table. This will give you access to the data. The next step is to process the information. Since each worksheet is different, I would use a case statement to select which procedure that would be used.

st = “Select workbook.names from workbooktable”
Set rs = currentdb.openrecordset(st)
While not eof rs!workbook.names
While not eof worksheets.count
docmd.TransferSpreadsheet acImport,,"temp", workbook.names
or
link the worksheet
processdata(workbook.names)
wend
wend

sub processdata(name as string)
…
end sub