macro to find file

Np72

Board Regular
Joined
Dec 22, 2010
Messages
108
Hi,

Is there anyway that vba can find, open files. The files are always kept in the same place (for example C:test/home/Testddmmyy.xls) but the date changes everyday. Is there a way of inserting the date in a pop up box then vba will find and open the desired file in a template work book ??

Thanks

Neil
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub test()
Dim d As String
d = InputBox("Enter date, ddmmyy")
Workbooks.Open Filename:="C:\test\home\Test" & d & ".xls"
End Sub
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,685
Office Version
  1. 365
Platform
  1. Windows
Something like:
Code:
Sub OpenSelectFile ()
Dim FileLocation as String, InputDate as String
FileLocation = "C:\test\home\Test"
InputDate = InputBox("Enter date of file (ddmmyy):", "File date required")
Workbooks.Open Filename:=FileLocation & InputDate & ".xls"
End Sub
 

Np72

Board Regular
Joined
Dec 22, 2010
Messages
108
Cool, it works. However I forgot to add that the files are always called

H:\_TransfertPROD\InstitutionalAssetManagement\PamIT\Compliance\GVA\ISSCAP_Total_zero_GVA_YYMMDD.XLS

I tried inserting this into the macro but alas
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub test()
Dim d As String
d = InputBox("Enter date, ddmmyy")
Workbooks.Open Filename:="H:\_TransfertPROD\InstitutionalAssetManagement\PamIT\Compliance\GVA\ISSCAP_Total_zero_GVA_" & d & ".xls"
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,465
Messages
5,528,962
Members
409,848
Latest member
Blomsten
Top