Open Directory for user to choose a file

[andymalan]

Hello, I hope i can get some help from people whom i regard as super smart.
The code below (that i have managed to get from others) finds the username and computer name so that other users would be able to use the same code on their computers.
I had hoped that the code would also open the filename "Element List (whatever the user had named the file).xlxs"
(The filename will ALWAYS begin with"Element List".) if that cannot be done, then if the directory could open for the user to choose the file manually.
the code below opens the directory to Documents, leaving the user to stumble along the path to find the file.

Your help will be greatly appreciated.
many thanks in advance
Andy

Dim wkb2 As Workbook
Dim UsersName As String, strFileName As String

UsersName = Environ("USERNAME")

strFileName = "C:\Users" & UsersName & _
"\Desktop\Excalibur Winner\ExcaliburProPlus\_ExcaliburDataConfig\Element list - Export.xlsx"

If Not Dir(strFileName, vbDirectory) = vbNullString Then

Set wkb2 = Workbooks.Open(strFileName, False, False)







Else
MsgBox strFileName & Chr(10) & Chr(10) & Space(Len(strFileName) / 2) & "File Not Found", 48, "Not Found"

End If


FilterIndx = IIf(Val(Application.Version) < 12, 1, 2)




FileFilter = "Excel 2003 (*.xls),*.xls," & _
"Excel 2007 > (*.xlsx),*.xlsx," & _
"All Excel Files (*.xl*),*.xl*," & _
"All Files (*.*),*.*"




GetFileName = Application.GetOpenFilename(FileFilter, FilterIndx, "Select One File To Open")
If VarType(GetFileName) = vbBoolean Then GetFileName = CVErr(10)

 

[andymalan]

Dear AlphaFrog, the Configure data wb closes as described if I choose a wb " Element List **?**", but if I choose " Element List - Export" the macro runs to its conclusion.


????

 

[AlphaFrog]

    [color=darkblue]Dim[/color] wkb2 [color=darkblue]As[/color] Workbook, strFileName [color=darkblue]As[/color] [color=darkblue]String[/color], FileFilter [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    ChDir ThisWorkbook.Path
    
    FileFilter = "Excel 2003 (*.xls),*.xls," & _
                 "Excel 2007 > (*.xlsx),*.xlsx," & _
                 "All Excel Files (*.xl*),*.xl*," & _
                 "All Files (*.*),*.*"
    
    strFileName = Application.GetOpenFilename(FileFilter, 4, "Select One File To Open")
    [color=darkblue]If[/color] strFileName <> "False" [color=darkblue]Then[/color]
        [color=darkblue]Set[/color] wkb2 = Workbooks.Open(strFileName, [color=darkblue]False[/color], [color=darkblue]False[/color])
    [color=darkblue]Else[/color]
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color] [color=green]'User canceled[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    [color=green]'copy the customer name and address from the "Element List WorkSheet"[/color]
    Range("A9:A15").Copy
    [color=darkblue]With[/color] ThisWorkbook.Sheets("Cabinets")
        .Range("I3").PasteSpecial xlPasteAll
        Application.CutCopyMode = False
        .Range("I1").Value = "The Last Client that was configured was:"
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=green]'Close the Element List ****.xlsm[/color]
    [color=green]'wkb2.Close SaveChanges:=False[/color]

 

[andymalan]

Dear AlphaFrog,

Thank you very much for your help, I appreciate the time taken to help me, and also that I have learned something.

at my age the trick now is not to forget what I learned. hahaha

Keep well
best regards
Andy