asked on

Merge Excel Spreadsheet with Access database

I'm helping a co-worker redesign their timesheet. I have an excel spreadsheet set up to use as an employee timesheet for 100+ employees. There are 4 cells that I would need to change for employee name, employee id, department, and pay period. I also have an access database with information that I imported from excel. How do I merge the excel timesheet with the access database so I could print each of the 100+ employee's timesheets with out having to manually enter their 4 cells of information?

Many thanks in advance.

Anne Troy

If I'm getting you right, the problem is that you've got many records for employee 1, employee 2, and so on. But you don't know how to get Access to collect the other 4 pieces, correct?

Where do these 4 pieces exist in the time sheets, if they exist?

I suspect you're going to need some Access VBA code to accomplish this, and you may want to go to community support at:
https://www.experts-exchange.com/Community_Support/

and ask them to delete your question here, then repost your question here:
https://www.experts-exchange.com/Databases/MS_Access/

Good luck!

jagermeister3

ASKER

You're close. And I'm not sure if I'll need to repost the question but let me explain it and you can tell me if I need to repost.

I have a file designed in excel with multiple columns and rows so employees can manuall fill it out in pen or pencil. I have 4 cells that I would like to replace, "Employee Name" "Employee ID" "Department" and "Pay Period".

I'd like be able to merge or change out the cells with the table in Access. Say "Joe Smith" "04532" "Sales" "04/20/03-04/27/03" there are 100+ employees with this information.

Once this is merged accounting could print out the timesheets after running a query? or merging the cells?

Thanks

Anne Troy

OH!
I see. I don't know how I could explain that in writing or even explain it without seeing your DB and Excel file. :(

jagermeister3

ASKER

would it help if I coud send you my excel file that has the time sheet on it?

gakibbie

If you want to go from Excel to access you can save the Excel file as a csv (Comma Seperated Value) file and import it directly into access as a seperate table. From there you can run an append or update query in Access to update your existing database.

If you are going the other direction you can export a table from Access as a csv but then you would be stuck manually updating Excel unless you do some tricky VBA code with search and replace.

Let me know which way you want to go and I will give you the particulars and any code necessary.

Cheers,
Aaron

ASKER CERTIFIED SOLUTION

Anne Troy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

drubarth

I must not be understanding all your needs, because as I do understand the problem the answer seems quite simple.

If all your data is in Access, why don't you simply create an Access report to print the individual timesheets.

If some of your data is in Excel, you can link to your Excel worksheet directly (you don't need to save it in a CSV format, but your headings do have to be setup on the first row with no spaces) and then create a query to pull your data together. Then again just use an Access report for printing your timesheets.

Good luck!

dr