PDA

View Full Version : How to avoid hardcoding a filepath



rhkirk
05-29-2018, 06:44 AM
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
05-29-2018, 07:13 AM
Like this:

Hightree
05-29-2018, 08:11 AM
Like what

Jan Karel Pieterse
05-29-2018, 08:15 AM
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
05-30-2018, 04:48 AM
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
05-30-2018, 05:38 AM
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
05-30-2018, 07:06 AM
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
05-30-2018, 07:26 AM
@snb: Appears to me that is still a hard-coded path, rather than one the user might pick :-)