Using Excel to write data to Access?? 1

[Beeps]

Hi,

I'm working with a client that has been using Excel to track employee time on Excel and wants to go to Access, but keep the Excel page for the employees to enter their time on. Any suggestions?

Thanks!

 

[ShortTexan]

In Access, while viewing the database window, click File/Get External Data/Link. Change file type to MS Excel, Point to the Excel Workbook, Specify the desired worksheet and answer the questions in the Wizard. You can then create forms and queries based on the link. Hope this helps.

 

[Beeps]

Thanks for your help! You rule!

 

[GeekGirlau]

If the users are going to continue to use Excel, you may want to automate this process.

To import the spreadsheet data into Access, create a form with a command button and attach the following code to the On Click event of the button:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, _
"TableName", "Filename", True

You can either insert this data straight into the Access table if it will work smoothly, or enter a new table name here. If you use the latter method, you will need a few queries:

1. A delete query to delete all entries in the temporary table;
2. Use the import spreadsheet as above;
3. An append query to add all new records to your table (records exist in temporary table but not in database table);
4. A delete query to remove any deleted records (records exist in database table but not temporary table);
5. An update query to change any details (records exist in both tables).

To run the queries in code, use the following command:

docmd.OpenQuery "QueryName"

 

[GBall]

Surely, these solutions require someone to do something at the access end for each user each week/month ?
Wouldn't it be better to place VBA in the spreadsheet to automatically post the data to Access ?
Or am I way off here ?

Graham

 

[Beeps]

That might be a better idea. Is there a way to code the spreadsheet so that data from the sheet is written to the table?

The sheet now holds employee ID, hours worked, different projects worked on, different systems worked on and different dates. I imagine the best way to organize it on the table would be to have each record go by date, system worked, task on system and hours worked.

The problem is that these fields on the spreadsheet do not fall into a format that resembles a table. Any ideas on how to code the sheet so that it transmits the date to Access in a logical table format?

Thanks!

 

[GingerR]

i suggest that you created a data-entry form in Access and gently persuade the Person In Charge to use that instead. I do this all the time...getting data from excel can get to be quite a problem. wait until someone puts "NA" in what you need to be a 'date' field or puts in an invalid job number, etc....you end up scrubbing the excel data before being able to import it. you can handle all this validation, etc, right in Access. my suggestion is to create something cool on the side, then show them how cool it is, how much easier it will be for them to use, let them get all warm and fuzzy about it over a few weeks, then convert over. it's the best thing for everyone.

g

 

[GBall]

Ginger is quite right, of course, but this should get you going in the meantime. I suggest putting a button on the spreadsheet, which is linked to a macro as follows.

Sub Store_data()
Dim db As database
Dim rs As Recordset

Set db = OpenDatabase("myfile.mdb&quot
Set rs = db.OpenRecordset("Select * from timetables&quot
lngRowIndex = 0
With rs
.edit
.Fields("Hours&quot.Value = Worksheets("Cumseries&quot.Range("A1&quot.Value
.update
.Close
End With

Set rs = Nothing
db.Close
Set db = Nothing

end sub

hope this helps.

 

[Beeps]

Thanks for all your help everybody, works out pretty well!