Adding Userform Dropdown List items from names of subfolders on network drive

Status
Not open for further replies.
R

reubendayal

Senior Member
Outlook version
Outlook 365 64 bit
Email Account
Office 365 Exchange
Hi All,

I am having difficulties making this code work.

What I am trying to achieve is to add a network drive's subfolder names in a Userform's Dropdown List. Unsure what I am doing wrong. Please help.

The code has been taken from here and adjusted to my requirement. - Find and List all Files and Folders in a Directory - VBA and VB.Net Tutorials, Education and Programming Services

Code: 
Sub



Dim employer As String



Dim objFSO As Object

Dim objFolder As Object

Dim objSubFolder As Object





'Create an instance of the FileSystemObject

Set objFSO = CreateObject("Scripting.FileSystemObject")

'Get the folder object

Set objFolder = objFSO.GetFolder("T:\Network Drive\Customers\")

i = 1

'loops through each file in the directory and prints their names and path

For Each objSubFolder In objFolder.SubFolders

'    'print folder name



Debug.Print objSubFolder.Name



    employer = objSubFolder.Name(i)

    cbEmployer.AddItem employer

    i = i + 1

    employer = employer & vbCrLf

    Next objSubFolder



End Sub

Thanks so much!
 
Hi Diane,

Thank you for your reply.

I've tried to change my code based on that link. And unfortunately, the code doesnt work for me or what I am trying to achieve. I am only more confused now.

My goal is to populate a dropdown list on a userform in outlook. And I aim to do this either by listing the folder names using the FileSystemObject or by pulling the contents from a column in a excel sheet on the network drive.

Will be grateful if you can help suggest what can be done.

Thank you.
 
Ah... now I understand. I thought you needed to get the folders into a list.

It will be easier to read a column, preferably in a CSV file (it won't need Excel to open).

I have a code sample in this article but there is only 1 column. I'll need to get the code to read a specific column.
www.slipstick.com

Outlook VBA: Use a Text File to Populate a ListBox

This code sample demonstrates how to use the contents of a text file to populate a ListBox. This works for ComboBoxes and TextBoxes. A client adds keywords to message subjects so it's easier to find the messages later but had a problem: if he misspelled a keyword or used a slightly different...
www.slipstick.com www.slipstick.com

Code: 
Private Sub UserForm_Initialize()
  Dim fn As String, ff As Integer, txt As String
    fn = "C:\Users\drcp\Documents\keywords.txt" '< --- .txt file path
    txt = Space(FileLen(fn))
    ff = FreeFile
    Open fn For Binary As #ff
    Get #ff, , txt
    Close #ff

 Dim myArray() As String
  'Use Split function to return a zero based one dimensional array.
  myArray = Split(txt, vbCrLf)
  'Use .List method to populate listbox.
  ListBox1.List = myArray
lbl_Exit:
  Exit Sub

End Sub
 
Thanks so much! This may just be the solution. will check it out and come back to you.
 
Hi again Diane,

I could learn something new today with this.

I got my code working with this. And as you said, it only works if the data is in one column. But the moment I try with more columns, I have a problem.

I will later need this code to pull the data that is in the corresponding columns. How does one do that.. I've never tried binary related codes earlier.

thank you.
 
I think there are other problems as well. As I am in Denmark we use 3 special characters. This seems to be an issue when listing the data in to the combo box. As words like "Øst" become "øst". Do you know another way of how this can be achieved? Else, I will need to find another way to read from the Excel file itself. As I wrote earlier, I will need to pull data in subsequent rows of the selected row.

Thanks for your answer in advance!
 
I am trying to build on the excel file approach. The excel file has the names of the subfolders on a fixed location on the network drive. And the each row has the relevant data I need to use later in the code.
But I've tried a lot and have now rewritten the code after referring to several examples online. And have come up with this code, but it simply wont work.

Any suggestions please?

Code: 
Public Sub UserForm_Initialize()

Dim Caseloc As String

Dim xlApp As Object
Dim xlWb As Object
Dim xlsheet As Object
Dim rng As Excel.Range
Dim cRows As Long
Dim I As Long


Caseloc = "C:\Users\rdayal\OneDrive(O365D)\Documents\Templates\Link To Company Folders-RD2.xlsx"
        On Error Resume Next
         Set xlApp = GetObject(, "Excel.Application")
         If Err <> 0 Then

            'Application.StatusBar = "Please wait while Excel source is opened ... "
            Set xlApp = CreateObject("Excel.Application")
            CopyStarted = True
         End If
         On Error GoTo 0
         'Open the workbook to input the data
         Set xlWb = xlApp.Workbooks.Open(Caseloc) ', , , , "Immigration1"

         Set xlsheet = xlWb.Sheets("Sheet1")
        
'         rng = xlsheet.cells(2, A10").Value
        
        cRows = xlsheet.Range("A" & xlsheet.Rows.Count).End(xlUp).Row
        cRows = cRows + 1

        With Me.cbEmployer
          For I = 2 To cRows
            
            .AddItem xlsheet.Range("A" & xlsheet.Rows).Cells(I, 1)

          Next I
        End With
        

        '**close sheet****
'        xlWb.Save
        xlWb.Close 1

    If CopyStarted Then
        xlApp.Quit
    End If


Set xlWb = Nothing
Set xlApp = Nothing
Set xlsheet = Nothing

End Sub
 
reubendayal said:
I think there are other problems as well. As I am in Denmark we use 3 special characters. This seems to be an issue when listing the data in to the combo box. As words like "Øst" become "øst". Do you know another way of how this can be achieved? Else, I will need to find another way to read from the Excel file itself. As I wrote earlier, I will need to pull data in subsequent rows of the selected row.

Thanks for your answer in advance!
Click to expand...
The controls use ANSI AFAIK, so its probably not going to show the correct value. ChrW codes *might* work, but making the conversion will be a PITA.
 
reubendayal said:
I am trying to build on the excel file approach. The excel file has the names of the subfolders on a fixed location on the network drive. And the each row has the relevant data I need to use later in the code.
But I've tried a lot and have now rewritten the code after referring to several examples online. And have come up with this code, but it simply wont work.

Any suggestions please?

Code: 
Public Sub UserForm_Initialize()

Dim Caseloc As String

Dim xlApp As Object
Dim xlWb As Object
Dim xlsheet As Object
Dim rng As Excel.Range
Dim cRows As Long
Dim I As Long


Caseloc = "C:\Users\rdayal\OneDrive(O365D)\Documents\Templates\Link To Company Folders-RD2.xlsx"
        On Error Resume Next
         Set xlApp = GetObject(, "Excel.Application")
         If Err <> 0 Then

            'Application.StatusBar = "Please wait while Excel source is opened ... "
            Set xlApp = CreateObject("Excel.Application")
            CopyStarted = True
         End If
         On Error GoTo 0
         'Open the workbook to input the data
         Set xlWb = xlApp.Workbooks.Open(Caseloc) ', , , , "Immigration1"

         Set xlsheet = xlWb.Sheets("Sheet1")
       
'         rng = xlsheet.cells(2, A10").Value
       
        cRows = xlsheet.Range("A" & xlsheet.Rows.Count).End(xlUp).Row
        cRows = cRows + 1

        With Me.cbEmployer
          For I = 2 To cRows
           
            .AddItem xlsheet.Range("A" & xlsheet.Rows).Cells(I, 1)

          Next I
        End With
       

        '**close sheet****
'        xlWb.Save
        xlWb.Close 1

    If CopyStarted Then
        xlApp.Quit
    End If


Set xlWb = Nothing
Set xlApp = Nothing
Set xlsheet = Nothing

End Sub
Click to expand...
Any error messages? Does it stop on a specific line?
 
Diane Poremsky said:
Any error messages? Does it stop on a specific line?
Click to expand...
Yes it does. All through the options I kept receiving different error messages. For now if I simply run the Userform initialize code it says Runtime error: 7 Out of memory.

Although the Userform is meant to be shown from when another macro is initiated. The same error message anyway.
 
Status
Not open for further replies.
< Outlook Crashes on setting SaveSentMessageFolder | Tweak vba so it can target another mailbox >
Similar threads
Thread starter Title Forum Replies Date
M Outlook 365 adding standard message in body of template email Outlook VBA and Custom Forms 3
S Adding Custom Forms Outlook VBA and Custom Forms 4
G Adding a contact to a specific folder Using Outlook 0
S Adding a recipient's column to Sent folder in Outlook 2010 Outlook VBA and Custom Forms 1
G Stop Outlook 365 adding meetings to calendar Using Outlook 1
G Removing old emails when adding accounts Using Outlook 3
D Contact Group - Adding Bulk Addresses Using Outlook 2
C Outlook 2007 Removing then adding account restores junk email processing Using Outlook 0
J O365 - Adding Shared Google Calendar ICS link issue in O365 Using Outlook 0
B Adding signature to bottom of VBA reply email Outlook VBA and Custom Forms 1
S User Defined Fields adding new value (2) Using Outlook 0
M Changing the preferred order for "Put this entry in" list for adding new contacts to the Address Book Using Outlook 1
M Adding Subject to this Link-Saving VBA Outlook VBA and Custom Forms 5
E Project Management - Adding Folders for Different Folder Types Using Outlook.com accounts in Outlook 0
D Adding Enterprise Exchange Email Account to Outlook Prevents Sending via Outlook.com Account Using Outlook.com accounts in Outlook 10
S Adding new Exchange (2016) rule very slow down Microsoft Outlook Exchange Server Administration 0
Z Outlook Custom Form: Adding Dropdown(Project Code) at the end of subject Outlook VBA and Custom Forms 0
Z Adding dropdown list using custom form Outlook VBA and Custom Forms 7
M Adding Macro to populate "to" "subject" "body" not deleting email string below. Outlook VBA and Custom Forms 5
E Unable to open Outlook 2010 after adding new email account Using Outlook 4
O Adding a new account - "CompanyL (none)" line is added Using Outlook 5
broadbander Needing help with reply/reply all while keeping attachments and adding a new CC recipient. Outlook VBA and Custom Forms 5
M adding corresponding contact form data on a mass scale Using Outlook 5
A VB to "reply all" email items stored in a folder of outlook with adding a new message Outlook VBA and Custom Forms 0
K adding more rules to 'different domains check' macro Outlook VBA and Custom Forms 2
P MS OUTLOOK 2013 - Adding Sender on the CC line Using Outlook 5
R User Defined Fields adding new value Using Outlook 3
W Adding A Macro To Message Ribbon Outlook VBA and Custom Forms 2
I Collecting mail address from GAB and adding to Outlook Task Using Outlook 2
A Outlook 2016 - adding outlook.com account creates a new/strange address Using Outlook.com accounts in Outlook 18
F Adding textbox filter to listbox? Outlook VBA and Custom Forms 2
N Recurring invite sent w/distribution list adding/removing attendees Using Outlook 0
J Issues with adding iCloud to Outlook Using Outlook 1
G Adding a contact to Outlook with a custom form using Access VBA Outlook VBA and Custom Forms 1
C Macro to send email after changing from address and adding signature Outlook VBA and Custom Forms 1
J Adding Reply & Delete to main toolbar? Using Outlook 0
T Outlook 2007 adding categories Using Outlook 15
N Adding Appointment Item in Outlook to Shared Calendar Folder Outlook VBA and Custom Forms 7
Diane Poremsky Adding Emojis to Outlook's AutoCorrect Using Outlook 0
T Adding "Mark As Complete" btton to Task Remindet Pop-Up Using Outlook 3
O Saving Attachments to folder on disk and adding Initials to end of file name Outlook VBA and Custom Forms 9
Ascar_CT Adding contacts on Android phone and then syncing them to Outlook Using Outlook.com accounts in Outlook 4
A Adding a 2010 sharepoint contact list to outlook 2010 address book Using Outlook 1
M Adding fields to Task in Outlook Home and Business 2010 Outlook VBA and Custom Forms 7
S Using Send on Behalf is adding extra data in from line Using Outlook 1
Lucylou Outlook 2013 Adding Outlook.com breaks profile, "Outlook not working" messag Using Outlook.com accounts in Outlook 1
C Adding Categories when Composing Email Using Outlook 1
stephenjones Adding a business account to Outlook Using Outlook 1
Chris Grew Adding 2nd Email Address BCM (Business Contact Manager) 3
G Adding an attachment to email I just created (VBA) Outlook VBA and Custom Forms 1

Similar threads

Back
Top