MrExcel Publishing
Your One Stop for Excel Tips & Solutions

looking up values in a csv file to place in a list box


Posted by steven scaife on September 20, 2001 5:21 AM

I have a userform that when an item is selected populates 4 fileds in a row. What i have done is copied the data froma csv and pasted into a new worksheet in the current document. When the data changes i have to continually cut and paste the new data into the worksheet. is there any way to access the csv files information and link it to the listbox on my form. In the list box properties under the rowsource i am using the following :

"=stocklist!a2:g157" do i have to change this statement. I'm not sure how to reference other spreadsheet documents i only know how to access the sheets in the current document.

any help would be gratefully accepted

cheers

steve


Posted by Damon Ostrander on September 24, 2001 1:23 AM

Hi Steven,

You can read data almost directly from a csv file into a listbox and vice versa using simple file I/O in VBA. To read the data, check on the VBA Open, Input # and Line Input # statements in the VBA helps. To see how to load this data into a listbox, check out the AddItem method of the listbox object in the VBA helps.

When it comes to referencing other "spreadsheet documents", I assume you are referring to other workbooks rather than other spreadsheets within the same workbook? The general form for this is:

"'workbook name.xls'worksheet name!cellrange", which in your case would look something like

"='August Stock.xls'stocklist!a2:g157"

Happy computing.

Damon