[jlawson]

Does anyone have some VBA code that will allow the combo box in Word to
access an Excel spread sheet or .txt file for it's dropdown list and
populate that data?

I can create it to list the array I put into the code but I want it to
access a spread sheet other people can update easily without messing
with the code. I apologize if this has been posted before. Thank you
for your time.

[jlawson]

jlawson wrote:
Does anyone have some VBA code that will allow the combo box in Word to
access an Excel spread sheet or .txt file for it's dropdown list and
populate that data?

I can create it to list the array I put into the code but I want it to
access a spread sheet other people can update easily without messing
with the code. I apologize if this has been posted before. Thank you
for your time.

I forgot to add that I am using MS Word XP

[Ed]

Hi jlawson,

Here are a couple of ways you might go about populating a combobox from a
text file. The first method uses VBA's builtin commands and the second method
uses the Scripting.FileSystemObject. If you want to use the FileSystemObject
you need to create a reference in your VBA project to the library that
contains it: you do this by clicking Tools/References and selecting
"Microsoft Scripting Runtime".

Regards.

Ed

Private Sub cmdReadUsingVBABuiltin_Click()
Dim FileHandle As Integer
Dim FName As String
Dim TextLine As String
Const FilePath As String = "c:\testfile.txt"

FName = Dir(FilePath)

If FName "" Then
FileHandle = FreeFile

Open FilePath For Input Shared As FileHandle

Do While Not EOF(FileHandle)
Line Input #FileHandle, TextLine
cboItems.AddItem TextLine
Loop

Close #FileHandle

If cboItems.ListCount 0 Then
cboItems.ListIndex = 0
Else
MsgBox "There are no items to list.", vbCritical, "Hmm!"
End If
Else
MsgBox "No such file:" & vbCrLf & FilePath, vbCritical, "Drat!"
End If
End Sub

Private Sub cmdReadUsingFSO_Click()
Dim FSO As Scripting.FileSystemObject
Dim TSIn As Scripting.TextStream
Dim FileToRead As Scripting.File
Dim TextLine As String
Const FilePath As String = "c:\testfile.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")

If Not FSO.FileExists(FilePath) Then
MsgBox "No such file:" & vbCrLf & FilePath, vbCritical, "Drat!"
Exit Sub
End If

Set FileToRead = FSO.GetFile(FilePath)

Set TSIn = FileToRead.OpenAsTextStream(ForReading)

Do While Not TSIn.AtEndOfStream
TextLine = TSIn.ReadLine
cboItems.AddItem (TextLine)
Loop

Set TSIn = Nothing
Set FSO = Nothing

If cboItems.ListCount 0 Then
cboItems.ListIndex = 0
Else
MsgBox "There are no items to list.", vbCritical, "Hmm!"
End If
End Sub


"jlawson" wrote:

Does anyone have some VBA code that will allow the combo box in Word to
access an Excel spread sheet or .txt file for it's dropdown list and
populate that data?

I can create it to list the array I put into the code but I want it to
access a spread sheet other people can update easily without messing
with the code. I apologize if this has been posted before. Thank you
for your time.

[jlawson]

Ok, I have the combo box on the word document itself. I created the
txt file. When I click on the drop down box nothing appears in the
drop down and if I type in it I have to debug because VBA says that the
object can not be found. The debug menu highlights the
"cboItems.AddItem TextLine" line of the code and I can hover my mouse
over the "TextLine" part to see the first name in my txt file, in this
case "Mary" is the first name. I'm sorry I don't have much experience
with combo boxes. Below is the exact code.


Private Sub ComboBox3_Change()
ComboBox3.ColumnCount = 1
Dim FileHandle As Integer
Dim FName As String
Dim TextLine As String
Const FilePath As String = "c:\testfile.txt"


FName = Dir(FilePath)

'ComboBox1.ColumnCount = 1


If FName "" Then
FileHandle = FreeFile


Open FilePath For Input Shared As FileHandle


Do While Not EOF(FileHandle)
Line Input #FileHandle, TextLine
cboItems.AddItem TextLine
Loop


Close #FileHandle


If cboItems.ListCount 0 Then
cboItems.ListIndex = 0
Else
MsgBox "There are no items to list.", vbCritical, "Hmm!"
End If
Else
MsgBox "No such file:" & vbCrLf & FilePath, vbCritical, "Drat!"
End If
End Sub


Ed wrote:
Hi jlawson,

