I have a range in an excel document which I need to extract into a listbox. I can do this through .txt file, but really would prefer to source the information from excel. Any ideas?
Many tks for yr help in advance.
Best Rds.
Printable View
I have a range in an excel document which I need to extract into a listbox. I can do this through .txt file, but really would prefer to source the information from excel. Any ideas?
Many tks for yr help in advance.
Best Rds.
You'll need to...
- Start a new vb project
- Add 1 listbox and 1 command button to the form
- Goto the project menu > references option & select the Microsoft Excel Library line
- Create an excel file with the first 10 cells A1 to A10 with data in, then save this file as "C:\file.xls"
Code:Private Sub Command1_Click()
Dim objXLApp As Excel.Application
Dim intLoopCounter As Integer
Set objXLApp = New Excel.Application
With objXLApp
.Workbooks.Open "C:\File.xls"
.Workbooks(1).Worksheets(1).Select
For intLoopCounter = 1 To 10
List1.AddItem .Range("A" & intLoopCounter)
Next intLoopCounter
.Workbooks(1).Close False
.Quit
End With
Set objXLApp = Nothing
End Sub
Tks for the help..... I understand this code. And it works fine assuming the list in excel doesn't grow.
Is there a way to display a range which has been "named"
Example:
Range("Test")
Where Range("test") = A1 to A20
Tks for yr help!
You could loop through until you come to a cell that is blank.
i haven't check this so the syntax may be wrong but the idea will work!!!!Code:With objXLApp
.Workbooks.Open "C:\File.xls"
.Workbooks(1).Worksheets(1).Select
count = 1
while strComp(.cells(count,1),"")<>0
List1.AddItem .Range("A" & count)
count = count + 1
wend
end with
I tried yr code I get teh following error msg:
Comile error:
Function or interface marked as restricited, or the function uses an Automation type not supported in Visual Basics.
Any ideas?
Tks for yr help.
put
dim count as integer
above the count = 1 line
Sorry, only just noticed you replied to this - if the suggestions above don't work, you should be able to use this one:
Code:Private Sub Command1_Click()
Dim objXLApp As Excel.Application
Dim intLoopCounter As Integer
Set objXLApp = New Excel.Application
With objXLApp
.Workbooks.Open "C:\File.xls"
.Workbooks(1).Worksheets(1).Select
For intLoopCounter = 1 To CInt(.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row)
List1.AddItem .Range("A" & intLoopCounter)
Next intLoopCounter
.Workbooks(1).Close False
.Quit
End With
Set objXLApp = Nothing
End Sub
Have been following this thread as I'm doing something similar; however, I'm having a problem listing a range. Please see "not working" comment in code below.
Thanks for any help offered.
VB Code:
Private Sub Command1_Click() Dim objXLApp As Excel.Application Dim intRowCounter As Integer Set objXLApp = New Excel.Application With objXLApp .Workbooks.Open "C:\File.xls" .Workbooks(1).Worksheets(1).Select For intRowCounter = 1 To 25 'Test for empty cell in column 2 If StrComp(.Cells(intRowCounter, 2), "") <> 0 Then List1.AddItem .Range("A" & intRowCounter) 'Not working: List1.AddItem .Range("A" & intRowCounter, "C" & intRowCounter) End If: Next intRowCounter .Workbooks(1).Close False .Quit End With Set objXLApp = Nothing End Sub
To specify a range, you have to use this format - A1:C1 rather than the comma, try altering the commented line to this:
VB Code:
List1.AddItem .Range("A" & intRowCounter & ":C" & intRowCounter)
try using this ... your list box should be named "list"
Private Sub UserForm_Initialize()
Dim lngRow As Long
Dim strTxt As String
lngRow = 1
Do Until IsEmpty(Cells(lngRow, 1))
strTxt = strTxt & Cells(lngRow, 1) & vbLf
lngRow = lngRow + 1
Loop
list.Text = Left(strTxt, Len(strTxt) - 1)
End Sub
Dim objXLApp As Excel.ApplicationQuote:
Originally Posted by Dandlv
I have paste the entire code and also created a button and a list box on the form as well as created the desired tables in the excel file.
on the buttons click event the application gives me this error
" User define type not defined " for Dim objXLApp As Excel.Application
Welcome to VBForums :wave:
You haven't added the reference, as specified at the top of post #2.
You may find my Excel Tutorial (link in my signature) useful.
Hey Alex,
I am unable to find the "Microsoft Excel Library" in the Project emnu. Any other suggestions.
Thanks and Regards,
Gideon.
to add a reference in VBA, menu > tools > referencesQuote:
I am unable to find the "Microsoft Excel Library" in the Project emnu. Any other suggestions.
vb6 is different, there it would be in the project menu
but if you are doing this in excel you do not need to do
if you are using late binding you do not need to do
if you have a question start a new thread, instead of reserrecting one that was started in 2003, and ask a full question with information about what program or application you are writing your code in