Macro to pick File Name and assign it to variable

petes

Board Regular
Joined
Sep 12, 2009
Messages
168
I have a Excel File in the following folder:

C:\PA\

However, the name of the Excel File varies each day. Hence, I need a macro, so that i can assgin this Excel File name to a variable and use it whereever it is required.

Also, is it possible to check, at any point of time there is only one excel file in the path mentioned above. Else, display some kind of error..??

Your help is very much appreciated!!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hi Petes ,

You can use below code for file picker ..

Sub fileFound()
Dim objFiles, objFld As Object
Set objFiles = CreateObject("Scripting.FilesystemObject")

Set objFld = objFiles.GetFolder("C:\PA")



If objFld.Files.Count < 1 Then ' will check more than one file

MsgBox "more than file "

Exit Sub ' if true thn exit

Else

For Each objFls In objFld.Files

Filename = objFls.Name ' filename will give you file name of ur Excel file

Next objFls

End If


End Sub
 
Upvote 0
Thanks a lot Puru... Just one thing. I am calling that variable into message box at the end of this code.

Actually it is displaying the Path of the File Name. But I need to display the File Name instead of Path.. May be both, Path and File Name or only the File Name. Anything is fine, preferably only the File Name.


Code:
Sub fileFound()
Dim objFiles, objFld As Object
Set objFiles = CreateObject("Scripting.FilesystemObject")
Set objFld = objFiles.GetFolder("C:\PA")
If objFld.files.Count > 1 Then ' will check more than one file
MsgBox "more than one file "
Exit Sub ' if true thn exit
Else
For Each objFls In objFld.files
Filename = objFls.name ' filename will give you file name of ur Excel file
Next objFls
MsgBox ("File Name" & objFld)
End If

End Sub

Any thoughts..?? Thanks!!
 
Upvote 0
You can use any of below code for message box

MsgBox objFld.Path & "\" & Filename ' for both path & file name

MsgBox objFld.Path ' for path

MsgBox Filename ' For File name



Hope this would clear to you ......:)
 
Upvote 0

Forum statistics

Threads
1,211,685
Messages
6,103,291
Members
447,853
Latest member
olddutch7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top