I have created a Combo box on a spreadsheet (using the Control Box Toolbar)
that I would like to populate from a text file on our network
(G:\MyText.txt). I do not wish to use a range of cells on the spreadsheet to
store the data, nor data validation. Can you help? Thanks!

Steve C

Maybe something like:

Option Explicit
Sub auto_open()

Dim myFileName As String
Dim myLine As String
Dim testStr As String
Dim FileNum As Long

myFileName = "G:\MyText.txt"

testStr = ""
On Error Resume Next
testStr = Dir(myFileName)
On Error GoTo 0

If testStr = "" Then
Exit Sub
End If

Worksheets("sheet1").ComboBox1.Clear

FileNum = FreeFile
Close #FileNum
Open myFileName For Input As #FileNum
Do While Not EOF(FileNum)
Line Input #1, myLine
Worksheets("sheet1").ComboBox1.AddItem myLine
Loop

Close #FileNum

End Sub


Steve C wrote:

I have created a Combo box on a spreadsheet (using the Control Box Toolbar)
that I would like to populate from a text file on our network
(G:\MyText.txt). I do not wish to use a range of cells on the spreadsheet to
store the data, nor data validation. Can you help? Thanks!

Steve C

--

Dave Peterson

Thanks, Dave! I'll give it a try!

"Dave Peterson" wrote:

Maybe something like:

Option Explicit
Sub auto_open()

Dim myFileName As String
Dim myLine As String
Dim testStr As String
Dim FileNum As Long

myFileName = "G:\MyText.txt"

testStr = ""
On Error Resume Next
testStr = Dir(myFileName)
On Error GoTo 0

If testStr = "" Then
Exit Sub
End If

Worksheets("sheet1").ComboBox1.Clear

FileNum = FreeFile
Close #FileNum
Open myFileName For Input As #FileNum
Do While Not EOF(FileNum)
Line Input #1, myLine
Worksheets("sheet1").ComboBox1.AddItem myLine
Loop

Close #FileNum

End Sub


Steve C wrote:

I have created a Combo box on a spreadsheet (using the Control Box Toolbar)
that I would like to populate from a text file on our network
(G:\MyText.txt). I do not wish to use a range of cells on the spreadsheet to
store the data, nor data validation. Can you help? Thanks!

Steve C

--

Dave Peterson