VBA code to import delimited text file

[thecapt]

Hi,

I am a total novice at VBA code, so I am looking for alot of help with this one.

I have a text file, which contains numbers which are spaced with spaces. I have successfully imported the file before using the import wizard but would prefer for an automatic squence instead via VBA code.

I know that I need to use tranfertext but I haven't a clue how and most most online stuff shows how to do this with comma sperated values.

The following is an exmaple of the text file data

1 1
2
3 2 2 4 2 2 2 3 3
5
1
6
1 1
6
6 7 7 7 6 6 8 8 8 8
6 9 4 4 4 10 3 10 5 10 4 4 4

 

[thecapt]

***Follow up***

I have found a way of setting up the parameters of how the importing of the text file by saving the import wizards parameter.

I believe that there is a way of calling this when using the tranfertext in vba. Can anyone help my with the vba code??

 

[Dennisk]

search Access help for transfertext and it will show you how to use specifications with the transfertext action.

 

[thecapt]

I have search online and in access help for transfertext but I am getting nowhere tbh.

I know that it need to go something like this:

DoCmd.TransferText acImportDelim, SpecificationName, TableName, Path & FileName

but I have no idea how to declare it in the correct manner

 

[thecapt]

***Follow up ***

I used the following code which seems to work:

DoCmd.TransferText acImportDelim, "Import Specification", "tbl1", "C:\Documents and Settings\Admin\Desktop\seq2.txt"

what I am now wondering is it possible to add a message box on completion of importing the file and will it just be a case of adding a msgbox line after the tranfertext line of code or should I be preforming some sort of validation before outputing a message box.

 

[thecapt]

just nocticed that by using the transfertext method as stated above it doesn't pull all of the data but just the first 13 seperated values.

Any ideas how to fix this??

 

[thecapt]

Hello again,

I am getting desperate on this matter now and I really need help.

The text file is a unfixed width, delimited, and blank space seperated file.

I am guessing that using tranfertext option isn't gonna much use.

Please, please help me, it is driving me mad.

 

[WayneRyan]

Cap,

What does the data represent?

Is it just one column in a table?

need more info,
Wayne

 

[Bat17]

did you get all the data when you imported by hand?

 

[thecapt]

did you get all the data when you imported by hand?

no it cuts off the after 13 spaced entrys

 

[Bat17]

Looks like a problem with the data then. Have you tried opening it with XL to see if that can handle it?
Can you look at the data as a text file to see what happens after row 13 to upset it?
I think that the wizard only looks at the first few rows when it sets things up so, if the problem is further down, you may have to set things up manualy to sort it out or even have to import the whole thing one line at a time using code.
but the first thing is to understand the data to know where the problem is.

Peter