Importing Excel Spreadsheet to Access Database

[icthus123]

I've created a database in access with which people can enter their hours worked, etc.

However, a number of people have complained that it was easier to enter hours with the old system when they simply filled in an excel timesheet spreadsheet and handed it in.

Now, really we need to access database because it makes it an awful lot easier to get data about hours out of it. As a result of this I'm considering the idea of having the hours entered on an excel spreadsheet and then imported into the database after they'd entered them in excel. This would get the best of both worlds.

Does anyone have any tips on the best way to go about this?

[matthewspatrick]

Honestly, I would force them to use Access :whip

[stanl]

Honestly, I would force them to use Access :whip

I agree, set up a little switchboard with an update form. If OTOH, you must use Excel do they

1. enter weekly stuff by name into a common workbook

2. have individual worksheets

as this will affect any tips you might receive

[icthus123]

They enter they're hours into individual worksheets every month as it stands at the moment!

I personally agree that it's better just to do it in access, but I've agreed with some people that I'll look into the idea of using excel still and importing the data into access, so if anyone's got any ideas or tips then I'd be glad to hear them!

[debauch]

Hi There.
I have easy code that dumps excel data into a specified database. You can invoke it when the workbook closes if u wanted.

My code is at work, but search for DAO connections using VBA. It's pretty easy to work with.

Not sure I'd want multiple people doing this though. The user-end errors would be common.

[debauch]

I can post it tomorrow.

[icthus123]

Thanks debauch, it'd be great if you could post it tomorrow!

[stanl]

Hi There.
I have easy code that dumps excel data into a specified database. You can invoke it when the workbook closes if u wanted.


I see some concerns (which have nothing to do with the code not working)

1. You have to place the code in each individual workbook, and any modifications would have to be updated to each workbook; not a problem if there are only 2 employees.

2. If you will be (and probably should be) using a common Access DB/table then you have mult-user issues [locked .ldb files].

If it were me I would have all workbooks placed in a common network folder, each workbook passworded per user, but passwords known to you - workbooks named by either employee name, id number????, viz bSmith.xls

Then you create a single lookup table in Access, and write your import code as an Access Module. You iterate each entry in the lookup and process/or not the individual workbooks, writing out a status report for each week to catch errors or ommissions.

... trust me, present that scenario to management and the other employees and they will let you do it all in Access in no time:devil2: Stan

[asingh]

Hi,

I created a similiar logic..where people..."wanted" entry on excel but wanted "processing" in Access.

1. 25 Excell Sheets [all with same structures] are dumped in a folder residing next to the Access data base.
2. These 25 names [of the files] are then fed into a table in the access data base.
3. I create a link specification in the access data base, which holds the structure of the source files.
4. I run a VBA module, which invokes a Text Transfer Method via VBA and appends the 25 sheets data into an Access table. The Text transfer method reads the link specification which is also stored in the table which contains the names of the files which are to be uploaded.

What "4" does is: Reads a table, where the file name is present, reads the respective link specification for that file, and text transfers it into Access.

CONS:
1.25 names have to be hard-coded onto the lookup access table.
2.If your Excel structures change, you will need to save another link specification.
3. Each of the 25 files, will have to have a unique name.

The code runs off the Access database, so it is centralized, the Excel files hold no code.

Let me know, if this would suit you..can provide the code.

regards,
asingh

[debauch]

Hey, sorry for the delayed response. I can't remember where I found this but it works wonders.


' exports data from the active worksheet to a table in an Access database
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("I:\Shared\reports\controls\accessDB.mdb") '97 DB
' open the database
Set rs = db.OpenRecordset("TABLE_NAME, dbOpenTable)
' get all records in a table
r = 1 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
.Fields("Int") = Range("A" & r).Value
.Fields("TIME") = Range("B" & r).Value
.Fields("I) = Range("C" & r).Value
.Fields("employee") = Range("D" & r).Value
.Fields("RING") = Range("E" & r).Value

' add more fields if necessary...


.Update ' stores the new record

End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing


You could have one workbook with x number of sheets for each person so you only need to update/change one workbook.

To use the code, you could have a button for each user calling the procedure, or maybe have it upload the data on close . Hope this helps.

[debauch]

I suppose a 'link table' would work to. Just have the workbook stored on your local drive, and link to it in access so you can do what you need with it?

[Ken Puls]

Another option for you as well...

I use an add-in daily that uses templates to ensure that the forms are still set up the way I want them. I pull information back from the database to Excel using a variant of this routine (http://www.excelguru.ca/node/23).

The worksheet holds all the information to validate the data before upload, which is then done by a variant of this routine (http://www.excelguru.ca/node/18). What I really like about the routine to push the data back to Access is that it uses transaction processing. So if there is an error in the upload, it doesn't leave part of a transaction posted.

Overall, I would tend to agree with the others that keeping it all in Access by designing efficent forms is probably preferable. If you can't do that, though, and there is Excel functionality that the users (badly) want or need, then this might be a route to consider.

HTH,