Importing a txt file into Access 07 via VBA

[NathanL]

Hello and Good Day,
I am having some issues with importing a text file that is a screen scrape from another program. I was wondering is there a way to make this work using VBA Scripting in access 07, I have not yet tried this as my VBA skills are lacking.

Example: The first row are my headers though i would honestly like to change the format and the wizard doesn't like the last two column names (formating issue with date/text).

QGRP Description 07/07 07/06 07/02 07/01 Older Total
TEST TEST CALL QUEUE 1 2 3 4 5 6
BEST BEST Push Queue 1 2 3 4 5 6

I need the table to look like this:
QGRP Description Date Volume ***Headers***
TEST TEST CALL QUEUE 07/07 1
TEST TEST CALL QUEUE 07/06 2
TEST TEST CALL QUEUE 07/02 3
TEST TEST CALL QUEUE 07/01 4
TEST TEST CALL QUEUE Older 5
TEST TEST CALL QUEUE Total 6
BEST BEST PUSH QUEUE 07/07 1
BEST BEST PUSH QUEUE 07/06 2
BEST BEST PUSH QUEUE 07/02 3
BEST BEST PUSH QUEUE 07/01 4
BEST BEST PUSH QUEUE Older 5
BEST BEST PUSH QUEUE Total 6
Any assistance with this or at least pointing me in the right direction would be greatly appreciated.

Thanks,
Nathan

 

[vbaInet]

Can you show what you've written so far?

Welcome to AWF by the way

 

[NathanL]

Thanks for the welcome =)

I haven't started any code because i'm not sure if its possible and if so were to begin. This will be my first major stab at VBA coding and all other things i have done has been simple things.

 

[vbaInet]

Looks like you're on a long journey then. You would need to know the basics before even attempting this. Here's a pretty good source:

http://www.functionx.com/

 

[NathanL]

Thanks for the link I will see if i can pull it off

 

[vbaInet]

I will give you some pointers though

1. First thing you do is create a table (manually) with the correct field names based on your header. Don't include asterisks, hyphen, space, colon etc... in the field names or table name even
2. If you're going to use these records for anything meaningful then really you should normalize, which of course would make the importing process harder.
3. If not, then you can read the text file but skip the first line (by not reading it)
4. Read the line into an array and split the array using the Split() function
5. Read each item in the array (using a For Loop) and append into the table using an INSERT statement