Here are a couple of ways you might go about populating a combobox from a
text file. The first method uses VBA's builtin commands and the second method
uses the Scripting.FileSystemObject. If you want to use the FileSystemObject
you need to create a reference in your VBA project to the library that
contains it: you do this by clicking Tools/References and selecting
"Microsoft Scripting Runtime".

Regards.

Ed

Private Sub cmdReadUsingVBABuiltin_Click()
Dim FileHandle As Integer
Dim FName As String
Dim TextLine As String
Const FilePath As String = "c:\testfile.txt"

FName = Dir(FilePath)

If FName "" Then
FileHandle = FreeFile

Open FilePath For Input Shared As FileHandle

Do While Not EOF(FileHandle)
Line Input #FileHandle, TextLine
cboItems.AddItem TextLine
Loop

Close #FileHandle

If cboItems.ListCount 0 Then
cboItems.ListIndex = 0
Else
MsgBox "There are no items to list.", vbCritical, "Hmm!"
End If
Else
MsgBox "No such file:" & vbCrLf & FilePath, vbCritical, "Drat!"
End If
End Sub

Private Sub cmdReadUsingFSO_Click()
Dim FSO As Scripting.FileSystemObject
Dim TSIn As Scripting.TextStream
Dim FileToRead As Scripting.File
Dim TextLine As String
Const FilePath As String = "c:\testfile.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")

If Not FSO.FileExists(FilePath) Then
MsgBox "No such file:" & vbCrLf & FilePath, vbCritical, "Drat!"
Exit Sub
End If

Set FileToRead = FSO.GetFile(FilePath)

Set TSIn = FileToRead.OpenAsTextStream(ForReading)

Do While Not TSIn.AtEndOfStream
TextLine = TSIn.ReadLine
cboItems.AddItem (TextLine)
Loop

Set TSIn = Nothing
Set FSO = Nothing

If cboItems.ListCount 0 Then
cboItems.ListIndex = 0
Else
MsgBox "There are no items to list.", vbCritical, "Hmm!"
End If
End Sub


"jlawson" wrote:

Does anyone have some VBA code that will allow the combo box in Word to
access an Excel spread sheet or .txt file for it's dropdown list and
populate that data?

I can create it to list the array I put into the code but I want it to
access a spread sheet other people can update easily without messing
with the code. I apologize if this has been posted before. Thank you
for your time.

[Ed]

Hello again.

Ok, I have the combo box on the word document itself.

My mistake. I assumed the combobox was on a VBA userform, not in a document
type of form. The first code sample presumes that a userform has a combobox
called "cboItems" and a command button called "cmdReadUsingVBABuiltin". As it
stands this code won't work for a combobox in a Word document.

I'm guessing that you created the combobox using the Controls toolbar?

I've never tried before to populate one of those using VBA (except just now
as a test). One way that seems as though it might work is this:

I'm assuming that you're creating a template (.dot) and that the combobox's
name shows as "ComboBox1" when you view its code.

With the template open, get to the code window for the document itself. I
think you can do this by right-clicking the combobox in design mode and
choosing "View code" or by going into VBA and double-clicking the template's
"ThisDocument" module.

In the left-hand drop-down select "Document". Then in the right-hand
drop-down select "New". This creates an empty event handler which runs each
time a new document is created based on the template.

Copy the code from the sample (excluding the "Private Sub ..." and "End Sub"
lines) into the Document_New procedure. Change any occurrences of "cboItems"
to "ComboBox1".

Remove the Document_Change procedure.

Exit design mode, protect the form and save it.

Then create a new document based on the template (either through "File/New"
or by double-clicking it in Explorer), and see if it has worked.

(If you are creating a document (.doc) then in the above steps select "Open"
rather than "New" from the right-hand drop-down. This creates an empty event
handler which runs each time the document is opened.)

Regards.

Ed




"jlawson" wrote:

Ok, I have the combo box on the word document itself. I created the
txt file. When I click on the drop down box nothing appears in the
drop down and if I type in it I have to debug because VBA says that the
object can not be found. The debug menu highlights the
"cboItems.AddItem TextLine" line of the code and I can hover my mouse
over the "TextLine" part to see the first name in my txt file, in this
case "Mary" is the first name. I'm sorry I don't have much experience
with combo boxes. Below is the exact code.


Private Sub ComboBox3_Change()
ComboBox3.ColumnCount = 1
Dim FileHandle As Integer
Dim FName As String
Dim TextLine As String
Const FilePath As String = "c:\testfile.txt"


FName = Dir(FilePath)

'ComboBox1.ColumnCount = 1


