use Wildcard in file name

[doriannjeshi]

Hello,

I need to create a macro that copies data from a workbook that the name will change and a fixed sufix i.e: 22034202_name.xls
how should I go with using the wildcard in the file name?

 

[jolivanes]

In your example, "22034202_" is a constant, "name" is a variable and ".xls" is a constant?

 

[doriannjeshi]

number is variable
name is constant
.xls is constant

 

[jolivanes]

Don't be so stingy. Supply information. I promise, I won't tell anybody!!!
Are both workbooks open? You could have 12 workbooks open and you need one particular one.
Is one open, with the code I assume, and the other one needs to be opened from a Folder?
It could even be possible that both workbooks are closed and you run the code from a third, although I don't think that that is what you are doing.

 

[doriannjeshi]

Thanks for helping,

this one 2034202_name.xls needs to be opened from a folder
Yes the one with the code is open and needs to copy cells from 2034202_name.xls
i.e copy from sheet1 of 2034202_name.xls paste to sheet2 of test.xlsm (the one with the code)
possible ?

 

[jolivanes]

Sub Like_So()
Dim fn As String
fn = Dir("C:\Folder Name\Sub Folder Name\" & "*" & "_name.xls")
Workbooks.Open "C:\Folder Name\Sub Folder Name\" & fn
End Sub

 

[doriannjeshi]

 

[jolivanes]

In Post #1 it is an ".xls" extension. In Post #7 it is an ".xlsx" extension.
I am sure you don't have a Folder named "Folder Name" and a Sub Folder inside that Folder named "Sub Folder Name"!!!!!
Change to required.

 

[doriannjeshi]

and to open two files same folder with this method ?
possible to give command to copy from opened file with wildcard name ?

 

[jolivanes]

It will open the first found file.

If you have not too many files, I would look at a UserForm with Listbox populated with filenames where you can select from.