Macro to pick File Name and assign it to variable

[petes]

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!!

 

[puru.sve]

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

 

[petes]

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.

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!!

 

[puru.sve]

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 ......

 

[petes]

Great!!! that works.. thanks Puru!!!