I need to populate a ComboBox with the filenames contained in a directory

I am using the following code to find the first file but am at a loss to
find the remaining files

Private Sub UserForm_Initialize()
NLDir = ThisWorkbook.Path & "\NewsLetters\"
NL = Dir(NLDir & "*.pdf")
End Sub

Some example code would be greatly appreciated

Regards and TIA

Here is a way from John Walkenbach...
http://j-walk.com/ss/excel/tips/tip18.htm
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"JRB @eclipse.co.uk>" <sa3214<nospam>
wrote in message
I need to populate a ComboBox with the filenames contained in a directory
I am using the following code to find the first file but am at a loss to
find the remaining files

Private Sub UserForm_Initialize()
NLDir = ThisWorkbook.Path & "\NewsLetters\"
NL = Dir(NLDir & "*.pdf")
End Sub

Some example code would be greatly appreciated
Regards and TIA

I've now managed to populate an array with the filenames using the following
code:

Dim NLArray

NLDir = ThisWorkbook.Path & "\NewsLetters\"

NL = Dir(NLDir & "*.pdf")
NLArray = NL

Do While NL <> ""
NL = Dir
NLArray = NLArray & ", " & NL
Loop

I now require to use the contents of the array as the RowSource of a
ComboBox

How do I achieve this ?

Regards and TIA

It's not a RowSource question at all, even though that's what the topic
indicates.

Private Sub UserForm_Initialize()
Dim NLDir As String
Dim NL As String
NLDir = ThisWorkbook.Path & "\NewsLetters\"
NL = Dir(NLDir & "*.pdf")
With Me.ComboBox1
Do Until NL = vbNullString
.AddItem NL
NL = Dir()
Loop
End With
End Sub

Note that you have no control over the order in which Dir() return the file
names.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)


"JRB @eclipse.co.uk>" <sa3214<nospam> wrote in message
news:GK-dnRiU--(E-Mail Removed)...
>I need to populate a ComboBox with the filenames contained in a directory
>
> I am using the following code to find the first file but am at a loss to
> find the remaining files
>
> Private Sub UserForm_Initialize()
> NLDir = ThisWorkbook.Path & "\NewsLetters\"
> NL = Dir(NLDir & "*.pdf")
> End Sub
>
> Some example code would be greatly appreciated
>
> Regards and TIA
>
>

Thanks Chip,
I was attempting to use an array - but as always you have provided a much
simpler solution.
Many Thanks
BTW - The filenames are listed in alpahnumeric order which is exactly what I
require



"Chip Pearson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> It's not a RowSource question at all, even though that's what the topic
> indicates.
>
> Private Sub UserForm_Initialize()
> Dim NLDir As String
> Dim NL As String
> NLDir = ThisWorkbook.Path & "\NewsLetters\"
> NL = Dir(NLDir & "*.pdf")
> With Me.ComboBox1
> Do Until NL = vbNullString
> .AddItem NL
> NL = Dir()
> Loop
> End With
> End Sub
>
> Note that you have no control over the order in which Dir() return the
> file names.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
> (email on the web site)
>
>
> "JRB @eclipse.co.uk>" <sa3214<nospam> wrote in message
> news:GK-dnRiU--(E-Mail Removed)...
>>I need to populate a ComboBox with the filenames contained in a directory
>>
>> I am using the following code to find the first file but am at a loss to
>> find the remaining files
>>
>> Private Sub UserForm_Initialize()
>> NLDir = ThisWorkbook.Path & "\NewsLetters\"
>> NL = Dir(NLDir & "*.pdf")
>> End Sub
>>
>> Some example code would be greatly appreciated
>>
>> Regards and TIA
>>
>>
>
>

Dim NLArray() as String

NLDir = ThisWorkbook.Path & "\NewsLetters\"

NL = Dir(NLDir & "*.pdf")
Redim NLArray(1 to 1)
NLArray(1) = NL

Do While NL <> ""
NL = Dir
Redim Preserve NLArray(1 to ubound(NLArray)+1)
NLArray(ubound(NLArray)) = NL
Loop
Userform1.Combobox1.List = NLArray

you would not set the rowsource unless you wanted to put the array on the
worksheet first. Then it would be

