Excel to Access

T

trouble

Hi,
I am a new user. I have an excel timesheet that everyone
uses to enter their time. My boss wants to be able to
have everyone enter their time on the excel timesheet file
and be able to have it go into access. If it is only one
line, I can do it, but it could be 10-20 lines. I can
copy, past append, but I think there should be an easier
way to do this. Any little help will be appreciated.
Thank you
 
J

John Nurick

Hi Trouble,

It would be simpler on the whole (bosses apart) to have everyone enter
their times directly into Access; if you're using Excel it's extremely
difficult to ensure that the data is "clean".

The best way of importing data from Excel to Access depends on how it is
laid out in Excel.

If it's a block of clean data laid out as a table, you can usually use
File|Get External Data|Import or DoCmd.TransferSpreadsheet. (This
normally imports all the data on a worksheet, but you can also use
Insert|Name|Define in Excel to name the range you want to import and
then import it by name.)

Otherwise - e.g. if the Excel sheet is laid out like a form with the
employee name and ID in one place and the dates and hours in a table
below - you need to use Automation to have VBA code running in Access
get the values directly from the individual cells in the worksheet. Here
are some links that help with gettign started:

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/?id=123859

ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(Q210148) http://support.microsoft.com/?id=210148

ACC: Using Automation to Create and Manipulate an Excel Workbook
(Q142476)
http://support.microsoft.com/?id=142476

ACC2000: How to Use Automation to Fill a List Box (Q210145)
http://support.microsoft.com/?id=210145
This article shows you how to use Automation to populate a list box
with values from a Microsoft Excel worksheet
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top