Weird File Open Behavior After Windows 11 Upgrade

[Joe4]

I wrote some VBA code years ago for work that opens a bunch of files and runs some comparisons. The files have the same prefix every week, but different time stamps.
So this is the code the VBA code uses to open the files (first file):

    Dim fName As String
    fName = "C:\TEMP\Macros\Test\CMDB Server Report_*.xlsx"
    Workbooks.Open Filename:=fName

(I should note, that there will only ever be one file in the folder matching the desired prefix)

This has worked for them for years. Now, they have some updates they want me to make. When I try to run this code (which works for them) on my computer, I get the following error:

If I run this version of the code, with the exact file name, it works as expected.

    Dim fName As String
    fName = "C:\TEMP\Macros\Test\CMDB Server Report_20230901_0637.xlsx"
    Workbooks.Open Filename:=fName

however, since the date/time stamp changes every week, I cannot do that.

I don't understand why the original code is no longer working on my computer. The only thing of note I can think of is I upgraded to Windows 11 recently.
Perhaps some system setting changed, messing things up? Anyone have any ideas?

 

[6StringJazzer]

That does not work for me on Windows 10 and I don't know how it ever worked, on any system. I don't think Workbooks.Open supports wildcards.

Here is a revision that will work anywhere:

    Dim fName As String, dName As String
    dName = "C:\TEMP\Macros\Test\CMDB Server Report_*.xlsx"
    fName = Dir(dName)
    If fName = "" Then
      MsgBox dName & " not found"
    Else
      Workbooks.Open fileName:=fName
    End If

 

[Joe4]

Thanks for the reply.

Yeah, it has been working since 2019, so don't really have an answer for that!

When I test your code on my simple sample, it works perfectly. But when I incorporate it into my existing VBA code project, I still get that error message.
Very odd! I will need to dig a little deeper and see if I can tell what the issue is.

I wonder if I might have a bit of workbook corruption. I may try creating a new workbook, and copying my VBA code in to that and see if that works.
I will post back my results.

 

[JEC]

Agreed with 6StringJazzer, wildcard directly into a workbook.open should not have worked

 

[RoryA]

Dir only returns the file name, not path, so you should include the path in the Workbooks.Open with fName

 

[Joe4]

Dir onlly returns the file name, not path, so you should include the path in the Workbooks.Open with fName

Yes, I need to add the folder name back in to get it to work, i.e.

    fName = Dir(dataFile)
    If fName = "" Then
      MsgBox dataFile & " not found"
      Exit Sub
    Else
      Workbooks.Open Filename:=dataFolder & fName
    End If

Agreed with 6StringJazzer, wildcard directly into a workbook.open should not have worked

Why it used to work, I am not sure. But it did. And it still seems to work with my text files that I am importing.
Basically, I have one main procedure that is calling a bunch of other procedures and passing them the file name values.

Here is how one that opens a CSV file looks:

Line of code in the Main procedure that calls the other procedure (in a different module):

    Call BigFixFile(flBigFixFile)

Start of my BigFileFile procedure:

Public Sub BigFixFile(dataFile As String)
'   This is the module to import the Big Fix file
'       dataFile = file name structure of files to import

    Dim wbData As Workbook
    Dim lr As Long
    Dim cell As Range
    Dim rng As Range
    Dim lc As Long
    Dim c As Long
    Dim errMsg As String
    
    Application.ScreenUpdating = True
    
    MsgBox dataFile

'   Open data file & capture in workbook variable
    Workbooks.OpenText Filename:=dataFile, _
        Origin:=65001, startRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
        TrailingMinusNumbers:=True

'   Set data file
    Set wbData = ActiveWorkbook

Here is what the MsgBox returns:

You can clearly see the wildard in there.
The data file is actually named "report-33.csv".

When I run the above code, it successfully opens this "report-33.csv" file.

 

[JEC]

And what if you have a report called "Report-34.csv" too?

 

[Joe4]

And what if you have a report called "Report-34.csv" too?

There is only one file of each type in the folder. It gets cleaned out every week.

I just thought it was odd that it works, since it has a wildcard.

 

[JEC]

Maybe that is why it works. Just curious, could you try it with one more file, named as "Report-34.csv"

 

[Joe4]

Maybe that is why it works. Just curious, could you try it with one more file, named as "Report-34.csv"

Interestingly, it just did the first one it found and ignored the other.
But it did do it!