locate and open worksheet

[andymalan]

Hello Everyone

the code below currently opens the worksheet "Element list - Export.xlsx".

Dim wkb2 As Workbook
Set wkb2 = Workbooks.Open("C:\Users\andym\Desktop\Excalibur Winner\ExcaliburProPlus\_ExcaliburDataConfig\Element list - Export.xlsx")

I need to run this code on other computers without having to modify the users computer-name every time.
please help me with some code to:

1. find the users' computer-name and replace andym(my computer-name) with the users' computer-name. If no computer-name then ignore computer-name.

2. Use wildcards to open Element list(it will always be called Element list) - Export.xlsx (apply wild card to whatever characters come after "element list") e.g. Element list(****).xlsx

your learned assistance will be greatly appreciated.

best regards
Andy

 

[dmt32]

Hi,
see if following helps

    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)

       'rest of code


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

Dave

 

[andymalan]

Hi Dave, thank you very much for your prompt reply,

the first question about finding user and user name works great, thanks a lot.

the 2nd part opens the file if it finds it, if it doesn't find the file, t gives a message box, so this does work. but it isnt what I am looking for.
perhaps the best way would be to open the directory "_ExcaliburDataConfig", and choose the file to open?

 

[dmt32]

Hi Dave, thank you very much for your prompt reply,

the first question about finding user and user name works great, thanks a lot.

the 2nd part opens the file if it finds it, if it doesn't find the file, t gives a message box, so this does work. but it isnt what I am looking for.
perhaps the best way would be to open the directory "_ExcaliburDataConfig", and choose the file to open?

Try this update

Sub andy()
    Dim wkb2 As Workbook
    Dim UsersName As String
    Dim varFileName As Variant
    
    UsersName = Environ("USERNAME")
    
    varFileName = "C:\Users\" & UsersName & _
    "\Desktop\Excalibur Winner\ExcaliburProPlus\_ExcaliburDataConfig\Element list - Export.xlsx"
    
    If Dir(varFileName, vbDirectory) = vbNullString Then
        varFileName = GetFileName
'cancel pressed
        If IsError(varFileName) Then Exit Sub
    End If
            
    Set wkb2 = Workbooks.Open(varFileName, False, False)


    
    'rest of code
    
End Sub

Add function to a STANDARD module

Function GetFileName() As Variant
    Dim sFileName As Variant
    Dim FileFilter As String
    Dim FilterIndx As Integer


    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)
End Function

Dave

 

[andymalan]

Hi Dave
Once again, thanks for the above code which works fine until I select the file to open and click "OK". Nothing happens except a screen flicker, file does not open.

Also, The window that opens to select the file opens in documents and not the folder I would prefer which is _ExcaliburDataConfig.

Thank you for taking the time to help me.

 

[dmt32]

Hi,
Ran the code on my local directory & file opened ok.


Try following updates to both codes

 Sub andy()
    Dim wkb2 As Workbook
    Dim UsersName As String, FilePath As String
    Dim varFileName As Variant
    
    UsersName = Environ("USERNAME")
    
    varFileName = "C:\Users\" & UsersName & _
    "\Desktop\Excalibur Winner\ExcaliburProPlus\_ExcaliburDataConfig\Element list - Export.xlsx"
    
    If Dir(varFileName, vbDirectory) = vbNullString Then
        FilePath = Left$(varFileName, InStrRev(varFileName, "\"))
        varFileName = GetFileName(FilePath)
        If IsError(varFileName) Then Exit Sub
    End If
            
    Set wkb2 = Workbooks.Open(varFileName, False, False)


    MsgBox wkb2.Name
    'rest of code
    
End Sub

For testing, I have inserted a msgbox which should display showing the filename you have opened.

Function GetFileName(Optional ByVal FilePath As Variant) As Variant
    Dim sFileName As Variant
    Dim FileFilter As String
    Dim FilterIndx As Integer


    FilterIndx = IIf(Val(Application.Version) < 12, 1, 2)
    
    If Not IsMissing(FilePath) Then ChDir FilePath


    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)
End Function

There is no path parameter you can use in the GetOpenFileName method but we can use Chdir which hopefully, will do what you want. I have added this as a parameter to the function where you specify Folder you want to start in when dialog displayed.

Dave