If FName "" Then
FileHandle = FreeFile


Open FilePath For Input Shared As FileHandle


Do While Not EOF(FileHandle)
Line Input #FileHandle, TextLine
cboItems.AddItem TextLine
Loop


Close #FileHandle


If cboItems.ListCount 0 Then
cboItems.ListIndex = 0
Else
MsgBox "There are no items to list.", vbCritical, "Hmm!"
End If
Else
MsgBox "No such file:" & vbCrLf & FilePath, vbCritical, "Drat!"
End If
End Sub


Ed wrote:
Hi jlawson,

Here are a couple of ways you might go about populating a combobox from a
text file. The first method uses VBA's builtin commands and the second method
uses the Scripting.FileSystemObject. If you want to use the FileSystemObject
you need to create a reference in your VBA project to the library that
contains it: you do this by clicking Tools/References and selecting
"Microsoft Scripting Runtime".

Regards.

Ed

Private Sub cmdReadUsingVBABuiltin_Click()
Dim FileHandle As Integer
Dim FName As String
Dim TextLine As String
Const FilePath As String = "c:\testfile.txt"

FName = Dir(FilePath)

If FName "" Then
FileHandle = FreeFile

Open FilePath For Input Shared As FileHandle

Do While Not EOF(FileHandle)
Line Input #FileHandle, TextLine
cboItems.AddItem TextLine
Loop

Close #FileHandle

If cboItems.ListCount 0 Then
cboItems.ListIndex = 0
Else
MsgBox "There are no items to list.", vbCritical, "Hmm!"
End If
Else
MsgBox "No such file:" & vbCrLf & FilePath, vbCritical, "Drat!"
End If
End Sub

Private Sub cmdReadUsingFSO_Click()
Dim FSO As Scripting.FileSystemObject
Dim TSIn As Scripting.TextStream
Dim FileToRead As Scripting.File
Dim TextLine As String
Const FilePath As String = "c:\testfile.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")

If Not FSO.FileExists(FilePath) Then
MsgBox "No such file:" & vbCrLf & FilePath, vbCritical, "Drat!"
Exit Sub
End If

Set FileToRead = FSO.GetFile(FilePath)

Set TSIn = FileToRead.OpenAsTextStream(ForReading)

Do While Not TSIn.AtEndOfStream
TextLine = TSIn.ReadLine
cboItems.AddItem (TextLine)
Loop

Set TSIn = Nothing
Set FSO = Nothing

If cboItems.ListCount 0 Then
cboItems.ListIndex = 0
Else
MsgBox "There are no items to list.", vbCritical, "Hmm!"
End If
End Sub


"jlawson" wrote:

Does anyone have some VBA code that will allow the combo box in Word to
access an Excel spread sheet or .txt file for it's dropdown list and
populate that data?

I can create it to list the array I put into the code but I want it to
access a spread sheet other people can update easily without messing
with the code. I apologize if this has been posted before. Thank you
for your time.

[jlawson]

Thank you, ED! Super code once again!


Ed wrote:
Hello again.

Ok, I have the combo box on the word document itself.

My mistake. I assumed the combobox was on a VBA userform, not in a document
type of form. The first code sample presumes that a userform has a combobox
called "cboItems" and a command button called "cmdReadUsingVBABuiltin". As it
stands this code won't work for a combobox in a Word document.

I'm guessing that you created the combobox using the Controls toolbar?

I've never tried before to populate one of those using VBA (except just now
as a test). One way that seems as though it might work is this:

I'm assuming that you're creating a template (.dot) and that the combobox's
name shows as "ComboBox1" when you view its code.

With the template open, get to the code window for the document itself. I
think you can do this by right-clicking the combobox in design mode and
choosing "View code" or by going into VBA and double-clicking the template's
"ThisDocument" module.

In the left-hand drop-down select "Document". Then in the right-hand
drop-down select "New". This creates an empty event handler which runs each
time a new document is created based on the template.

Copy the code from the sample (excluding the "Private Sub ..." and "End Sub"
lines) into the Document_New procedure. Change any occurrences of "cboItems"
to "ComboBox1".

Remove the Document_Change procedure.

Exit design mode, protect the form and save it.

Then create a new document based on the template (either through "File/New"
or by double-clicking it in Explorer), and see if it has worked.

(If you are creating a document (.doc) then in the above steps select "Open"
rather than "New" from the right-hand drop-down. This creates an empty event
handler which runs each time the document is opened.)

Regards.

Ed




