How to copy or transfer data from several excell sheets sinaccess tabl

[gglgokop1704]

Dear All,

Please how can I transfer or copy data from several excel sheets to a single access table programatically using VB. The data in all the sheets have the same fields.

Kind regards
Gokop

 

[lameid]

Use docmd.transferspreadsheet with the same target table for each sheet.

 

[gglgokop1704]

Than you very much Lameid. But that means one sheet after the other? I have several excel work books (100 + Cost Centres) and each workbook has several worksheets (General Ledgers) . I want the vb code to look for each worksheet, transfer the data to a table and then move to the next sheet, append the data under the previous sheet's data, until the worksheets are exhausted in a workbook.All sheets in a Workbook contain the same Columns and data types.

Sorry for this lengthy explanation.

Kind regards for your usual help
Gokop

 

[lameid]

If you mean a bunch of separtate files you could use the dir function to get at the file names....

I think transferspreadsheet can take a sheet name but I'm not sure... If it does, you could use Excel automation to loop through all the sheets in a file to find out their names.

I know this is vague but I don't have the time at the moment to be specific... I hope it helps... you can likely find Excel automation examples here to get you going.

 

[gglgokop1704]

Thanks again Lameid. If Trnasfersheet can take a worksheet name, then may be in the range argument. The range arguement as I have seen it used takes only the cells range. I have succeeded in copying the first active worksheet of a workbook to my access table. the problem is how can it loop to the next worksheet and so on. I am not too bothered with opening many workbooks (excell files), I am more concerned with copying my worksheets in a single workbook to a single access table.

Kind regards and thanks for your time.
Gokop

 

[gglgokop1704]

Please Lameid do you have any idea on how to consolidate data from several worksheets to a single worksheet. With this I can used the Transferspreadsheet method to get the whole data into a single access table

Kind regards

 

[SkipVought]

Hi,

Check out my reply (corrected) in thread707-1398909.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[lameid]

If you wanted to run Skip's code from Access, you would have to add a reference from excel and do something like the following to open the file and use Excel...

Dim App As Excel.Application
Set App = CreateObject("Excel.Application")
App.Workbooks.Open FileName:=strFile

That is just something to get you started... you should be able to figure out from the methods and properities available on App how to meld the two.

 

[gglgokop1704]

Thanks Lameid. This code I quess is to run on access side. I am just trying it. the App does not seem to popup methods/properties when a . is appended. It does not seem to recognise it.
I guess. Is this not enough to reference Excel?

Thanks

 

[lameid]

In the module select references from the tools menu and find the one for Excel (probably starts with Microsoft Excel) and check it. Then you should see the methods and properties. And remember that is just a starting place to get Skip's code to work from Access.

 

[gglgokop1704]

Hi Lameid,

Thanks very much.I can now see the methods. I am working on the codes now.

Regards