Wildcard with the workbooks.open function

M

Mihael

Board Regular
Joined
Mar 18, 2016
Messages
170
Hi there,

I am using the wildcard in the filename for the workbooks.open function. In Excel 2013 it works fine, but in Excel 2003 (which we also use) then it does not work. Should this version be a problem?

The code I use is:

DataWkBkName1 = prodNumberIn & "_ac_rev-?_DTS Inner 12 inch+angle_" & batchNumberIn & "*.xls"
FolderOne = "J:\Kwaliteit Helmond\FINAL INSPECTION REPORTS" & Year & "\0_WC_340_Duramax\DuraMax_144461"

DataWBkPathAndName = FolderOne & DataWkBkName1

Workbooks.Open DataWBkPathAndName
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
As a far as I know you can't use wildcards in Workbooks.Open in any version of Excel.

Is that all the code you have?

PS You could use a wildcard with the Dir function to get the full name of a workbook.
 
Upvote 0
It works with Excel 2013

This is the code:

Code: 
Sub AssemblyDataRetrieve()
 Dim DataWkBk As Workbook
 Dim DataWkBkName1 As String
 Dim DataWkBkName2 As String
 Dim DataWkBkName1_1 As String
 Dim DataWkBkName2_1 As String
 Dim FolderOne As String
 Dim FolderTwo As String
 Dim FolderThree As String
 Dim FolderFour As String
 Dim SrcShtName As String
 Dim SrchVal As String
 Dim TargetShtName As String
 Dim DataWBkPathAndName As String
 Dim DataWBkPathAndName2 As String
 Dim prodNumberIn As String
 Dim batchNumberIn As String
 Dim prodNumberOut As String
 Dim batchNumberOut As String
 Dim Year As String
 Dim Year2 As String
 Dim Awb As String
 Dim cellSplit As String
 Dim NmCheck As String
 Dim NwWbk As String
 Dim check1 As String
 Dim check2 As String
 
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
check1 = 0
check2 = 0
 Awb = ActiveWorkbook.Name
 Sheets("Report").Activate