"jlawson" wrote:

Ok, I have the combo box on the word document itself. I created the
txt file. When I click on the drop down box nothing appears in the
drop down and if I type in it I have to debug because VBA says that the
object can not be found. The debug menu highlights the
"cboItems.AddItem TextLine" line of the code and I can hover my mouse
over the "TextLine" part to see the first name in my txt file, in this
case "Mary" is the first name. I'm sorry I don't have much experience
with combo boxes. Below is the exact code.


Private Sub ComboBox3_Change()
ComboBox3.ColumnCount = 1
Dim FileHandle As Integer
Dim FName As String
Dim TextLine As String
Const FilePath As String = "c:\testfile.txt"


FName = Dir(FilePath)

'ComboBox1.ColumnCount = 1


If FName "" Then
FileHandle = FreeFile


Open FilePath For Input Shared As FileHandle


Do While Not EOF(FileHandle)
Line Input #FileHandle, TextLine
cboItems.AddItem TextLine
Loop


Close #FileHandle


If cboItems.ListCount 0 Then
cboItems.ListIndex = 0
Else
MsgBox "There are no items to list.", vbCritical, "Hmm!"
End If
Else
MsgBox "No such file:" & vbCrLf & FilePath, vbCritical, "Drat!"
End If
End Sub


Ed wrote:
Hi jlawson,

Here are a couple of ways you might go about populating a combobox from a
text file. The first method uses VBA's builtin commands and the second method
uses the Scripting.FileSystemObject. If you want to use the FileSystemObject
you need to create a reference in your VBA project to the library that
contains it: you do this by clicking Tools/References and selecting
"Microsoft Scripting Runtime".

Regards.

Ed

Private Sub cmdReadUsingVBABuiltin_Click()
Dim FileHandle As Integer
Dim FName As String
Dim TextLine As String
Const FilePath As String = "c:\testfile.txt"

FName = Dir(FilePath)

If FName "" Then
FileHandle = FreeFile

Open FilePath For Input Shared As FileHandle

Do While Not EOF(FileHandle)
Line Input #FileHandle, TextLine
cboItems.AddItem TextLine
Loop

Close #FileHandle

If cboItems.ListCount 0 Then
cboItems.ListIndex = 0
Else
MsgBox "There are no items to list.", vbCritical, "Hmm!"
End If
Else
MsgBox "No such file:" & vbCrLf & FilePath, vbCritical, "Drat!"
End If
End Sub

Private Sub cmdReadUsingFSO_Click()
Dim FSO As Scripting.FileSystemObject
Dim TSIn As Scripting.TextStream
Dim FileToRead As Scripting.File
Dim TextLine As String
Const FilePath As String = "c:\testfile.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")

If Not FSO.FileExists(FilePath) Then
MsgBox "No such file:" & vbCrLf & FilePath, vbCritical, "Drat!"
Exit Sub
End If

Set FileToRead = FSO.GetFile(FilePath)

Set TSIn = FileToRead.OpenAsTextStream(ForReading)

Do While Not TSIn.AtEndOfStream
TextLine = TSIn.ReadLine
cboItems.AddItem (TextLine)
Loop

Set TSIn = Nothing
Set FSO = Nothing

If cboItems.ListCount 0 Then
cboItems.ListIndex = 0
Else
MsgBox "There are no items to list.", vbCritical, "Hmm!"
End If
End Sub


"jlawson" wrote:

Does anyone have some VBA code that will allow the combo box in Word to
access an Excel spread sheet or .txt file for it's dropdown list and
populate that data?

I can create it to list the array I put into the code but I want it to
access a spread sheet other people can update easily without messing
with the code. I apologize if this has been posted before. Thank you
for your time.

[Ed]

You're welcome.

Cheers.

Ed

"jlawson" wrote:

Thank you, ED! Super code once again!


Ed wrote:
Hello again.

Ok, I have the combo box on the word document itself.

My mistake. I assumed the combobox was on a VBA userform, not in a document
type of form. The first code sample presumes that a userform has a combobox
called "cboItems" and a command button called "cmdReadUsingVBABuiltin". As it
stands this code won't work for a combobox in a Word document.

I'm guessing that you created the combobox using the Controls toolbar?

I've never tried before to populate one of those using VBA (except just now
as a test). One way that seems as though it might work is this:

I'm assuming that you're creating a template (.dot) and that the combobox's
name shows as "ComboBox1" when you view its code.

