Drop Down List With .txt File Instead Of .xlsx Follow

0
Avatar
Legacy Poster

Hi, First time poster here.

 

I have a script I got from reading the forums here for a simple drop down list pulled from a file.

 

FUNCTIONS AND SUBS:


dim objConn
dim strConn
dim rs
 
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='c:\Database\ingredients.xlsx';Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;"""


Public Function GetNames()
    Dim strNames
 
    Set objConn = CreateObject("ADODB.Connection")
    objConn.Open strConn
    Set rs = CreateObject("ADODB.Recordset")
    Set rs = objConn.Execute("SELECT * FROM [Sheet1$]")
 
    strNames = ""
    rs.MoveFirst()
    do while NOT rs.EOF
        strNames = strNames + rs.fields("Ingredients") + vbCrLf
        rs.MoveNext()
    loop
 
    GetNames = strNames
 
End Function

ONFILLLIST


Value = GetNames()

While it works perfectly, I would like to have it pull from a txt file instead.

I am completely lost as to how this is to be accomplished.

 

Thank you for your time!

3 comments

0
Avatar
Shotaro Ito
Comment actions Permalink

Hi,

I thought there was - though I couldn't find that in the forum. try below:

 

Function and Subs:
Function GetNamesFromText(textPath)
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set txtFile = fso.OpenTextFile(textPath, ForReading, TristateFalse)
  options = ""
  Do Until txtFile.AtEndOfStream = true
      options = options & txtFile.ReadLine & vbCr
  Loop
  txtFile.Close
  set txtFile = Nothing
  set fso = nothing
  GetNamesFromText = options
End Function
 
OnFillList:
Value = GetNamesFromText("c:\listitems.txt")

example of list items - plain text file with a entry per line.

John Smith
Taro Yamada
Mario Rossi

If you specify ".\filename.txt", it picks from the same folder as btw document.

 

In case someone need OLEDB connection string for CSV text with column header, it goes like this.

 

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""c:\tmp\"";Extended Properties=""Text;HDR=YES;FMT=Delimited"""
...
Set rs = objConn.Execute("SELECT * FROM listitem.csv")
0
Avatar
Legacy Poster
Comment actions Permalink

Hello,

I am currently trying to set up some templates for a client and wanted to incorporate some dropdown lists that link to a database.

 

This VB script in this post was helpful but the VB script in the post applies the one field to both the 'Display Text' and the 'Data Source Value'. I would like to modify it so that the dropdown list "Display Text' reads from one field and the 'Data Source Value' reads from another field from the same excel spreadsheet. 

 

Any help would be appreciated.

 

We are resellers of Bartender software and TSC printers.

0
Avatar
Shotaro Ito
Comment actions Permalink

answered in this topic.

Please sign in to leave a comment.