Want to read a notepad file from a user defined location

[PrashanthNair]

Hi guys,

I am having trouble with my code. From the code shown, the "mypath" constant is embedded into the vba while reading for the file location. i tried representing the location in spreadsheet but the mypath constant could not retrieve the location. Every time i want 2 open a file,i have to manually change the file location and also the file name.

Option Explicit
 
'---------------------
'----- EDIT THIS -----
'---------------------
Const myPath = "U:\Prashanth\Stereoplot Tool\tool\"
Const delim = vbTab

Sub MergeMultipleTextFiles()
    Dim myFile As String        'filename
    Dim strRecord As String     'record string
    Dim arrRecord()             'record array
    Dim fNum As Integer         'free file number
    Dim RowCounter As Long      'row counter
    Dim i As Long               'loop variable
 
    On Error GoTo errHandler
    myFile = Dir(myPath & "Scotia-7_Dip_file.txt")
 
    RowCounter = 0
    Do While myFile <> ""
        fNum = FreeFile
        Open myPath & myFile For Input As #fNum
 
        Do While Not EOF(fNum)
            'read in the row into the record variable
            Line Input #fNum, strRecord
            'add the filename to the record variable
            strRecord = myFile & delim & strRecord
 
            RowCounter = RowCounter + 1
            'resize the record array
            ReDim Preserve arrRecord(1 To RowCounter)
            arrRecord(RowCounter) = Split(strRecord, delim)
        Loop
 
        Close #fNum
        'get the next file in the directory
        myFile = Dir()
    Loop
    '
    '------------------
    '----- OUTPUT -----
    '------------------
    With ThisWorkbook.Sheets("Input_Data").Range("A1")
        For i = 1 To RowCounter
            .Offset(i - 1).Resize(, UBound(arrRecord(i)) + 1).Value = arrRecord(i)
        Next i
    End With
 
errExit:
    'close all open text files
    Reset
    Exit Sub
errHandler:
    'error message goes here
    Resume errExit
End Sub

 

[Weaver]

i tried representing the location in spreadsheet but the mypath constant could not retrieve the location.

I'm not quite sure what you mean by this, since it doesn't look like you're trying to do this in the code you've supplied.

One thing I have noticed is this

myFile = Dir(myPath & "Scotia-7_Dip_file.txt")

This code is only ever going to supply a maximum of 1 filename and dir only ever returns the filename and not the full path, at least in my experience, so really you don't need the dir command at all, since all it's going to return is
""Scotia-7_Dip_file.txt", unless you're using it to detect whether the file is actually there of course. This means that the DIR() statement issued within the loop will use the conditions applied the first time Dir() is used, so it won't loop through all the files in the folder.

try

myFile = Dir(myPath & "*.txt")

and see what happens. You may need some other way of identifying whether the files exist. I use this function, which returns true if the argument is a valid file

Function checkfile(strFile)
    With CreateObject("Scripting.FileSystemObject")
        checkfile = .fileExists(strFile)
    End With
End Function

 

[PrashanthNair]

Thanks for your help...it does find the notepad that i wanted...by the way,how if i write the path link address at a specific excel location...as in

Const myPath = Sheets("Sheet1").Range("a1").Value


is this possible? cause i m getting error for this..

 

[Weaver]

Just so I know, is there a specific reason you're using constants for this?

If not, I'd suggest you dim a variable. If it needs to be global, you can still define it outside of a procedure.

I'm guessing the basic problem is that vb isn't happy to use a 'volatile' expression to define a constant (volatile because cell values can change independantly of the code, perhaps?)

 

[PrashanthNair]

Awesome...it really worked with dim..thanks a lot ya..actually i got this code from the internet and trying to edit it to fit in my need..but thanks to you,i managed to change it already...