Parsing a line in Excel using VBScripting

[DevilDog#1]

Anyone has a good function I can use?

InStr or Midstr?

[IcIshoot]

what are you trying to accomplish? (split a string, pull data from it, etc)

from what I remember, instr basicaly checks that a substring is in the string

mid() will pull letters from the given string, starting where you tell it to and the length to pull out.
IcI

[DevilDog#1]

Yep. Pulling data from row # 5 of an Excel File.

Looking for 9 digit and 5 digits on the row.

[IcIshoot]

can you give a example of what the line looks like? as is the line consistently the same?

An other thought, are you pulling this data from an excel sheet that is open up and are the numbers your trying to get in their own cells?


IcI

[DevilDog#1]

Yep

ADJASDHj: 123456789 00000000 ASDSDHS: 1234567

They are on row 5 column A

[IcIshoot]

I had an reply all typed up, went to submit it, submission failed


Ok, I did what it looks like your trying to do:

But this method will only work if the length of each field stays the same if you have multiple rows like your sample (ie, all the letter and number groupings stay the same length, only the data changes)


using cell A12 for my source, and copying your sample

I put the first group of numbers you have bolded into cell A15 by entering "=MID(A12,10,10)" into cell A15.


Put the second group of bold numbers into cell A17 by entering "=MID(A12,38,8)" into said cell.

(both examples with out the " marks.


IcI

[DevilDog#1]

Originally posted by icishoot
I had an reply all typed up, went to submit it, submission failed


Ok, I did what it looks like your trying to do:

But this method will only work if the length of each field stays the same if you have multiple rows like your sample (ie, all the letter and number groupings stay the same length, only the data changes)


using cell A12 for my source, and copying your sample

I put the first group of numbers you have bolded into cell A15 by entering "=MID(A12,10,10)" into cell A15.


Put the second group of bold numbers into cell A17 by entering "=MID(A12,38,8)" into said cell.

(both examples with out the " marks.


IcI

Thanks ICI ...

Below is the code I came up with and works like a charm.

PHP Code:

Const ForReading = 1 ,ForWriting = 2
Const File_Path = "C:\"
Const File_Write_Path ="C:\"
Const Read_File_Name = "xyz.txt"
Const Write_File_Name ="abc.txt"


Dim objFSORead, objFSOWrite, objFileRead, objFileWrite, strTextRead, strTextWrite, strRxNum, strOrdNum

Set objFSORead = CreateObject("Scripting.FileSystemObject")
Set objFSOWrite = CreateObject("Scripting.FileSystemObject") 

Set objFileRead = objFSORead.OpenTextFile(File_Path & Read_File_Name, ForReading)
Set objFileWrite = objFSOWrite.CreateTextFile(File_Write_Path & Write_File_Name, ForWriting) 

Do Until objFileRead.AtEndOfStream

        strTextRead = objFileRead.ReadLine

'Check inputfile  which contains the X and Y
         If condition statement Then

            strTextWrite = strTextRead 
            objFileWrite.WriteLine strTextWrite  
           

            strRxNum = Mid(strTextWrite, 22, 9)   
            strOrdNum = Mid(strTextWrite, 66, 7)


       End If

Loop


objFileRead.Close
objFileWrite.Close   

Set objFileRead = Nothing
Set objFSORead = Nothing
Set objFileWrite = Nothing   
Set objFSOWrite = Nothing 


Need some Error handling but other than that it should be good.

[Scott]

gj dd