Maybe you could have a routine that opens the text file and updates an existing
sheet (maybe even hide that sheet) when the workbook opens.
I created a worksheet named Table, then used Insert|name to create a name that
changed with the amount of data in column A.
Debra Dalgleish has some more notes:
http://contextures.com/xlNames01.html#Dynamic
Then used that range name as the list in a Data|Validation cell.
http://www.contextures.com/xlDataVal01.html
(also from Debra's site).
Then I could use a macro that updated worksheet:
Option Explicit
Sub Auto_Open()
Dim myTextFileName As String
Dim TextWks As Worksheet
Dim TableWks As Worksheet
Set TableWks = ThisWorkbook.Worksheets("Table")
myTextFileName = "C:\myfolder\text.txt"
myTextFileName = "C:\My Documents\Excel\spacedelim.txt"
Workbooks.OpenText Filename:=myTextFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1))
Set TextWks = ActiveSheet
'clean up existing values
TableWks.Cells.Clear
'copy new values
TextWks.Range("A:A").Copy _
Destination:=TableWks.Range("a1")
'close the text file
TextWks.Parent.Close savechanges:=False
End Sub
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
kspeters wrote:
I have this spreadsheet that I wanta column of cells to have a drop down box
that would show choices froma text file that periodically gets updated. I
would like the drop down box to "point" to this external file to get it's
data. Is this possible?
--
Dave Peterson