Drop Down List With .txt File Instead Of .xlsx

Legacy Poster

• 28. Juli 2014 20:03

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!

0

• 

  Shotaro Ito

  ★ BarTender Hero ★

  • 29. Juli 2014 02:41

  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
• 

  Legacy Poster

  ★ BarTender Hero ★

  • 28. August 2014 17:15

  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
• 

  Shotaro Ito

  ★ BarTender Hero ★

  • 1. September 2014 08:23

  answered in this topic.

  0

Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.