PDA

View Full Version : Sleeper: Excel-Outlook-Access Link



rsilberfarb
05-19-2005, 04:43 PM
Not sure if this is the right forum, but here goes:

I already have code in place to do the following:

When an e-mail comes in from a particular sender, save it as text and automatically open an Excel file that imports the text and populates a form. Then the form is automatically sent to an e-mail address.

What I need to add:

I have an access database to keep track of the form info. Within the Excel file is a sheet that has info that I manually import into the database utilizing a "transfer spreadsheet" macro connected to a switchboard button. So currently I need to manually click the button to get the info into the database. I would like this step to happen in conjunction with what I already have automated

Any ideas are welcomed.

Thanks in Advance!!!!

Ross

xCav8r
05-19-2005, 08:58 PM
Check out the sample I provided in a somewhat mislabelled thread in the access forum. You can use that as a springboard to accomplish what you want.

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

rsilberfarb
05-20-2005, 12:40 AM
Alright, most of that just went right over my head. It seems like there would be an easier way to just link up the macro in excel to access and add some error handling.

Perhaps I need a bit more explaination.

Again, any help is greatly appreciated.

Ross

xCav8r
05-20-2005, 08:31 AM
Okay, try this:

http://support.microsoft.com/kb/q177760/



Sub AccessTest1()
Dim A as Object
Set A = CreateObject("Access.Application")
A.Visible = False
A.OpenCurrentDatabase("C:\My Documents\AccessAutomation.mdb")
A.DoCmd.RunMacro "AccessMacro"
End Sub

rsilberfarb
05-20-2005, 09:48 AM
xCav8r-

I have not tested this yet, but I know I am going to run into a problem. The data that is imported into Access contains a primary key (so only one instance of that record can exist). If for some reason the same record tried to be imported twice an error message will pop up. If this happens my response is always "No", is there a way to have an "on error" line in the code?

EDIT:

I executed the code for the same record twice, and no error, but no data was imported (which is the goal-I do not want it be able to go in twice). When I run the code, it leaves mt acess file open, and I would like it to be closed. I aded and application.quit at the end, but that did not do it.

Please help!


Thanks,

Ross

xCav8r
05-20-2005, 10:23 AM
Yes, it's possible. It depends on how you've set up your routine to add the data. You should be able to trap the err.number and skip the addition to the table. I'd need to see more before I could get more specific.