Excel Link with access

[bryVA]

Hello,

I am trying to create a access database that will accept input from an excel spreadsheet VBA code. I have 25 agents that need tracking. They put it in a userform in Excel that transfers it to an Access table. What I need to figure out is how can I get them to alter the information in the table. When the tracked item is closed they need to go in and solve that tracked item out. The problem is they don't have access and I can't figure out how to get access to alter the information in the database using Excel and ADO. Is there any other method or procedure for doing this?

Thanks,

-B

[OBP]

I would work in Access and import the Excel worksheet with the changes in to a Temporary Table in your Access database and then run an Append query to add any new records and an Update Query to update the current records. Then delete the Temporary Table (or delete the table before importing the next worksheet)
This can be completely automated, so all you need to do is click a Button, or Browse to the desired Excel workbook and then click a button. I have a demo Access Database if needed.

[bryVA]

That demo would be great. Thanks so much for that idea.

-B

[OBP]

The attached database has a form to Import any Excel worksheet by browsing to it.
If the worksheet has the same name and is in the same place all the time it can be built in to the VBA instaed of browsing for it.
The VBA code is extensive, including Modules. You may have to set VBA Library references, especially to Excel.
The code currently imports form a1 to co500.

If it does what you want we can discuss the Queries that you will need.

[bryVA]

Thank you for being so willing to help.

I am not sure if I am missing something but I don't see an attachment.

That sounds like exactly what I need.

Thank you,

-B

[OBP]

Sorry, the Forum blocked it because it is already on here on this Thread.

http://www.vbaexpress.com/forum/showthread.php?t=28387

[bryVA]

Great. Thank you. I had an issue with this working properly. I click on the get source and import data button and it kicks it out at:

Dim AppExcel As New Excel.Application

It states: "User-Defined Type not defined."

Do I have to reference something. I am using Access 2000 so is this causing the problem.

Thanks for all your help,

-B

[geekgirlau]

Haven't checked out the code, but you will need a reference to "Microsoft Excel xx.x Object Library" (I forget which version number it is for Office 2000, but there will probably only be 1 to choose from anyway).

[OBP]

As I explained in post #4 :yes

To open and set the references goto the VBA Editor (alt + f11) and on the main menu Tools>References and you will probably see Excel marked as missing. Untick it and the use the drop down to go down to Microsoft Excel and tick your version and then click OK. You should also make sure that Microsoft ActiveX Data Objects is also ticked.