Use a conditional "instr" function in Excel VBA to NOT add an item to a list

Question

I am trying to populate a list box with the open files. The whole procedure will export the selected sheets from the existing file to another workbook and save it with a date time stamp appended into the same directory. It builds this export name and file from items in list boxes on a form.

The macro will be running from Personal.XLSB; the problem is that this becomes the active workbook and the exported file inherits the path and file name from it. I want to build the export name by letting the use pick a file name from the list box BUT I don't want the Personal.XLSB to show in the list, further, once the user picks the file name (from the list of open files) I want to make that file the active file. I have spent three hours with many variations INSTR (using numbers or just text) and even text comparison, OR USING "PERSONAL.xlsb" in the "instr" below, but I cant get it to work at all. Ever have one of this days?????

Any insights anyone can give?

CODE FOLLOWS:

' Populate the open file name list box excluding Personal.xlsb

Dim wbOpen As Workbook
Dim wbopenText As String

ListBoxOpenFiles.Clear
    For Each wbOpen In Workbooks
    wbopenText = wbOpen.Name
    If InStr(1, wbopenText, "Per") = 0 Then
        ListBoxOpenFiles.AddItem wbOpen.Name    ' add the name to the list
        MsgBox wbOpen.Name & "Added to list"
    End If
    
    If InStr(1, wbopenText, "Per") = 1 Then ' If "Personal" is NOT found skip
    
    End If                                  'Loop again
Next
' next step - make the existing file the active workbook

'still to do

[This may be useful](https://stackoverflow.com/questions/2813925/how-to-get-the-path-of-current-worksheet-in-vba) — cybernetic.nomad, Nov 26 '21 at 18:27

score 0 · Answer 1 · answered Nov 26 '21 at 18:45

All of this should work (I am a big fan of intermediate variables, but I think the variable wbopenText is not needed in this case):

If StrComp("ABC", "personal.xlsb", vbTextCompare) = 0 Then
    ListBoxOpenFiles.AddItem wbOpen.Name 
End If

If wb.Name <> "PERSONAL.XLSB" Then
   ListBoxOpenFiles.AddItem wbOpen.Name
End If

If InStr(wb.Name, "PER") = 0 Then
    ' Dangerous as it would also skip "MySuperWorkbook"
    ListBoxOpenFiles.AddItem wbOpen.Name
End If

If InStr(1, wb.Name, "per", vbTextCompare) = 0 Then
    ' Also dangerous as it would also skip "MySuperWorkbook"
    ListBoxOpenFiles.AddItem wbOpen.Name
End If

Your second If is not needed (except you want to do something special with the Personal workbook).

See https://stackoverflow.com/a/45216693/7599798 for some clarification.

To tell you how to get the selected item from the ListBox, you need to tell us where the listbox is located: On a sheet? On a Userform? Assuming you are able to get the selected name by your own and it is put into variable SelectedWorkbook

Dim wb As workbook
set wb = workbooks(SelectedWorkbook) 
wb.Activate
Debug.Print wb.FullName

Thank you. I had tried many variations, including some that you had suggested, then penny dropeed. My file name was "PERSONAL.xlsb" To get around this I used: — Terry McC, Nov 29 '21 at 15:57

score 0 · Answer 2 · answered Nov 29 '21 at 16:01

Thank you. I tried many variations on this including the ones you had sent. Then the penny dropped, my file name was "PERSONAL.xlsb" I changed the code to:

NEW CODE FOLLOWS

'Populate the open file name list box excluding Personal.xlsb Dim wbOpen As Workbook Dim wbopenUcase As String

ListBoxOpenFiles.Clear
    For Each wbOpen In Workbooks
    wbopenUcase = UCase(wbOpen.Name)                    ' convert name to uppercase just in case Personal XLSB has different cases
  
        If wbopenUcase <> "PERSONAL.XLSB" Then
            ListBoxOpenFiles.AddItem wbOpen.Name            ' add the name to the list
        End If                                              'Loop again
    Next

Use a conditional "instr" function in Excel VBA to NOT add an item to a list

2 Answers2