Import Excel into Access Table

[Santa-D]

I've got an excel sheet that I need to import into a table, however, I
want the function to automatically manipulate the data prior to it
being imported.

For example, i have a field called [Flt No] and the format is 16107 for
example, when the VBA function is running I want it to add "EA" to the
record so it becomes EA16107 but the field I want it to import into is
called [ORDNO]

another field in the same dataset I want to import is [Product], this
field has many different product descriptions which I want to
manipulate prior to being imported.

I want to convert the following:

Premium UL - PULP
ULP - ULP
Ultimate - PULP
Unleaded Motor Spirit - ULP
LPG - LPG
Autogas - LPG
Monthly AD - Monthly AD

Any help would be wonderful, ps: I'm still an amateur in VBA

[Keith Wilby]

"Santa-D" <st*****@forgone.org> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...

I've got an excel sheet that I need to import into a table, however, I
want the function to automatically manipulate the data prior to it
being imported.

For example, i have a field called [Flt No] and the format is 16107 for
example, when the VBA function is running I want it to add "EA" to the
record so it becomes EA16107 but the field I want it to import into is
called [ORDNO]

another field in the same dataset I want to import is [Product], this
field has many different product descriptions which I want to
manipulate prior to being imported.

I want to convert the following:

Premium UL - PULP
ULP - ULP
Ultimate - PULP
Unleaded Motor Spirit - ULP
LPG - LPG
Autogas - LPG
Monthly AD - Monthly AD

Any help would be wonderful, ps: I'm still an amateur in VBA

Is this a one-off import or something regular? If it's a one-off, copy your
data into the destination table then use an update query to append the
prefixes.

If this is something regular and you're new to code then post back. It
would also be useful to know what version of Access and which OS you're
using.

Regards,
Keith.
www.keithwilby.com

[Santa-D]

> If this is something regular and you're new to code then post back. It

would also be useful to know what version of Access and which OS you're
using.

Hi Keith,

I'm using Windows XP-SP2 & MS Access 2003.
Yes, it would be a regular thing and I would probably manipulate the
code and adopt it elsewhere within the system I'm building.

[Keith Wilby]

"Santa-D" <st*****@forgone.org> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...

If this is something regular and you're new to code then post back. It
would also be useful to know what version of Access and which OS you're
using.

Hi Keith,

I'm using Windows XP-SP2 & MS Access 2003.
Yes, it would be a regular thing and I would probably manipulate the
code and adopt it elsewhere within the system I'm building.

If you have not yet applied SP2 to Office 2003 then you can use the linked
table method to connect to Excel from Access and use the linked table as you
would a native table. Therefore you can use an append query with calculated
fields (eg Expr1: "EA" & [Flt No]).

If you have SP2 applied then the linked table method will not work with
Excel. This makes linking to Excel relatively tricky compared to the linked
table method. You'd need to first set a reference to the Excel object
library in your VBA project. You'd then need to use some code to connect to
your spreadsheet. This method is known as "Office Automation" and was new
to me until recently.

I was going to post a shed load of code at this point but I learned about
automation by reading a book and I would recommend you do the same so that
you get a good understanding rather than "monkey see, monkey do". The book
I used was this:

http://www.amazon.com/gp/product/059...Fencoding=UTF8

There is another option to consider - do you really need to store these
prefixes? If they're consistent then you can import the data as is and use
select queries at run-time to append them to form and report controls.

HTH - Keith.
www.keithwilby.com