Populate Combo Box with Values from CSV File

[Mark]

I have a combo box that I want to use an array of values from the first
column of a CSV file.

Workingfilename = "master.csv"
workinglocation = workingdir & Workingfilename

If IsFileOpen(workinglocation) = True Then

MsgBox ("The file is in use, wait a moment and try again.")
Exit Sub

Else

Workbooks.Open (workinglocation)
On Error Resume Next
a = 1
cnt = 1

Do While Cells(a, 1) <> ""
a = a + 1
cnt = cnt + 1
Loop


Dim myrange As Range

Dim myArray()
Erase myArray 'incase it's already full


myArray = Workbooks("Master.csv").Range(Cells(1, 1), Cells(cnt, 1)).Values

Workbooks("Master.csv").Close True

Getesc.EscDrop.List = myArray

Getesc.Show

End If
End Sub

Any idea why it keeps coming up blank??

 

[rylo]

Hi

I'm pretty sure you need to nominate the sheet for the range.


Code
-------------------
myArray = Workbooks("Master.csv").sheets("master").Range(Cells(1, 1), Cells(cnt, 1)).Value
-------------------


I don't know what your sheet name is so I've defaulted it to master.

HTH

ryl

 

[Dave Peterson]

Untested:

Option Explicit
Sub testme()

Dim WorkingFileName As String
Dim WorkingLocation As String
Dim WorkingDir As String
Dim CSVWks As Worksheet
Dim LastRow As Long
Dim myArray As Variant

WorkingDir = "c:\somestring\"

WorkingFileName = "master.csv"
WorkingLocation = WorkingDir & WorkingFileName

If IsFileOpen(WorkingLocation) = True Then
MsgBox "The file is in use, wait a moment and try again."
Exit Sub
Else
Set CSVWks = Workbooks.Open(Filename:=WorkingLocation).Worksheets(1)
With CSVWks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
myArray = .Range("A1:A" & LastRow).Value
.Parent.Close savechanges:=False
End With

Getesc.EscDrop.List = myArray
Getesc.Show
End If
End Sub

 

[Mark]

Dave you are an "expletive deleted" genious! Thank you!!

 

[Dave Peterson]

Glad it worked for you.

ps. You should stop watching the Sopranos <vbg>!