With the template open, get to the code window for the document itself. I
think you can do this by right-clicking the combobox in design mode and
choosing "View code" or by going into VBA and double-clicking the template's
"ThisDocument" module.

In the left-hand drop-down select "Document". Then in the right-hand
drop-down select "New". This creates an empty event handler which runs each
time a new document is created based on the template.

Copy the code from the sample (excluding the "Private Sub ..." and "End Sub"
lines) into the Document_New procedure. Change any occurrences of "cboItems"
to "ComboBox1".

Remove the Document_Change procedure.

Exit design mode, protect the form and save it.

Then create a new document based on the template (either through "File/New"
or by double-clicking it in Explorer), and see if it has worked.

(If you are creating a document (.doc) then in the above steps select "Open"
rather than "New" from the right-hand drop-down. This creates an empty event
handler which runs each time the document is opened.)

Regards.

Ed




"jlawson" wrote:

Ok, I have the combo box on the word document itself. I created the
txt file. When I click on the drop down box nothing appears in the
drop down and if I type in it I have to debug because VBA says that the
object can not be found. The debug menu highlights the
"cboItems.AddItem TextLine" line of the code and I can hover my mouse
over the "TextLine" part to see the first name in my txt file, in this
case "Mary" is the first name. I'm sorry I don't have much experience
with combo boxes. Below is the exact code.


Private Sub ComboBox3_Change()
ComboBox3.ColumnCount = 1
Dim FileHandle As Integer
Dim FName As String
Dim TextLine As String
Const FilePath As String = "c:\testfile.txt"


FName = Dir(FilePath)

'ComboBox1.ColumnCount = 1


If FName "" Then
FileHandle = FreeFile


Open FilePath For Input Shared As FileHandle


Do While Not EOF(FileHandle)
Line Input #FileHandle, TextLine
cboItems.AddItem TextLine
Loop


Close #FileHandle


If cboItems.ListCount 0 Then
cboItems.ListIndex = 0
Else
MsgBox "There are no items to list.", vbCritical, "Hmm!"
End If
Else
MsgBox "No such file:" & vbCrLf & FilePath, vbCritical, "Drat!"
End If
End Sub


Ed wrote:
Hi jlawson,

Here are a couple of ways you might go about populating a combobox from a
text file. The first method uses VBA's builtin commands and the second method
uses the Scripting.FileSystemObject. If you want to use the FileSystemObject
you need to create a reference in your VBA project to the library that
contains it: you do this by clicking Tools/References and selecting
"Microsoft Scripting Runtime".

Regards.

Ed

Private Sub cmdReadUsingVBABuiltin_Click()
Dim FileHandle As Integer
Dim FName As String
Dim TextLine As String
Const FilePath As String = "c:\testfile.txt"

FName = Dir(FilePath)

If FName "" Then
FileHandle = FreeFile

Open FilePath For Input Shared As FileHandle

Do While Not EOF(FileHandle)
Line Input #FileHandle, TextLine
cboItems.AddItem TextLine
Loop

Close #FileHandle

If cboItems.ListCount 0 Then
cboItems.ListIndex = 0
Else
MsgBox "There are no items to list.", vbCritical, "Hmm!"
End If
Else
MsgBox "No such file:" & vbCrLf & FilePath, vbCritical, "Drat!"
End If
End Sub

Private Sub cmdReadUsingFSO_Click()
Dim FSO As Scripting.FileSystemObject
Dim TSIn As Scripting.TextStream
Dim FileToRead As Scripting.File
Dim TextLine As String
Const FilePath As String = "c:\testfile.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")

If Not FSO.FileExists(FilePath) Then
MsgBox "No such file:" & vbCrLf & FilePath, vbCritical, "Drat!"
Exit Sub
End If

Set FileToRead = FSO.GetFile(FilePath)

Set TSIn = FileToRead.OpenAsTextStream(ForReading)

Do While Not TSIn.AtEndOfStream
TextLine = TSIn.ReadLine
cboItems.AddItem (TextLine)
Loop

Set TSIn = Nothing
Set FSO = Nothing

If cboItems.ListCount 0 Then
cboItems.ListIndex = 0
Else
MsgBox "There are no items to list.", vbCritical, "Hmm!"
End If
End Sub


"jlawson" wrote:

Does anyone have some VBA code that will allow the combo box in Word to
access an Excel spread sheet or .txt file for it's dropdown list and
populate that data?

I can create it to list the array I put into the code but I want it to
access a spread sheet other people can update easily without messing
with the code. I apologize if this has been posted before. Thank you
for your time.