Pulling information from Excel

[Ian Mac]

All,

What is the quickest easiest way to Append information from 45 Excel Worksheets into an Access table.
The data types and layout are identical. The lists will be of varying length.
This would need to be coded as they sit on 4 different servers.

Just a future insite, The data will be updated consantly, I either need the data to be deleted at source once retrieved (Very messy I imagine) or only pull unique values, i.e. not existing data.

Any starters most welcome.

Cheers,

 

[SydneyGeek]

Ian,
Here's an attempt at breaking up the problem.

1. The files live on 4 different servers. Are they grouped so that all files from Server 1 are in the same folder? If so, you can define 4 path constants in the Access code to go to those folders, and loop through the resident files.

2. I think you can get around the unique record problem by setting a flag field in the Excel files. eg: Select the range. Do the import. Then put 1 in a new field at the right of the data set. Next import, set the filter to ignore records with a flag.

OK, that's the logic. I can't have a go until I get to machine with Access on board, but it might get the process started...

Denis

 

[mdmilner]

I like Sydney's #2 idea - flag fields inside Excel. If you'd like a brute force method though, Kill the spreadsheet after import and replace it with a blank template copied from some safe location.

DoCmd.TransferSpreadsheet (the normal command to import a spreadsheet) works very well, until the users get creative modifying the spreadsheets. Renaming columns, cell data types, or worse, adding new columns tends to make things crash.

You should probably open the spreadsheets with VBA and walk thru them.

Mike

 

[Ian Mac]

Hi,

The clincher on this one, sorry if I wasn't clear, I'm trying to sort out so many solutions at the same time.
And this will make a BIG difference.

The Workbooks are shared, each server has a Workbook which has an input sheet where the user input data and hit a 'Submit' button.
The data then goes to their own Sheets dependant on the users NT Username.
This solves the Data type issue as the user CANNOT gain access to the records.
Also solves the issue of messing with the information.

Each record is time/date and Username stamped, and then continues along the fields with the other information. Then saved.
BTW I would like to use Access for this but.....you all know the score there

I need to pull the information Realtime (if possible) from the various sheets into a table.
I can't see how I can add a flag to the data as it's a shared Workbook in which all of the Sheets are xlSheetsVeryHidden, If I can that would be great.
I also like the idea of clearing the data, but this would mess with the submission of the data Realtime. BUT! the idea would be good for out of hours, so could be used as well as flagging if it can be done.

So, the plot has thickened. Hope that's nice and chewy.

Cheers,