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!
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 :-)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.