Workbooks.open using wildcard for folder name

Rafow701 · Aug 26th 2020

Help! I have a macro where I am opening an excel workbook from a network drive and coping the contents of a sheet to a sheet in my active workbook. Today I discovered that the folder name where that workbook will be located has the possibility of changing from time to time. This is because the folder name contains the revision level of the file (third line in example below).

Can I use a wildcard in my call out?

Currently my code is this:

Code

Dim wbkFrom, wbkTo As Workbook


Set wbkTo = ActiveWorkbook


Set wbkFrom = Workbooks.Open("R:\Masters\777\Software\7770239 Rev A")


wbkFrom.Sheets("Jigs").Range("A1:H500").Copy


wbkTo.Sheets("Jigs").Range("A1").PasteSpecial (xlPasteValues)

Display More

Thanks.

**royUK** · Aug 27th 2020

Welcome to Ozgrid, please take the time to read the Forum Rules so that you use the Forum correctly. I have added Code Tags this time.

You need to add a browse for file into the code. I doubt if Excel will find the folder any other way.

Rafow701 · Aug 27th 2020

Thanks for the update to the post. I am not sure how to do what you are suggesting. My current code is doing what I need if the file name stays as is, but it will change at some point, and I need to account for that.

**royUK** · Aug 27th 2020

Something like this

Code

Dim wbkTo As Workbook
Dim sFilter As String, sTitle As String, sFile As Variant


sFilter = "Excel Files (*.xl*),*.xl*"
sTitle = "Please Select an Excel File"
sFile = Application.GetOpenFilename(sFilter, , sTitle)


Set wbkTo = ThisWorkbook
If sFile = "False" Then
MsgBox "No file selected", vbCritical, "Cancelled"
Exit Sub
End If


If LCase(Mid(sFile, InStrRev(sFile, "."), 3)) <> ".xl" Then
MsgBox "Excel File not selected", vbCritical, , "Excel required"
Exit Sub
End If


Workbooks.Open Filename:=sFile
ActiveWorkbook.Sheets("Jigs").Range("A1:H500").Copy
wbkTo.Sheets("Jigs").Range("A1").PasteSpecial (xlPasteValues)

Display More

Rafow701 · Aug 27th 2020

Thanks Roy. I am looking to do this without the user having to input the file path.

Thought of other info that might be helpful:

The xlsx file will always be in the same folder name (R:\Masters\777\Software\7770238 REV ?) only the ? will change.

There will never be two 7770238 REV ? folders located in this directory. The 7770238 REV B folder will replace the 7770238 REV A.

The xlsx file within this folder will always be named the same regardless of the revision.

The xlsx file is the only file in this folder.

Workbooks.open using wildcard for folder name

Participate now!

Share