PDA

View Full Version : Solved: help with additem from textfile



Emoncada
10-29-2012, 09:26 AM
I have the following code that works perfectly, but I need it for 3 Combobox's from 3 different files.

Private Sub Workbook_Open()
Dim F As Integer, FromFile As String
F = FreeFile(1)
ActiveSheet.OLEObjects("CmbModel").Object.Clear
Open "H:\Excel\Data\Models.txt" For Input As #F
Do Until EOF(F)
Line Input #F, FromFile
ActiveSheet.OLEObjects("CmbModel").Object.AddItem FromFile
Loop
Close #F

End Sub


What i was trying is something like this


Private Sub Workbook_Open()
Dim F As Integer, FromFile As String
F = FreeFile(1)
ActiveSheet.OLEObjects("CmbModel").Object.Clear
Open "H:\Excel\Data\Models.txt" For Input As #F
Do Until EOF(F)
Line Input #F, FromFile
ActiveSheet.OLEObjects("CmbModel").Object.AddItem FromFile
Loop
Close #F

Dim G As Integer, FromFile1 As String
G = FreeFile(1)
ActiveSheet.OLEObjects("CmbIssue").Object.Clear
Open "H:\Excel\Data\Issues.txt" For Input As #G
Do Until EOF(G)
Line Input #G, FromFile1
ActiveSheet.OLEObjects("CmbIssue").Object.AddItem FromFile1
Loop
Close #G

End Sub

I would also need to add one more set, any help would be great.

Thanks

snb
10-29-2012, 09:43 AM
Avoid populating listboxes/comboboxes using Additem; use .list instead


A oneliner suffices:


Private Sub Workbook_Open()


with createobject("scripting.filesystemobject")

for j=1 to 3
ActiveSheet.OLEObjects("CmbModel" & j).Object.list=split(.opentextfile("H:\Excel\Data\Models" & j & ".txt").readall,vbcrlf)
next
end with
End Sub

Emoncada
10-29-2012, 09:48 AM
snb thanks for the reply.
I am now getting a
"ActiveX component can't create object"

any idea?

Emoncada
10-29-2012, 10:12 AM
I don't think that will work for this case, since the cmbboxes have different names not cmbmodel1, cmbmodel2, cmbmodel3. Also text files have different names.

Emoncada
10-29-2012, 10:50 AM
any other ideas?

snb
10-29-2012, 10:50 AM
You have my permission to adapt the code.
You will have to do something yourself.
I merely offer a suggestion for a method to accomplish your task. Don't expect complete solutions....

Taking my code as a starting point, 99% of your solution is ready, you only have to do the remaining 1%.

Kenneth Hobs
10-29-2012, 11:14 AM
You don't know why it had the error? If you don't name your controls and file names with sequenced suffix names, you are left with a brute strength approach or at least a method where you have to poke the control names and/or file names into a collection, array, or such. It is better to use looping methods that brute strength methods.

ActiveSheet.OLEObjects("CmbIssue").Object.list=split(.opentextfile("H:\Excel\Data\Issues.txt").readall,vbcrlf)

Of course with one liner code, you don't have error checking. Do that before the one liner.

Emoncada
10-29-2012, 11:19 AM
Kenneth I am getting a
"Invalid or unqualified reference" on

.opentextfile

Kenneth Hobs
10-29-2012, 12:20 PM
Put that one line of code between the With lines. For multiple references to an object like in a loop, With is more efficient.

Or just:

ActiveSheet.OLEObjects("CmbIssue").Object.list=split(CreateObject("scripting.filescriptingobject").OpenTextfile("H:\Excel\Data\Issues.txt").Readall,vbCrLf)

Emoncada
10-29-2012, 01:14 PM
This is the same error I was getting before.
"ActiveX component can't create object"

I don't understand why.

Emoncada
10-29-2012, 01:41 PM
I even tried it within the With createobject("scripting.filescriptingobject")

Still no go.

Kenneth Hobs
10-29-2012, 02:20 PM
Use FileSystemObject rather than FileScriptingObject. I normally use early binding methods so I can use Itellisense. You can catch this sort of thing easier that way.

ActiveSheet.OLEObjects("CmbIssue").Object.List = Split(CreateObject("Scripting.FileSystemObject").OpenTextfile("H:\Excel\Data\Issues.txt").Readall, vbCrLf)

snb
10-29-2012, 02:26 PM
@KH

Of course 'scripting.filesystemobject' :doh:

I amended the code I posted.

Emoncada
10-30-2012, 08:02 AM
That worked thanks