Solved: populate ComboBox from text

[white_flag]

Hello,
finally the spring is coming. Anyway. I need your help, I try to populate an combobox via an text file (data.txt) but I do not know how to do it.

I put in attachment the example. any direction will be appreciated.
thx
A.

[Bob Phillips]

Dim LastRow As Long
Dim i As Long

Workbooks.Open ActiveSheet.Range("D6").Value
With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow

Me.ComboBox1.AddItem .Cells(i, "A").Value2
Next i
End With
ActiveWorkbook.Close savechanges:=False

[white_flag]

thx for reply ..but It is not populated the combobox. I have an error '1004.' with the option explicit..but I don't know what it is wrong. can you be more explicit?

[Bob Phillips]

I had a typo in the last line somehow, I have corrected it above, see if that fixes it.

[mdmackillop]

Option Explicit
Private Sub Worksheet_Activate()
Dim InputData
ComboBox1.Clear
Open Range("D6") For Input As #1
Do While Not EOF(1)
Line Input #1, InputData
ComboBox1.AddItem InputData
Loop
Close #1
End Sub

'Test
Private Sub ComboBox1_Click()
Cells(Rows.Count, 1).End(xlUp)(2) = ComboBox1
End Sub

[white_flag]

I had something like this:



Option Explicit

Private Sub UserForm_Initialize()
Populate_Combobox Me.ComboBox1
Populate_Combobox Me.ComboBox2
End Sub

Sub Populate_Combobox(cb As ComboBox)

Dim data As Variant
Open "data.txt" For Input As #1
data = Split(Input(LOF(1), 1), vbCrLf)
Close #1
cb.List() = data
cb.ListIndex = 0
end sub



I like to have in combobox1 different data then combobox 2 ..combobox 3 ..etc, data will be take it from the same file "data.txt", with an "|" like an separator (or something else). it is possible:



data = Split(Input(LOF(1), 1), "|")

[mdmackillop]

Can you post a sample txt file showing what you require in each combo.

[white_flag]

combobox1
Dutch
English
German
France|
combobox2
Acoustical panels
Noise insulation
Arma sound
etc...|
combobox3
0.0358
0.0789
0.0698
etc.|
combobox4
etc ...|

the text has to be change so I prefer to put it in an text file (data for comboboxes) and on excel to be just the algorithm.

[mdmackillop]

Can you post a file using manage attachments in thre Go Advanced reply section so code can be properly tested.

[white_flag]

voilla :)

[mdmackillop]

Option Explicit

Dim Data

Private Sub UserForm_Initialize()
Dim i As Long, fs, s, ts, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("C:\data.txt")
Set ts = f.OpenAsTextStream(1)
Data = Split(ts.Readall, "|")
For i = 1 To 3
Populate i
Next
End Sub

Sub Populate(i As Long)
Dim InputData, t
InputData = Split(Data(i - 1), Chr(13))
For Each t In InputData
If Len(t) > 0 Then _
Me.Controls("ComboBox" & i).AddItem Application.Substitute(t, Chr(10), "")
Next
End Sub

[white_flag]

:clap: this is fantastic :) thx.
but it is possible to insert someting like this:

if combobox1 = "Dutch" then
insert in an field an paragraph staring with "#1"ending with "|" from "c:/data.dutch" - dutch text
elseif combobox1 = "German" then
insert in an field an paragraph staring with "#1"ending with "|" from "c:/data.german" - german text
...
end if

[white_flag]

how can be inserted only one paragraph from text file (putted somewhere in the middle of the file) (not just the first line from the text file)


Sub take_txt()

Open "D:\data\data." & ComboBox1 For Input As #1
Range("A2") = Split(Input(LOF(1), 1), vbCrLf)
Close #1

End Sub

thx for the input

[white_flag]

the data from the file needs to have an "marker" I choose "="


Private Sub insert_txt()

Dim nFile As Integer
Dim sTemp As String
Dim nEqualsSignPos As Integer

nFile = FreeFile()
Open "D:\data\data." & ComboBox1 For Input As #nFile
Do While Not EOF(nFile)
Input #nFile, sTemp
If Len(sTemp) > 0 Then
nEqualsSignPos = InStr(sTemp, "=") ' or an different mark
nEqualsSignPos = nEqualsSignPos + 1
If InStr(sTemp, ComboBox2) Then
Range("A3") = Mid(sTemp, nEqualsSignPos)
End If
End If
Loop

Close nFile
MsgBox "The text was inserted"

End Sub



thx for Help :)
A.