VB/Module - InStr

[Moxioron]

Hello all.

Sigh. I am at my wits end ... again. HiTechCoach was trying to assist me as well on something similar, but I am still at a loss.

I have a module in Access that I use to import data into a table and it works great. However, there is one line that I want to pull in, but I don't know what the heck I am doing.

The text file looks something like this:
Alternate Account: 100/000000024681012141618
Ashton Kutchter

To bring in the Alternate Account number I used:
If StrComp(Mid(strFileData, 1, 9), "Alternate", vbBinaryCompare) = 0 Then
strAlt = Mid(strFileData, 31, 14)
End If

That works great.

My issue is grabbing the customer name (Ashton Kitcher)
If InStr("Alternate", vbCrLf) > 0 Then
strCustomerName = Mid(strFileData, 1, 43)
End If

I am sure that will give some of you a chuckle. Of course nothing comes back.

Please help. Thank you.

 

[boblarson]

So the text file has it in two lines?

 

[Moxioron]

Yes, the text file has multiple lines.

My dilemna is that all of the other fields have a constant on the same row that I can grab onto (like I showed in my example "Alternate").

The customer name has nothing else in the row. So I am trying to figure out how to leverage the row above the customer name to import that field as well.

Hope that made sense.

 

[boblarson]

I would iterate through the file and either import line by line or I would just run some code to move the other line to the same line as the rest. Importing without doing that isn't going to work well at all.

So, the big question is what is the code you are using to import (as you didn't share that, but it is important).

 

[Norie]

Why are you looking for vbCrLf in the string 'Alternate'?

If anywhere shouldn't you be looking in the string variable strFileData?

 

[boblarson]

Why are you looking for vbCrLf in the string 'Alternate'?

If anywhere shouldn't you be looking in the string variable strFileData?

And why are you posting the question again?
http://www.mrexcel.com/forum/showthread.php?t=565373

 

[Moxioron]

1.) I don't know what I am doing lol. I was hoping that 'vbCrLf' would perform a carriage return to the next line...

2.) I did not post the same question again. The string you are referencing was to convert the text file to one line. This question was how to import data on a different line. ??

 

[Norie]

What exactly is the original string?

Is it the blue text in the first post?

If it is then the InStr might work if you looked in that rather than the word 'Alternate', which for some reason I don't think is going to have a carraige return.

 

[revans]

When you import the file into a table make an AutoNumber field for a "LineID". Then when you are looking for your Alt Account grab the line id, run a query that returns the LineID+1 to get the data.

hth,

Rich

 

[Moxioron]

Someone was able to help me from another user forum.

I did a horrible job explaining my situation and I apologize for the confusion.

Here is the code that finally gave me what I wanted, which was to retrieve the customer name when importing the text file.

'get the alternate acct
If StrComp(Mid(strFileData, 1, 9), "Alternate", vbBinaryCompare) = 0 Then
strAlt = Mid(strFileData, 31, 14)
Line Input #intInFile, strFileData 'read line.
strCustomerName = Mid(strFileData, 1, 43)
End If