set rng = Worksheets("Sheet1").Resize(Ubound(NLArray)-Lbound(NLArray)+1,1)
rng.Value = Application.Transpose(NLArray)
Useform1.Combobox1.RowSource = rng.Address(1,1,xlA1,True)


Your approach actually builds a string - not an array. You could go that
direction and use split to make it into an array.


Dim NLArray variant
Dim s as String

NLDir = ThisWorkbook.Path & "\NewsLetters\"

NL = Dir(NLDir & "*.pdf")
s = NL

Do While NL <> ""
NL = Dir
s = s & ", " & NL
Loop

NLArray = Split(s,",")

set rng = Worksheets("Sheet1").Resize(Ubound(NLArray)-Lbound(NLArray)+1,1)
rng.Value = Application.Transpose(NLArray)
Useform1.Combobox1.RowSource = rng.Address(1,1,xlA1,True)

or

Userform1.Combobox1.List = NLArray

--
Regards,
Tom Ogilvy


"JRB @eclipse.co.uk>" <sa3214<nospam> wrote in message
news:6e-(E-Mail Removed)...
> I've now managed to populate an array with the filenames using the
> following code:
>
> Dim NLArray
>
> NLDir = ThisWorkbook.Path & "\NewsLetters\"
>
> NL = Dir(NLDir & "*.pdf")
> NLArray = NL
>
> Do While NL <> ""
> NL = Dir
> NLArray = NLArray & ", " & NL
> Loop
>
> I now require to use the contents of the array as the RowSource of a
> ComboBox
>
> How do I achieve this ?
>
> Regards and TIA
>
>

Many thanks for the response Tom, I had already used Chip's solution ... but
I will work through your's as well

Thanks again - this is a remarkable group - Long may it flourish

Jim Burton

"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Dim NLArray() as String
>
> NLDir = ThisWorkbook.Path & "\NewsLetters\"
>
> NL = Dir(NLDir & "*.pdf")
> Redim NLArray(1 to 1)
> NLArray(1) = NL
>
> Do While NL <> ""
> NL = Dir
> Redim Preserve NLArray(1 to ubound(NLArray)+1)
> NLArray(ubound(NLArray)) = NL
> Loop
> Userform1.Combobox1.List = NLArray
>
> you would not set the rowsource unless you wanted to put the array on the
> worksheet first. Then it would be
>
> set rng =
> Worksheets("Sheet1").Resize(Ubound(NLArray)-Lbound(NLArray)+1,1)
> rng.Value = Application.Transpose(NLArray)
> Useform1.Combobox1.RowSource = rng.Address(1,1,xlA1,True)
>
>
> Your approach actually builds a string - not an array. You could go that
> direction and use split to make it into an array.
>
>
> Dim NLArray variant
> Dim s as String
>
> NLDir = ThisWorkbook.Path & "\NewsLetters\"
>
> NL = Dir(NLDir & "*.pdf")
> s = NL
>
> Do While NL <> ""
> NL = Dir
> s = s & ", " & NL
> Loop
>
> NLArray = Split(s,",")
>
> set rng =
> Worksheets("Sheet1").Resize(Ubound(NLArray)-Lbound(NLArray)+1,1)
> rng.Value = Application.Transpose(NLArray)
> Useform1.Combobox1.RowSource = rng.Address(1,1,xlA1,True)
>
> or
>
> Userform1.Combobox1.List = NLArray
>
> --
> Regards,
> Tom Ogilvy
>
>
> "JRB @eclipse.co.uk>" <sa3214<nospam> wrote in message
> news:6e-(E-Mail Removed)...
>> I've now managed to populate an array with the filenames using the
>> following code:
>>
>> Dim NLArray
>>
>> NLDir = ThisWorkbook.Path & "\NewsLetters\"
>>
>> NL = Dir(NLDir & "*.pdf")
>> NLArray = NL
>>
>> Do While NL <> ""
>> NL = Dir
>> NLArray = NLArray & ", " & NL
>> Loop
>>
>> I now require to use the contents of the array as the RowSource of a
>> ComboBox
>>
>> How do I achieve this ?
>>
>> Regards and TIA
>>
>>
>
>