Application.GetOpenFilename error

[Greenfizz]

I'm having new problem with a macro that I have been using for several years. The macro allows me to import data from a prior year spreadsheet into the current version of the spreadsheet form. The problem is intermittent and only seems to occur in specific folders on my laptop.

MyFile = Application.GetOpenFilename()
If MyFile = False Then 'User clicked cancel
Exit Sub
End If

For some reason, the output is being changed from the actual directory. Any idea why the ? is appearing before the BAYCARE - ALL folder name?
Debug.Print MyFile
C:\Road Warrior\1 RSI Clients\1-F\?BAYCARE - ALL\St Joseph's Hospital North\CT\2018\St Joe N Siemens (64) 2018-10-16.xlsm

The correct path should be:
C:\Road Warrior\1 RSI Clients\1-F\‬BAYCARE - ALL\St Joseph's Hospital North\CT\2018\St Joe N Siemens (64) 2018-10-16.xlsm

I would appreciate any insight you might have.

Thank you

 

[Yongle]

Welcome to the forum

I do not understand why it is happening, but fix should be simple enough
Add a line to remove all question marks before opening the workbook

Untested ..

MyFile = Replace(MyFile, "?", "")

 

[Greenfizz]

Yongle, Thank you for the reply and the welcome.

I thought about implementing a similar fix to the problem that you have posed, but wanted to try and find the cause before implementing the fix. In other forums I have read similar instance of GetOpenFilename or Dir replacing spaces with ?. Were this to happen and I implement the ? removing code it would still error out in the future.

I'll try and determine the cause before implementing the fix to remove the question mark. It's quite odd that this only happens for certain folders and not for several others.

 

[Yongle]

I have read similar instance of GetOpenFilename or Dir replacing spaces with ?. Were this to happen and I implement the ? removing code it would still error out in the future.

It would be good to find out the root cause. but in the meantime .. here is more robust code that can handle your second worry

Sub MoreRobust()
    Dim MyFile As Variant, Test As String
    MyFile = Application.GetOpenFilename()
[COLOR=#006400]'did user select anything ?[/COLOR]
    If MyFile = False Then Exit Sub
[COLOR=#006400]'rremove question marks and then test existence of file[/COLOR]
    MyFile = Replace(MyFile, "?", "")
    On Error Resume Next
    Test = Dir(MyFile)
    On Error GoTo 0
[COLOR=#006400]'bail out if Dir fails to find file[/COLOR]
    If Test = "" Then
        MsgBox MyFile, vbExclamation, "The filepath does not exist"
        Exit Sub
    Else
        MsgBox "Found: " & vbCr & MyFile & vbCr & Test, vbOKOnly, "It's a lucky day ..."
    End If
End Sub

 

[Yongle]

An alternative method to browse to the file
- does it result in same problem ??

Sub AnotherWay()
    Dim MyFile As String
    With Application.FileDialog(msoFileDialogFilePicker)
            .InitialFileName = "C:\Folder\SubFolder\"      [COLOR=#006400] 'Set the initial path like this (optional)[/COLOR]
            .AllowMultiSelect = False
            .Filters.Add "Excel Files", "*.xls; *.xlsx; *.xlsm; *.xlsb", 1      [COLOR=#006400]'add filters to narrow down file choices (optional)[/COLOR]
            .Show
            If .SelectedItems.Count > 0 Then
                MyFile = .SelectedItems.Item(1)
            Else
                Exit Sub
            End If
    End With
    Workbooks.Open MyFile
End Sub

for info
https://docs.microsoft.com/en-us/office/vba/api/office.filedialog

 

[Greenfizz]

The saga continues...
So we had a staff meeting today and I had my colleagues try to use the form on their machines. Each of them had the same error "Run-time error '51': Bad file name or number. Checking the value for MyFile string it contained the erroneous question mark. Oddly enough, my boss was able to use the form without any issue. I have several other spreadsheets that use Application.GetOpenFilename as well and they are all having the same problem.

My colleagues that are having the problem are using Excel version 1909 along with myself. My boss is using version 1902 and everything is working fine using the file I originally posted. I was thinking it was a problem with the update, but I have other clients that are in the C:\Road Warrior\1 RSI Clients\1-F\ directory and they work fine using Application.GetOpenFilename. The problem is only with the ‬BAYCARE - ALL directory with those of us using version 1909. At this point I have no idea.

Regardless, I tried AnotherWay() suggested by Yongle. Unfortunately it resulted in the same error as Application.GetOpenFilename. Ugh.

And for the final kick to the nuts, the MoreRobust() provided by Yongle resulted in the question mark not being removed from the MyFile string. The subroutine ended with a message box displaying (you guessed it): C:\Road Warrior\1 RSI Clients\1-F\?BAYCARE - ALL\St Joseph's Hospital North\CT\2018\St Joe N Siemens (64) 2018-10-16.xlsm

Thus, whatever dark magic is placing the ? into the string in the first place is stronger than the MyFile = Replace(MyFile, "?","") line of code. I'm gobsmacked.

 

[Yongle]

It is not a question mark - it is an unprintable character manifesting itself as a question mark

Hopefully this method will work ..
Identify nuisance character's ascii number using the ASC function and remove it using Replace(MyFile,Chr(character number),"")

 

[Greenfizz]

Yongle, you are fantastic! So rather than trying to figure out the ascii number for the character and remove it using the VBA code, I simply deleted the current folder name and retyped they BAYCARE - ALL that it should be. This removed the unprintable character from the filename and solved the problem rather than treat the symptom. It turns out my boss' computer was not syncing with the rest of ours and the unknown character did not upload to his laptop.

It had never occurred to me that the ? was a representation of an unprintable ascii value.

 

[Yongle]

I am glad that your question was answered