style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<Elie911> wrote in messagehello
href="news:6183184@discussion.autodesk.com">news:6183184@discussion.autodesk.com...
, here is the code I used for filling a multicolumn listbox from an excel
workbook :
Private Sub UserForm_initialize()
size=2>
Dim app As excel.Application
Dim wbook As
excel.Workbook
Dim wsheet As excel.Worksheet
Dim rg As
excel.Range
Set app = excel.Application
this line should be (notice the "New"
keyword):
Set app=New excel Application
app.Visible=True
size=2>
size=2>
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
If I do Excel automation, I alway make it visible
so that user know something is going on and if my code run int error, I do not
end up with a hidden Excel app left behind in Task Manager.
Of course you can try
GetObject(,"Excel.Application) to see if there is already an Excel app
instance running, so that you do not have to "New" it. Make sure you have
proper error handling
size=2>
Set wbook =
app.Workbooks.Open("C:\Materials.xlsx")
Set wsheet =
wbook.Worksheets(1)
Set rg = wsheet.Range(A1, D107)
Dim
iCol As Integer
iCol = 2
Dim iRow As Integer
For
iRow = 1 To 10 Step iRow + 1
ListBox1 = wsheet.Cells(iRow,
1).Value
Next
you need to make sure to close the workbook and
quit the Excel app
Set rg=Nothing
Set wsheet=Nothing
wbook.Close
Set wbook=Nothing
app.Quit
End Sub
but an error is appearing :
Run-time error '1004':
Method 'Range' of object
'_worksheet' failed
plz help !!!
Have you step through the code in debugging? You
should be easily identify the offending line of
code.