How to avoid hardcoding a filepath

[rhkirk]

I am writing a macro that pastes all of the file names in a folder into a column. The code works but I don't know how to search for the file rather than hardcode it.

Sub FetchNewNames()

Dim myPath As String
Dim myFile As String
myPath = "C:\Users\rhkirk\Desktop\Original\"
myFile = Dir(myPath & "*.*")

r = 2
Do While myFile <> ""
    Cells(r, 2).Value = myFile
    r = r + 1
    myFile = Dir
Loop

End Sub

[Jan Karel Pieterse]

Like this:

[Hightree]

Like what

[Jan Karel Pieterse]

Sorry, lost the connection :-) Like this:

Sub FetchNewNames()
    Dim myPath As String
    Dim r As Long
    Dim myFile As String
    Dim oFd As FileDialog
    Set oFd = Application.FileDialog(msoFileDialogFolderPicker)
    myPath = "C:\Users\rhkirk\Desktop\Original\"
    With oFd
        .InitialFileName = myPath
        If .Show Then
            myPath = .SelectedItems(1) & Application.PathSeparator
            myFile = Dir(myPath & "*.*")

            r = 2
            Do While myFile <> ""
                Cells(r, 2).Value = myFile
                r = r + 1
                myFile = Dir
            Loop
        End If
    End With
End Sub

[mattreingold]

You could also do something like this:

ChDir "C:\Users\User\Folder\DocName"

xlFile = Application.GetOpenFilename("All Excel Files (*.csv*)," & _
    "*.xls*", 1, "Select Excel File", "Open", False)
  
xlFileName = fso.GetFileName(xlFile)

My company often saves files to the same places, thus you can specify a folder to look into and it will show all .csv (you can change the file type) files - then you can pick the one you wish and then xlFile will have stored in it the full filepath, file name and extension of the chosen file. You could also throw this in a loop to open multiple documents using a counter variable etc.

The fso command grabs just the file name also - I figured it may be helpful to you (extension included).

NOTE: to use the fso command you need to addin Microsoft Scripting Runtime which is EASY. Just go to tools -> References -> check the box.

Hope this helps!

[mattreingold]

Re-reading your post, it seems like my above solution would best be suited for you in a loop, using a simple counter variable and using xlFileName as an array/vector could easily store all file names (extensions included) which could then easily be pasted into a column in excel.

Cheers!

[snb]

Like ?

sub M_snb()
  sn=split(createobject("wscript.shell").exec("cmd /c dir ""C:\Users\rhkirk\Desktop\Original\*.*"" /b/a-d").stdout.readall,vbcrlf)
  cells(1).resize(ubound(sn)+1)=application.transpose(sn)
End Sub

[Jan Karel Pieterse]

@snb: Appears to me that is still a hard-coded path, rather than one the user might pick :-)