Macro to open a file when only a partial name is known

I'm having a problem, I need to open a file e.g "C:\1523002327_ADP_Tab_Din_II(R).xls" in a macro however I only know the last characters "ADP_Tab_Din_II(R).xls" at the start is a random 10 digit reference number which I have no way of predicting , is there any way I can open this file knowing only C:\ADP_Tab_Din_II(R).xls?

Any help would be very much appreciated!

yes, but i have other files in the same folder.

Is that the only file with that ending?

Try:

Please Login or Register  to view this content.

No...after 3 hours i received a new file with different name eg.

First File name received at 03:00 am: 1523002327_ADP_Tab_Din_II(R).xls
Second File Name received at 06:00 am: 1545003548_ADP_Tab_Din_II(R).xls
Third File Name receveid at 09:00 am: 1546983254_ADP_Tab_Din_II(R).xls

?? is there any way I can open this file knowing only C:\ADP_Tab_Din_II(R).xls? Did you want to open all those files?

@John H. Davis, I may be wrong but I think you need to include folder path in the workbooks.open event (unless you call it from a workbook in the same directory).

Alternative,

Please Login or Register  to view this content.

Use this function to get the full name of the latest file matching a partial pattern:

Please Login or Register  to view this content.

For the newest file in the directory, try:

Please Login or Register  to view this content.

i have all files in the same Directory, and i need open the most recent file with getting the partial name.
Is not can be only the most recent file because i have another files with different names and more recent than this one.

Hi hbender
Does my post in #10 not give you exactly this?

Hi guys.

@cyiangou

Here is my problem....i'm not good with VB and i need a help to change the VB code =(

The code is responsible for open another file in the corporate network (with or without password), copy a range selection, close the file without save it and paste the selection in this spreadsheet. This code worked very well
My new problem is i have more files into the same shared folder C:\Importa. and i need open the most recent file with getting the partial name.
Is not can be only the most recent file because i have another files with different names and more recent than this one.


This is my vb code and feel free to make any change.


Sub Copiar_Dados()
Dim wsOrigem As Worksheet
Dim wsDestino As Worksheet
Dim wbk As Workbook
Dim passwd As String

passwd = "teste"

'File Destinations, open first
'Set the path of the same

caminhocompleto = "C:\Importa\1523002327_ADP_Tab_Din_II(R).xls"

'as the worksheet is protected by password necessary to open the file before you select.
'Opening the file as read / write, open as read-only gives error.
Set wbk = Workbooks.Open(Filename:=caminhocompleto, ReadOnly:=False, Password:=passwd)
'hides the file so that the user does not see the same.
wbk.Windows(1).Visible = False



'Files and Tabs Origin and Destination
Set wsOrigem = Workbooks("1523002327_ADP_Tab_Din_II(R).xls").Worksheets("ADP")
Set wsDestino = Workbooks("ADP_Oficial.xlsm").Worksheets("ADP")

With wsOrigem
wsDestino.Range("B5:CJ30000").ClearContents
.Range("a2:CI30000").Copy Destination:=wsDestino.Range("B5:CJ30000")

End With

'Close the Destination File and Save
Workbooks("ADP.xlsx").Close SaveChanges:=False


End Sub

Hi hbender

Copy the function from post #10 below your sub in the module, then change this line in your code:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

and this line:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

I haven't checked whether the rest of your code does what you need it to do. I've only given you the part where you resolve the fully qualified pathname of the latest file.

Edit: I hope someone else can help you with the rest.

Thanks a lot cyiangou

The vb code work very well.