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

Just a red x without any message and two buttons on that: ok or help. When help was click. no help clue at all. When checking the database, no data was imported. Ok, i think i will stop here and back to the vba books to find the answer myself. Thanks for your time.

 

[msand]

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

The code finally worked. It is a very enjoyable process to learn from those who posted feedback. Thanks for this.

One remaining question is: in the following code:

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

If I save this excel file as a different name, \testexcel.xls in the code "C:\Documents and Settings\Myname\My Documents\testexcel.xls" will not automatically change to the name I saved. If you have experience dealing with this, can you give a hint? My Access VBA books are still in shipping process so I really dont have a good resource for this question. Thanks.

 

[LTunnicliffe]

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

Activeworkbook.Path returns the path of the workbook and Activeworkbook.Name return the name of the workbook. What I typically do then is concatenate them together like Activeworkbook.Path & "/" & Activeworkbook.Name.

Hope this helps!

I hope that everything I have posted has been useful and that you learned something. I hope that everyone on this board that responds to a question feels that they are a consultant and that if their answer causes confusion or other concerns that those are adressed. Again I hope that this has helped you further you horizons.

LT

 

[msand]

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

Hi, LT, thanks for the help. Definitely, your posts opened the door of VBA for me. I really appreciate that and do feel the power of code.

 

[msand]

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

Hi, LT,

In the following code, if I changed "C:\Documents and Settings\Johnsmith\My Documents\testexcel.xls" to ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

The code will be highlighted for debug and when I changed back, it would work again. Is there something wrong with the "ActiveWorkbook" line code?


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

 

[LTunnicliffe]

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

You do have this in Excel, correct? If so then to MsgBox the path and file name so you can see what it is.

 

[msand]

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

HI, LT,

#1. I am sure it is Excel and have run successfully if I use "C:|Document and Settings\myname\My Documents\testexcelfile.xls".

#2. Put ActiveWorkbook.Path & "\"& ActiveWorkbook.Name in MsgBox and it showed correct path "C:|Document and Settings\myname\My Documents\testexcelfile.xls" on the MsgBox.

#3. When put ActiveWorkbook.Path code into the original code to replace C:\.... The error message showed up like this:

"Run-time Error '2495' - Application-defined or object-defined error"

#4. Check help on this error and the following content is related to this error.


[It may be that in accessing objects from other applications, an error was propagated back to your program that can't be mapped to a Visual Basic error.

Check the documentation for any objects you have accessed. The Err object's Source property should contain the programmatic ID of the application or object that generated the error. To understand the context of an error returned by an object, you may want to use the On Error Resume Next construct in code that accesses objects, rather than the On Error GoTo line syntax.

Note In the past, programmers often used a loop to print out a list of all trappable error message strings. Typically this was done with code such as the following:

For index = 1 to 500
Debug.Print Error$(index)
Next index

Such code still lists all the Visual Basic for Applications error messages, but displays "Application-defined or object-defined error" for host-defined errors, for example those in Visual Basic that relate to forms, controls, and so on. Many of these are trappable run-time errors. You can use the Help Search dialog box to find the list of trappable errors specific to your host application. Click Search, type Trappable in the first text box, and then click Show Topics. Select Trappable Errors in the lower list box and click Go To. ]


#5. I am not quite sure what it meant for the last paragraph. Can you give some insight?

Thanks,