Reading external txt file issue

[agupta]

Hi - I need help with VBA reading an external .txt file. When I read the 1st record I get accurate data but when I read the next record I get 2 extra bytes and for the 3rd record it adds on another 2 bytes (i.e 4 bytes total) look at example:

External .txt file:
DbservNM \\servernm\one
McrServNM \\servernm\two
RptServNM \\servernm\three


Code in VBA:
Private Type ServerRowRec
strServerName As String * 12
strPathName As String * 16
End Type

Function GetDBPath(strDirectDB As String, strDirectMacroDB As String, strDirectRPTDB As String)
Dim strDirectory As String
Dim srtReportDB As String
Dim strMacroDB As String
Dim strRowRec As ServerRowRec
Dim strServerPath As String
Dim intRow As Integer
Dim intRecNum As Integer
Dim strColA As String
intRecNum = 1
strServerPath = "Z:\program Files\sqllib\RMPCommFile.txt"
Open strServerPath For Random As #1 Len = Len(strRowRec)
Do Until intRecNum > 3
Get #1, intRecNum, strRowRec
If strRowRec.strServerName = "DbServNM " Then
strDirectDB = strRowRec.strPathName
ElseIf strRowRec.strServerName = "McrServNM " Then
strDirectMacroDB = strRowRec.strPathName
ElseIf strRowRec.strServerName = "RptServNM " Then
srtReportDB = strRowRec.strPathName
End If
intRecNum = intRecNum + 1

Loop


Close #1
End Function

Results as shown when the cursor is placed over the field:
strRowRec.strServerName="DBServNM "
strRowRec.strServerName " McrServNM " ------> (the space appears as squares prior to McrServNM)
strRowRec.strServerName "ee RptServNM"

PLEASE HELP !

 

[jasmith4]

I don't know why your code insists that the server name is 12 long and the path name is 16 long. Forget the Private Type, and forget Random access. Declare another string to receive a whole text line at a time, use Line Input to read each line, and split the two halves at the first space:

Function GetDBPath(strDirectDB As String, strDirectMacroDB As String, strDirectRPTDB As String)
    Dim strDirectory As String
    Dim strReportDB As String
    Dim strMacroDB As String
 
    Dim strRowRec As String         ' No Private Type: separate strings
    Dim strServerName As String, strPathname As String
    Dim SpacePos As Long            ' position of first space
 
    Dim strServerPath As String
    Dim intRow As Integer           ' not used
    Dim intRecNum As Integer
    Dim strColA As String           ' not used
 
    strServerPath = "Z:\program Files\sqllib\RMPCommFile.txt"
    Open strServerPath For Input As #1
    For intRecNum = 1 To 3
 
        Line Input #1, strRowRec    ' Get next line and split it
        SpacePos = InStr(1, strRowRec, Space$(1))
        strServerName = Trim$(Left$(strRowRec, SpacePos - 1))
        strPathname = Trim$(Mid$(strRowRec, SpacePos + 1))
 
        Select Case strServerName
        Case "DbServNM":    strDirectDB = strPathname
        Case "McrServNM":   strDirectMacroDB = strPathname
        Case "RptServNM":   strReportDB = strPathname
        End Select
 
    Next
 
    Close #1
 
End Function

 

[agupta]

Thank you that worked. I need help with one more thing:
- I need to pass info (server name) to a macro. How do I do that in VBA?

 

[jasmith4]

I don't know what you mean.