Convert Tabs to White Space

[SgtJarrow]

I have a function that converts the vbTab to a single white space. What I really need is for the vbTab to be converted to the appropriate number of white spaces that the vbTab covers....in other words, my code does this

one two three four
becomes
one two three four

and I need it to be
one two three four
but be spaces instead of tabs

I have attached my code....maybe it can be modified...

'*************Start Code******************
Public Function dhTranslate(ByVal strIn As String, ByVal strMapIn As String, _
ByVal strMapOut As String) As String

Dim lngI As Long
Dim lngPos As Long
Dim strChar As String * 1
Dim strOut As String

'If there's no list of characters to replace, there's no point going on with the work
If Len(strMapIn) > 0 Then
'Right fill the strMapOut set
If Len(strMapOut) > 0 Then
strMapOut = Left$(strMapOut & String(Len(strMapIn), Right$(strMapOut, 1)), Len(strMapIn))
End If
For lngI = 1 To Len(strIn)
strChar = Mid$(strIn, lngI, 1)
lngPos = InStr(1, strMapIn, strChar, vbBinaryCompare)
If lngPos > 0 Then
'If strMapOut is empty, this doesn't fail, because Mid handles empty strings gracefully
strOut = strOut & Mid$(strMapOut, lngPos, 1)
Else
strOut = strOut & strChar
End If
Next lngI
End If
dhTranslate = strOut

End Function
'****************End Code***************** It's not important that someone else can do in one step what it took you ten to do...the important thing is that you found a solution.

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer

 

[Maquis]

I'm not sure that would be possible because a tab character is only a single character (Ascii Char(9)) and that character means different things to different programs.

For example, a tab in Notepad may equal 5 spaces, but in MS Word it may be 7 spaces and in Joe's Funky Editor Display it might be 2 spaces.

I think the best you can do is just pick a set amount of spaces that looks right to you (say, 5 spaces maybe) and just convert all the char(9) characters to 5 spaces.

Something like this:

Public Function Tab_Convert(word as string) as string
    Dim Length As Byte
    Dim NewWord as string
    Dim i As Byte
    Dim AsciiVal As Byte
    
    Length = Len(Word)
    For i = 1 To Length
        AsciiVal = Asc(Mid(Word, i, 1))
        If AsciiVal = 9 Then
           NewWord = NewWord & "     "   '5 spaces
        else
           NewWord = NewWord & Mid(Word,i,1)
        End If
    Next i
    Tab_Convert = NewWord
End Function

Maq
<insert witty signature here>

 

[Maquis]

oops, forgot to set NewWord to &quot;&quot; before the loop

Dang, I really wish you could retract posts here. Maq
<insert witty signature here>

 

[LittleSmudge]

To add a bit more complexity to the above from Maquis you'd need to identify the char position of the first vbTab using intPosn = Instr(strTestString,vbTab)
and the position of the next vbTab using
intNextPosn = Instr(intPosn+1,rstTestString,vbTab

Then extract the text string between the two tabs
strTempText = Mid(strTestString,intPosn + 1, intNextPosn - intPosn - 1)

Then the number of spaces that you need to replace the VBTab is
intNoOfSpaces = intDesiredGap - Len(strTempText)



'ope-that-'elps.

G LS

 

[SgtJarrow]

Maquis,

I know what you mean about editing/retracting posts....lol.

I agree that that would be the way to go in this instance, except that it just won't work accoring to the requirements....maybe there is another solution....

I receive a Word doc with the following format:

'***********Start Example****************
Safe Custody A/C - Removed For Security Purposes
Valuations - Debt
Month-End Market Value
CED USD 1,413,716.05 1.40bp USD 16.49
Valuations - Equity/Unit Trust
Month-End Market Value
AMS USD 467,786.77 1.50bp USD 5.85
CRF USD 1,696,785.06 1.00bp USD 14.14
FFT USD 741,357.02 1.50bp USD 9.27
HEL USD 411,163.14 4.00bp USD 13.71
LIS USD 115,797.63 7.50bp USD 7.24
MIL USD 516,480.88 2.00bp USD 8.61
NYK USD 4,186,642.93 0.75bp USD 26.17
PAR USD 1,352,913.13 1.50bp USD 16.91
ZUR USD 2,682,643.85 2.00bp USD 44.71

USD 163.10 EUR 180.29

Transactions
Against Payment/Book Entry Aganist Payment - Debt
CED 1 USD 15.00 USD 15.00

USD 15.00 EUR 16.58

Surcharges
Repaired
1 USD 8.00 USD 8.00

USD 8.00 EUR 8.84


Total EUR 205.71
'*************End Example*************

I need to be able to walk through this file and make changes according to some business rules. I have all the rules and stuff worked out. The problem lies in the whole thing being produced with tabs.....

For example, in the following line...
USD 15.00 EUR 16.58
the EUR appears in the 55th character place, but due to the tabs it is somethign like 15 or so, if I detab it.

I need to keep all formating of this file intact after I complete the business rules procedures. Maybe there is a better way to process this info.....Here is my top level process without the business rules.....

'***************Start Code*****************
Public Function UpdateInvoice()

Dim InputFile As String, strLine As String, OutputFile As String
Dim dblClientEURTotal As Double
InputFile = &quot;H:\Dublin\Billing\Client Billing\Robert\Access\invoice.txt&quot;
OutputFile = &quot;H:\Dublin\Billing\Client Billing\Robert\Access\invoice-out.txt&quot;

Open InputFile For Input As #1
Open OutputFile For Output As #2

Do While Not EOF(1)
Line Input #1, strLine
If Left$(strLine, 4) = &quot;Safe&quot; Then
Process Code
Print #2, strLine
ElseIf Mid$(strLine, 54, 3) = &quot;EUR&quot; Then
Process Code
Print #2, strLine
.
.
.
Else
Print #2, strLine
End If
Loop

Close #1
Close #2

End Function
'*************End Code****************

If you have any other suggestions, I am .

Thanks!!!! It's not important that someone else can do in one step what it took you ten to do...the important thing is that you found a solution.

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer

 

[SgtJarrow]

LittleSmudge,

Wow!! That's looks exactly like what I need to do.....

I will l.et you know. Thanks. It's not important that someone else can do in one step what it took you ten to do...the important thing is that you found a solution.

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer