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.
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.
[vba]
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
[/vba]
Last edited by Bob Phillips; 03-18-2010 at 10:14 AM.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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?
I had a typo in the last line somehow, I have corrected it above, see if that fixes it.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
[vba]
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
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I had something like this:
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: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
data = Split(Input(LOF(1), 1), "|")
Can you post a sample txt file showing what you require in each combo.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.
Can you post a file using manage attachments in thre Go Advanced reply section so code can be properly tested.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
voilla
[vba]
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
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
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)
thx for the inputSub take_txt() Open "D:\data\data." & ComboBox1 For Input As #1 Range("A2") = Split(Input(LOF(1), 1), vbCrLf) Close #1 End Sub
the data from the file needs to have an "marker" I choose "="
thx for HelpPrivate 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
A.