Importing Data From Spreadsheets - Tips 1

[Grumm]

Hi,

I have an Access application that tracks projects for a construction firm. One of the main table’s tracks the % Complete for each project task and the time spent on each task. The access application sits in head office and the site foremen (who are responsible for collating the figures) do not have remote access to the application.

I want to find the best way of getting the foremen to get the information to head office (developing a web from end is not a possibility at the moment).

What is the best approach to this problem - I am new to linked tables etc.

My first thoughts would be to export the project details to a spreadsheet and email to the foremen to keep up to date for the week. At the end of the week the spreadsheet would be emailed back to head office. This I am happy with but I do not want head office to have to retype the figures.

What is the most efficient way of getting the data from the sheet into the application. I have looked at import data, linked sheets. I need to update the data not replace in the application. Could I use SQL to update the data.

Sorry if this is a bit of a woolly question. Any starters for 10 would be a great help.

 

[LittleSmudge]

Sounds reasonable and easy enough to implement

Process:
The central database can automatically email a specially designed table in Excel format as an attachment each week

The foremen then fill it in

Then email it back

Someone then places a copy of the attachment into a predefined folder

The database can then import one spreadsheet at a time into a tempory table
Interogate the data
Update the central database
Empty the temp table
Delete the excel spreadsheet from the folder
Repeat with next spreadsheet until all are done
Hybernate until next week!


I know becuase I have applications that do something very similar. ( & I thought I was the only one having to jump through such crazy hoops )

'ope-that-'elps



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[Grumm]

Hi,

Thanks for that - sounds just what I am looking to do.

How do I go about creating the the excel table and automatically sending it? Is it a make 'excel'. Sorry to sound daft but its the first time I have used this type of function.

Any pointers are apreciated.

 

[LittleSmudge]

Not daft at all.

If you've not come across it before it would be a struggle to find. ( That why Tek-Tips is so popular )

Use

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblTableName", "\\Server\Path\FileName.xls"[code]

That exports the Access table directly into a Excel readable spreadsheet in the predefined folder

"\\Server\Path\FileName.xls" can be in the 
"C:\Path\FileName.xls" format if you want.



'ope- that-'elps.


 G LS
accessaceNOJUNK@valleyalley.co.uk 
Remove the NOJUNK to use.

 

[Grumm]

Thanks for that - very useful

Grum