HELP!: Popualte Access Database with data from 50 existing workboo

[sam]

Hi All,

I have about 50 workbooks from which I need to populate the data into
Acccess database, Doing it manually would take weeks and it is laborous too.
Is it possible to write a Macro to do this job?

How can I populate the access database with Data in Sheet1 from all those
workbooks into access database?

Please Help

Thanks in Advance

 

[Patrick Molloy]

In the ACCESS IDE you can use code like this

Function GetData(sFilename, sRange)
On Error GoTo ErrorTrap
DoCmd.TransferSpreadsheet acImport, 8, "BondTable", sFilename, True,
sRange
Macro1_Exit:
Exit Function
ErrorTrap:
MsgBox Error$
Resume Macro1_Exit
End Function


as you can see, you pass the filepath/name and the range

build a table with two columns the first is the filepath, the second is the
name, then iterate through it,calling the function each iteration

 

[(PeteCresswell)]

Per sam:

How can I populate the access database with Data in Sheet1 from all those
workbooks into access database?

My first try would be per Patrick's suggestion.

But I'd be ready for inconsistencies in the layouts and data on
the sheets - as in dollar amounts in most cells of a "Market
Value" column, but occasionally something like "No data
available, call Chris."

Another potential problem is header rows - the bane of most
imports I've done, especially when they contain information that
is vital to understanding the rows under them.

If it were too bad, I'd write a generic routine to go through a
named sheet row-by-row, column-by-column writing whatever was
found to a series of String fields in a temporary table and then
proceed to cleaning up the data in the temporary table.

If there were a lot of header rows, I'd circle back and modify
that generic routine to look for this and that value....