Opening File From Listbox

[Octofez2]

Good Afternoon All,

The code below works, when the file is in the process of opening, it appears Excel is opening and closing all of the workbooks and then finally opens the correct one. It's not visually appealing. I am curious to know, if when a filename is selected if the below code can be tweaked to just open the specific workbook? I have tried several IF/THEN statements and did not have much luck. Thank you for having a look.

Private Sub btnOpenFile_Click()

        With Application
            .ScreenUpdating = False
            .EnableEvents = False
            .Calculation = xlCalculationManual
        End With
 
        Dim fileH As String
        Dim fileJ As String
        Dim fileMI As String
        Dim fileMT As String
        Dim fileO As String
        Dim fileP As String
     
        If lstVAFiles.ListIndex < 0 Then
            MsgBox ("You must select a file!"), , "User Error"
        Exit Sub
        End If
     
            fileH = lstVAFiles.List(lstVAFiles.ListIndex, 1) & lstVAFiles.List(lstVAFiles.ListIndex, 2)
            Workbooks.Open (fileH)
         
            fileJ = lstVAFiles.List(lstVAFiles.ListIndex, 1) & lstVAFiles.List(lstVAFiles.ListIndex, 2)
            Workbooks.Open (fileJ)
         
            fileMI = lstVAFiles.List(lstVAFiles.ListIndex, 1) & lstVAFiles.List(lstVAFiles.ListIndex, 2)
            Workbooks.Open (fileMI)
         
            fileMT = lstVAFiles.List(lstVAFiles.ListIndex, 1) & lstVAFiles.List(lstVAFiles.ListIndex, 2)
            Workbooks.Open (fileMT)
         
            fileO = lstVAFiles.List(lstVAFiles.ListIndex, 1) & lstVAFiles.List(lstVAFiles.ListIndex, 2)
            Workbooks.Open (fileO)
         
            fileP = lstVAFiles.List(lstVAFiles.ListIndex, 1) & lstVAFiles.List(lstVAFiles.ListIndex, 2)
            Workbooks.Open (fileP)
         
     
            'Workbooks.Open (fileH)
            'Workbooks.Open (fileJ)
            'Workbooks.Open (fileMI)
            'Workbooks.Open (fileMT)
            'Workbooks.Open (fileO)
            'Workbooks.Open (fileP)
     
            With Application
                .ScreenUpdating = True
                .EnableEvents = True
                .Calculation = xlCalculationAutomatic
            End With



End Sub

 

[Logit]

Have you tried the FileDialog ?

Option Explicit

Sub open_file()
'open a file from a specific directory

Dim open_directory As String
Dim myFile As String

open_directory = "C:\Users\My\Desktop\" ''set default directory to open to
ChDir open_directory
'open file prompt
Application.Dialogs(xlDialogOpen).Show

End Sub

 

[Octofez2]

I appreciate the reply. The code opens the correct spreadsheet. What seems to be happening is Excel is opening all six spreadsheets, automatically closes 5 of the incorrect sheets leaving the correct sheet open.

If it helps at all, the list box has column2 set with the folder path and column3 lists the file name. The files are in 6 different folders. This way I can hide the folder path and only display the file name in the list box. Also note this is an ActiveX control.

 

[Logit]

Can you post your entire workbook without confidential data ? Helps alot to see the whole thing.

 

[Octofez2]

I could not post the workbook, there is a lot of other code pertaining to other functions.

I did solve my issue. Putting in Exit Sub after the Workbooks.Open string fixed everything.

Thanks again!!

 

[Logit]

Glad to hear it. Cheers.

 

[Hui]

It looks like this code is missing pieces

I have highlighted in Red below

'===============

Private Sub btnOpenFile_Click()

With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

Dim fileH As String
Dim fileJ As String
Dim fileMI As String
Dim fileMT As String
Dim fileO As String
Dim fileP As String

If lstVAFiles.ListIndex < 0 Then
MsgBox ("You must select a file!"), , "User Error"
Exit Sub
End If
Select Case lstVAFiles.ListIndex
Case 0
fileH = lstVAFiles.List(lstVAFiles.ListIndex, 1) & lstVAFiles.List(lstVAFiles.ListIndex, 2)
Workbooks.Open (fileH)
Case 1
fileJ = lstVAFiles.List(lstVAFiles.ListIndex, 1) & lstVAFiles.List(lstVAFiles.ListIndex, 2)
Workbooks.Open (fileJ)
Case 2
fileMI = lstVAFiles.List(lstVAFiles.ListIndex, 1) & lstVAFiles.List(lstVAFiles.ListIndex, 2)
Workbooks.Open (fileMI)
Case 3
fileMT = lstVAFiles.List(lstVAFiles.ListIndex, 1) & lstVAFiles.List(lstVAFiles.ListIndex, 2)
Workbooks.Open (fileMT)
Case 4
fileO = lstVAFiles.List(lstVAFiles.ListIndex, 1) & lstVAFiles.List(lstVAFiles.ListIndex, 2)
Workbooks.Open (fileO)
Case 5
fileP = lstVAFiles.List(lstVAFiles.ListIndex, 1) & lstVAFiles.List(lstVAFiles.ListIndex, 2)
Workbooks.Open (fileP)
End Select

'Workbooks.Open (fileH) 'Workbooks.Open (fileJ) 'Workbooks.Open (fileMI) 'Workbooks.Open (fileMT) 'Workbooks.Open (fileO) 'Workbooks.Open (fileP)
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Sub

 

[Octofez2]

Perfect, that was it. Thank you Hui!

 

[Hui]

Make sure the top value in the Listbox is working ok

If it isn't you may have code that starts the Listbox at Index 1 instead of 0
If that is the case simply add 1 to all the lines Case 1 etc
so Case 0 will become Case 1 etc

 

[Octofez2]

Thanks again Hui. The index did start at 0 so everything works fine. Good to know for future reference.