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
...
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" wrote in message
...
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
...
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
...
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" wrote in message
...
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
...
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