Question Import a non-standard excel file into Access 2007

[Affa]

Hi All,

i need a support to import a non-standard excel file to access 2007.
The excel file has 3 field: one of these field is a set of numerical codes separated by semicolons ( so in each excel cell there may be two or more numerical code separated by semicolons). The DB Access that I need to create, must have a record for each numerical code, and each record must have the others two field replicated. How I can create this file into Access 2007???
Thanks very much

 

[DJkarl]

I would either format the data in Excel the way you want it to look in Access (easiest), or write some VBA code to handle the importing and splitting of the records as needed. Access really has no built-in tools that I know of to handle your scenario, you would need to do some custom coding.

 

[Affa]

ok thank you very much for the answer,
could you suggest me the vba code?

 

[DJkarl]

Well as I mentioned it will be custom code given the unique nature of the file you're trying to import. If you have never coded in VBA before then coding this project may be a little out of reach at first, I would suggest learning some basic skills before proceeding further.

If you have coded before but just not Excel automation then I would suggest a quick search on the forum, tons of examples of people doing simliar things, you should be able to pick something close to what you need and modify it.

 

[Affa]

Hi,
this is an example of my field:
3456789267;1893672064;3780267194;
Do you think that the split function is good for me to create 3 different records?
Sorry but i haven't a great experience in vba but it's important for me to find a solution
thank you very much.

 

[DJkarl]

Split will work fine to break the string into a variant array so you can iterate through it to build your records.