Populate Combo Box with Values from CSV File
 

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??

Populate Combo Box with Values from CSV File
 

Hi

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


Code:
--------------------
myArray = Workbooks("Master.csv").sheets("master").Range(Cel ls(1, 1), Cells(cnt, 1)).Values
--------------------


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

HTH

rylo


--
rylo
------------------------------------------------------------------------
rylo's Profile: http://www.thecodecage.com/forumz/member.php?userid=28
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=29399

Populate Combo Box with Values from CSV File
 

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).Workshee ts(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 wrote:

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??

--

Dave Peterson

Populate Combo Box with Values from CSV File
 

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

"Dave Peterson" wrote:

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).Workshee ts(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 wrote:

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??

--

Dave Peterson

Populate Combo Box with Values from CSV File
 

Glad it worked for you.

ps. You should stop watching the Sopranos <vbg!

Mark wrote:

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

"Dave Peterson" wrote:

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).Workshee ts(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 wrote:

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??

--

Dave Peterson


--

Dave Peterson