cellSplit = Range("G8").Value
If cellSplit = "" Then
    Range("F8").Select
    Selection.TextToColumns Destination:=Range("F8"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End If
If LCase(Range("F8").Value) = "inner" Or LCase(Range("F8").Value) = "outer" Then
    batchNumberIn = Range("G8").Value
    batchNumberOut = Range("I8").Value
Else
    batchNumberIn = Range("F8").Value
    batchNumberOut = Range("G8").Value
End If
    
    prodNumberIn = 10601331
    prodNumberOut = 10601329
    Year = Right(Range("D5").Value, 4)
    Year2 = Year - 1
    DataWkBkName1 = prodNumberIn & "_ac_rev-?_DTS Inner 12 inch+angle_" & batchNumberIn & "*.xls"
    DataWkBkName1_1 = prodNumberIn & "_ac_rev-?_DTS Inner 12 inch+angle_" & batchNumberOut & "*.xls"
    DataWkBkName2 = prodNumberOut & "_ac_rev-?_DTS_Outer 12 inch+angle_" & batchNumberOut & "*.xls"
    DataWkBkName2_1 = prodNumberOut & "_ac_rev-?_DTS_Outer 12 inch+angle_" & batchNumberIn & "*.xls"
    FolderOne = "J:\Kwaliteit Helmond\FINAL INSPECTION REPORTS\" & Year & "\0_WC_340_Duramax\DuraMax_144461\"
    FolderTwo = "J:\Kwaliteit Helmond\FINAL INSPECTION REPORTS\" & Year & "\0_WC_340_Duramax\DuraMax_144751\"
    FolderThree = "J:\Kwaliteit Helmond\FINAL INSPECTION REPORTS\" & Year2 & "\0_WC_340_Duramax\DuraMax_144461\"
    FolderFour = "J:\Kwaliteit Helmond\FINAL INSPECTION REPORTS\" & Year2 & "\0_WC_340_Duramax\DuraMax_144751\"
    SrcShtName = "Report"
    TargetShtName = "Report"
    
    DataWBkPathAndName = FolderOne & DataWkBkName1
    If FileExists(DataWBkPathAndName) Then
        Workbooks.Open DataWBkPathAndName
        Sheets(SrcShtName).Activate
        NwWbk = ActiveWorkbook.Name
        Sheets("Report").Copy After:=Workbooks(Awb).Sheets(5)
        Workbooks(Awb).Activate
        Sheets("Report (2)").Activate
        NmCheck = Left(Range("B8").Value, 9)
        Sheets("Report (2)").Name = NmCheck
        Workbooks(NwWbk).Close
        check1 = 1
        GoTo EINDE1
    End If
    
    DataWBkPathAndName = FolderOne & DataWkBkName1_1
    
    If FileExists(DataWBkPathAndName) Then
        Workbooks.Open DataWBkPathAndName
        Sheets(SrcShtName).Activate
        NwWbk = ActiveWorkbook.Name
        Sheets("Report").Copy After:=Workbooks(Awb).Sheets(5)
        Workbooks(Awb).Activate
        Sheets("Report (2)").Activate
        NmCheck = Left(Range("B8").Value, 9)
        Sheets("Report (2)").Name = NmCheck
        Workbooks(NwWbk).Close
        check1 = 1
        GoTo EINDE1
     End If
           
    DataWBkPathAndName = FolderTwo & DataWkBkName1
    If FileExists(DataWBkPathAndName) Then
        Workbooks.Open DataWBkPathAndName
        Sheets(SrcShtName).Activate
        NwWbk = ActiveWorkbook.Name
        Sheets("Report").Copy After:=Workbooks(Awb).Sheets(5)
        Workbooks(Awb).Activate
        Sheets("Report (2)").Activate
        NmCheck = Left(Range("B8").Value, 9)
        Sheets("Report (2)").Name = NmCheck
        Workbooks(NwWbk).Close
        check1 = 1
        GoTo EINDE1
    End If
    
    DataWBkPathAndName = FolderTwo & DataWkBkName1_1
    
    If FileExists(DataWBkPathAndName) Then
        Workbooks.Open DataWBkPathAndName
        Sheets(SrcShtName).Activate
        NwWbk = ActiveWorkbook.Name
        Sheets("Report").Copy After:=Workbooks(Awb).Sheets(5)
        Workbooks(Awb).Activate
        Sheets("Report (2)").Activate
        NmCheck = Left(Range("B8").Value, 9)
        Sheets("Report (2)").Name = NmCheck
        Workbooks(NwWbk).Close
        check1 = 1
        GoTo EINDE1
    End If
    
    DataWBkPathAndName = FolderThree & DataWkBkName1_1
    If FileExists(DataWBkPathAndName) Then
        Workbooks.Open DataWBkPathAndName
        Sheets(SrcShtName).Activate
        NwWbk = ActiveWorkbook.Name
        Sheets("Report").Copy After:=Workbooks(Awb).Sheets(5)
        Workbooks(Awb).Activate
        Sheets("Report (2)").Activate
        NmCheck = Left(Range("B8").Value, 9)
        Sheets("Report (2)").Name = NmCheck
        Workbooks(NwWbk).Close
        check1 = 1
        GoTo EINDE1
    End If
    
    DataWBkPathAndName = FolderThree & DataWkBkName1_1
    
    If FileExists(DataWBkPathAndName) Then
        Workbooks.Open DataWBkPathAndName
        Sheets(SrcShtName).Activate
        NwWbk = ActiveWorkbook.Name
        Sheets("Report").Copy After:=Workbooks(Awb).Sheets(5)
        Workbooks(Awb).Activate
        Sheets("Report (2)").Activate
        NmCheck = Left(Range("B8").Value, 9)
        Sheets("Report (2)").Name = NmCheck
        Workbooks(NwWbk).Close
        check1 = 1
        GoTo EINDE1
    End If
           
    DataWBkPathAndName = FolderFour & DataWkBkName1
    If FileExists(DataWBkPathAndName) Then
        Workbooks.Open DataWBkPathAndName
        Sheets(SrcShtName).Activate
        NwWbk = ActiveWorkbook.Name
        Sheets("Report").Copy After:=Workbooks(Awb).Sheets(5)
        Workbooks(Awb).Activate
        Sheets("Report (2)").Activate
        NmCheck = Left(Range("B8").Value, 9)
        Sheets("Report (2)").Name = NmCheck
        Workbooks(NwWbk).Close
        check1 = 1
        GoTo EINDE1
    End If
    
    DataWBkPathAndName = FolderFour & DataWkBkName1_1
    
    If FileExists(DataWBkPathAndName) Then
        Workbooks.Open DataWBkPathAndName
        Sheets(SrcShtName).Activate
        NwWbk = ActiveWorkbook.Name
        Sheets("Report").Copy After:=Workbooks(Awb).Sheets(5)
        Workbooks(Awb).Activate
        Sheets("Report (2)").Activate
        NmCheck = Left(Range("B8").Value, 9)
        Sheets("Report (2)").Name = NmCheck
        Workbooks(NwWbk).Close
        check1 = 1
        GoTo EINDE1
    End If
  
EINDE1:
    DataWBkPathAndName2 = FolderOne & DataWkBkName2
    If FileExists(DataWBkPathAndName2) Then
        Workbooks.Open DataWBkPathAndName2
        Sheets(SrcShtName).Activate
        NwWbk = ActiveWorkbook.Name
        Sheets("Report").Copy After:=Workbooks(Awb).Sheets(5)
        Workbooks(Awb).Activate
        Sheets("Report (2)").Activate
        NmCheck = Left(Range("B8").Value, 9)
        Sheets("Report (2)").Name = NmCheck
        Workbooks(NwWbk).Close
        check2 = 1
        GoTo EINDE2
    End If
           
    DataWBkPathAndName2 = FolderTwo & DataWkBkName2
    If FileExists(DataWBkPathAndName2) Then
        Workbooks.Open DataWBkPathAndName2
        Sheets(SrcShtName).Activate
        NwWbk = ActiveWorkbook.Name
        Sheets("Report").Copy After:=Workbooks(Awb).Sheets(5)
        Workbooks(Awb).Activate
        Sheets("Report (2)").Activate
        NmCheck = Left(Range("B8").Value, 9)
        Sheets("Report (2)").Name = NmCheck
        Workbooks(NwWbk).Close
        check2 = 1
        GoTo EINDE2
    End If
    
    DataWBkPathAndName2 = FolderThree & DataWkBkName2
    If FileExists(DataWBkPathAndName2) Then
        Workbooks.Open DataWBkPathAndName2
        Sheets(SrcShtName).Activate
        NwWbk = ActiveWorkbook.Name
        Sheets("Report").Copy After:=Workbooks(Awb).Sheets(5)
        Workbooks(Awb).Activate
        Sheets("Report (2)").Activate
        NmCheck = Left(Range("B8").Value, 9)
        Sheets("Report (2)").Name = NmCheck
        Workbooks(NwWbk).Close
        check2 = 1
        GoTo EINDE2
    End If
           
    DataWBkPathAndName2 = FolderFour & DataWkBkName2
    If FileExists(DataWBkPathAndName2) Then
        Workbooks.Open DataWBkPathAndName2
        Sheets(SrcShtName).Activate
        NwWbk = ActiveWorkbook.Name
        Sheets("Report").Copy After:=Workbooks(Awb).Sheets(5)
        Workbooks(Awb).Activate
        Sheets("Report (2)").Activate
        NmCheck = Left(Range("B8").Value, 9)
        Sheets("Report (2)").Name = NmCheck
        Workbooks(NwWbk).Close
        check2 = 1
        GoTo EINDE2
    End If
    
    DataWBkPathAndName2 = FolderOne & DataWkBkName2_1
    If FileExists(DataWBkPathAndName2) Then
        Workbooks.Open DataWBkPathAndName2
        Sheets(SrcShtName).Activate
        NwWbk = ActiveWorkbook.Name
        Sheets("Report").Copy After:=Workbooks(Awb).Sheets(5)
        Workbooks(Awb).Activate
        Sheets("Report (2)").Activate
        NmCheck = Left(Range("B8").Value, 9)
        Sheets("Report (2)").Name = NmCheck
        Workbooks(NwWbk).Close
        check2 = 1
        GoTo EINDE2
    End If
           
    DataWBkPathAndName2 = FolderTwo & DataWkBkName2_1
    If FileExists(DataWBkPathAndName2) Then
        Workbooks.Open DataWBkPathAndName2
        Sheets(SrcShtName).Activate
        NwWbk = ActiveWorkbook.Name
        Sheets("Report").Copy After:=Workbooks(Awb).Sheets(5)
        Workbooks(Awb).Activate
        Sheets("Report (2)").Activate
        NmCheck = Left(Range("B8").Value, 9)
        Sheets("Report (2)").Name = NmCheck
        Workbooks(NwWbk).Close
        check2 = 1
        GoTo EINDE2
    End If
    
    DataWBkPathAndName2 = FolderThree & DataWkBkName2_1
    If FileExists(DataWBkPathAndName2) Then
        Workbooks.Open DataWBkPathAndName2
        Sheets(SrcShtName).Activate
        NwWbk = ActiveWorkbook.Name
        Sheets("Report").Copy After:=Workbooks(Awb).Sheets(5)
        Workbooks(Awb).Activate
        Sheets("Report (2)").Activate
        NmCheck = Left(Range("B8").Value, 9)
        Sheets("Report (2)").Name = NmCheck
        Workbooks(NwWbk).Close
        check2 = 1
        GoTo EINDE2
    End If
           
    DataWBkPathAndName2 = FolderFour & DataWkBkName2_1
    If FileExists(DataWBkPathAndName2) Then
        Workbooks.Open DataWBkPathAndName2
        Sheets(SrcShtName).Activate
        NwWbk = ActiveWorkbook.Name
        Sheets("Report").Copy After:=Workbooks(Awb).Sheets(5)
        Workbooks(Awb).Activate
        Sheets("Report (2)").Activate
        NmCheck = Left(Range("B8").Value, 9)
        Sheets("Report (2)").Name = NmCheck
        Workbooks(NwWbk).Close
        check2 = 1
        GoTo EINDE2
    End If
    
EINDE2:
If check1 = 1 Then
Sheets(6).Activate
Awb = ActiveSheet.Name
End If
If check2 = 1 Then
Sheets(7).Activate
NmCheck = ActiveSheet.Name
End If
If check = 1 & check2 = 1 Then
If Awb >= NmCheck Then
Sheets(7).Move Before:=Sheets(6)
End If
End If
If check1 = 1 Then
Sheets(6).Activate
Range("17:17,22:34,36:36,43:45").Copy
Sheets("3001120").Select
Rows("42:42").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A42:J59").Select
Selection.Cut Destination:=Range("B42:K59")
End If
If check2 = 1 Then
Sheets(7).Activate
Range("15:15,16:16,18:24,34:36").Copy
Sheets("3001120").Select
Rows("62:62").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A62:J74").Select
Selection.Cut Destination:=Range("B62:K74")
End If
    
Sheets("Report").Activate
If LCase(Range("F8").Value) = "inner" Or LCase(Range("F8").Value) = "outer" Then
batchNumberIn = Range("G8").Value
batchNumberOut = Range("I8").Value
Range("F8:I8").ClearContents
Range("F8") = batchNumberIn & " / " & batchNumberOut
End If
If check1 = 1 & check2 = 1 Then
DoEvents
Sheets("3001120").Activate
MsgBox "Controleer of de data van de Inner en Outer in de actieve sheet onder rij 41 en rij 61 aanwezig is. Indien er geen data aanwezig is, kan dit het volgende betekenen: 1)Geen meting aanwezig van de Inner/Outer 2) Het batchnummer is niet goed ingevuld.      De data moet nadat er op Ok gedrukt wordt te zien zijn"
End If
If check1 = 0 Or check2 = 0 Then
Sheets("3001120").Activate
MsgBox "Alleen de Inner of de Outer is gevonden en in dit rapport geplaatst. Oorzaak kan zijn dat het batchnummer niet goed ingevuld was, of dat de meting van de inner of outer niet dit jaar of het jaar ervoor gemeten was."
End If
ActiveWorkbook.Save
End Sub

The FileExistst function:

Code: 
Function FileExists(FileName As String) As Boolean
 FileExists = (Dir(FileName) > "")
End Function
 
Last edited:
Upvote 0
Has the code always used paths like '"J:\Kwaliteit Helmond\FINAL INSPECTION REPORTS\'?
 
Upvote 0
Like I said as far as I know it isn't possible to use wildcards when using Workbooks.Open but that isn't quite right.

Apparently when using UNC paths you can use wildcards.

So, I was wondering if in the past rather than using a path like 'J:\Kwaliteit Helmond\FINAL INSPECTION REPORTS\', which appears to refer to a mapped drive J:, you were using UNC paths.
 
Upvote 0
I cant recall that I ever used UNC paths. I tried my macro at several pc's with Excel 2013 and it worked, however the two pc's with 2003 version wont work. It says that it cannot find the file, but in the function fileExists it does find it
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
How to automatically change a Module code in multiple protected Visual Basic projects
Replies
1
Views
787
RoryA
RoryA
Tim_D
  • Question
VBA Structure Best Practices and Code Review
2
Replies
13
Views
4K
dmt32
D
B
  • Solved
Workbook.Open silently fails to function, execution continues - Totally baffled
Replies
4
Views
3K
BCVolkert
B
L
  • Question
workbooks.open - with or without ( )
Replies
3
Views
1K
lezawang
L
S
  • Question
2 Part question: Using Like & refining code
Replies
5
Views
514
Fluff
Fluff

Forum statistics

Threads
1,214,613
Messages
6,120,515
Members
448,968
Latest member
Ajax40

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top