PDA

View Full Version : Problem Reading Text file with Excel Macro (VBA)



xc0n
06-22-2012, 09:39 AM
Hi,
I am new to VBA and am designing a program that grabs a value that another program that stores the value in a text file. When my VBA function grabs this value it displays it with three extra characters in front of it "  ". The problem here is that the rest of my program needs this value to run by matching it to a database and pulling the information associated with the value. With the three extra characters in front of it the value can't be matched and it errors out saying value can't be found.

My function that reads the text file is:

Private Function ReadText() As String

Dim sFileName As String
Dim iFileNum As Integer
Dim sBuf As String


sFileName = "C:\temp.txt"

If Len(Dir$(sFileName)) = 0 Then
Exit Function
End If

iFileNum = FreeFile()
Open sFileName For Input As iFileNum

Do While Not EOF(iFileNum)
Line Input #iFileNum, sBuf
Loop


ReadText = sBuf
End Function

The value out put looks like: LWM00815
*The actual needed value is LWM00815*

If anyone has had this problem or can see an issue in my code any help is appreciated.

Thank-you

Kenneth Hobs
06-22-2012, 09:44 AM
Welcome to the forum! Please use VBA code tags for code.

Use Right() and Len() to trim the characters. e.g.
Dim s As String
s = "123Hello World!"
MsgBox Right(s, Len(s) - 3)

xc0n
06-22-2012, 09:47 AM
Thank you Ken for quick response. That works great!