How to automate the data impor process from Excel to Access

[msand]

I have an Excel template for my clients to fill in some data and then email it back to me. For each client's info, I want to add it to the current database. The excel template looks like this:

Product Type Quantity Unit Price Discount% Final Price
xx 2 each 5 20 8
yy 4 hour 10 50 20

blalblabla

For each client, there is such a excel spreadsheet back and I have a database table to store the data. My problem is how to automate the data import process. Thanks for your help.

 

[msand]

Re: How to automate the data impor process from Excel to Acc

Thanks, LT. Now the question is when I get the filled excel spreadsheet, I only want to transfer the new data to the existing database. I dont want to replace the existing database. So I have to make sure the new data will automatically add to the first empty row in the Access table. How to modify the code to achieve this?

 

[LTunnicliffe]

Re: How to automate the data impor process from Excel to Acc

Assuming that each spreadsheet only contains new data, then you shouldn't have a problem because this will only append the data to the bottom of the table, at least in 97.

 

[msand]

Re: How to automate the data impor process from Excel to Acc

Hi, LT:

No matter how I make sure the path of the file is correct. The run-time error 7866- "Access cant open the database because it is missing or opened exclusively by another user".


Sub TransferDataToAccessTable()

Dim appAccess As Access.Application
Set appAccess = New Access.Application

ActiveWorkbook.Names.Add Name:="TransferRange", RefersToLocal:=Range("A1:F" & Range("A65536").End(xlUp).Row)


appAccess.OpenCurrentDatabase "C:\Documents and Settings\Myname\My Documents\Order Details.db"


appAccess.DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel97, _
"Order Details", _
"C:\Documents and Settings\Myname\My Documents\testexcel.xls", _
True, "TransferRange"

appAccess.CloseCurrentDatabase
appAccess.Quit

MsgBox "Transfer Complete"

End Sub


Also, for the excel spreadsheet, the sheet 1 is includes the data I want to import. Please help find what the problem is with the code above. Thanks,

 

[LTunnicliffe]

Re: How to automate the data impor process from Excel to Acc

Try changing the "Order Details.db" to "Order Details.mdb" all access file names have the sufix ".mdb".

LT

 

[msand]

Re: How to automate the data impor process from Excel to Acc

Hi, LT:

I change db to mdb. The same problem appeared. When i used debug, the following code was highlighted. I dont know why.


appAccess.OpenCurrentDatabase "C:\Documents and Settings\Myname\My Documents\Order Details.mdb"

 

[Philem]

Re: How to automate the data impor process from Excel to Acc

Hullo. Hope you don't mind my jumping in. MSAND, is that line in your last post a DIRECT copy from your code? If so, I strongly suggest that you modify it, replacing Myname with your system login name. Sounds like you are having problems getting the correct path. So, instead of going direct to My Documents from an Explorer window, go from the top down, C: and open Documents and Settings. You should see several subdirectories in there. Look for one that most closely matches your name. Enter that directory's My Documents subfolder, and see if your database is there. If yea, the folder name two levels above where you are is what you need to insert instead of Myname in the OpenCurrentDatabase statement.

And, you are sure the code supplied is placed in EXCEL, not in ACCESS? That was my first thought upon reading this. I've done stranger things, trust me! :D

Also, please remember that we are not consultants on this board. We offer help freely, but, it is expected that the person asking for help will do some work on their own. It appears to me that you wish to have this solution handed to you on a platter. While somebody may well do that for you, it is not the norm, and should not be expected.

HTH

P

 

[msand]

Re: How to automate the data impor process from Excel to Acc

hi, philem: thanks for the help. Maybe I asked too many questions but trust me I am just new to VBA and confused to the errors on the codes. I am not intentionaly expecting somebody is going to hand the answer. I definitely understand this. If continuting posting question will make people think I am begging an answer, I will stop. THanks.

 

[Philem]

Re: How to automate the data impor process from Excel to Acc

Hullo. Wow! Yet again I've managed to make a complete butt out of myself. I did not mean that you should stop posting. Doing a quick read of the thread, I formed an incorrect impression. Please keep posting if you continue to have questions.

P

 

[msand]

Re: How to automate the data impor process from Excel to Acc

oh, sorry for the misunderstanding. Believe it or not, this code is my first VBA code I insert in the excel. So, forgive me if I did somthing inappropriate, even unintentionally. I believe I will contribute more when I pick up later on.

OK, back to the code. . I found the name and path is right. I put it in excel modul and then I put it to the sheet that has data. Run the code. An msgbox bounced up with a red x. What does that mean?

THanks,

 

[Philem]

Re: How to automate the data impor process from Excel to Acc

<SNIP>
OK, back to the code. . I found the name and path is right. I put it in excel modul and then I put it to the sheet that has data. Run the code. An msgbox bounced up with a red x. What does that mean?

THanks,

Was there any message in the box, or did it just show an X?

P