topic Re: Read Excel in VBA Forum

Read Excel

Anonymous — Sat, 29 Jul 2006 12:14:34 GMT

I have some code which should read an excel file. When I use the buttons my Listbox remains empty. I have loaded the Excel Object Library. What is wrong?

Private Sub CommandButton1_Click()
Dim excelApp As Excel.Application
Dim wbkObj As Workbook
Dim shtObj As WorkSheet

On Error Resume Next
UserForm1.Hide
Err.Clear
Set excelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Cannot start Excel", vbExclamation
End
End If
End If
excelApp.Visible = True
Set wbkObj = excelApp.Workbooks.Open("C:\Bob\Test.xls")
Set shtObj = excelApp.Worksheets(1)
UserForm1.Show
End Sub

Private Sub CommandButton2_Click()
Dim excelApp As Excel.Application
Dim wbkObj As Workbook
Dim shtObj As WorkSheet
Dim i As Integer
Dim txt As String
On Error Resume Next
UserForm1.Hide
Err.Clear
Set excelApp = GetObject(, "Excel.Appication")
excelApp.Visible = True
Set shtObj = excelApp.Worksheets(1)
ListBox1.Clear
i = 1
txt = shtObj.Cells(i, 2)
Do While txt <> ""
ListBox1.AddItem txt
i = i + 1
txt = shtObj.Cells(i, 2)
Loop
UserForm1.Show

End Sub

Re: Read Excel

Anonymous — Sat, 29 Jul 2006 16:48:51 GMT

Hi Numpty,

Sub CommandButton2_Click

Set excelApp = GetObject(, "Excel.Appication")

Application misspelled

A Caddie.

Re: Read Excel

Anonymous — Mon, 31 Jul 2006 01:46:58 GMT

And the reason you didn't see the error is the "On Error Resume Next" at the
top of CommandButton2_Click(). It is the root of much evil. Get rid of it,
or at least reset the error handler immediately after.you've checked for the
error.
--
John Goodfellow
irtfnm
use john at goodfellowassoc dot com

wrote in message news:5252497@discussion.autodesk.com...
I have some code which should read an excel file. When I use the buttons my
Listbox remains empty. I have loaded the Excel Object Library. What is
wrong?

Private Sub CommandButton1_Click()
Dim excelApp As Excel.Application
Dim wbkObj As Workbook
Dim shtObj As WorkSheet

On Error Resume Next
UserForm1.Hide
Err.Clear
Set excelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Cannot start Excel", vbExclamation
End
End If
End If
excelApp.Visible = True
Set wbkObj = excelApp.Workbooks.Open("C:\Bob\Test.xls")
Set shtObj = excelApp.Worksheets(1)
UserForm1.Show
End Sub

Private Sub CommandButton2_Click()
Dim excelApp As Excel.Application
Dim wbkObj As Workbook
Dim shtObj As WorkSheet
Dim i As Integer
Dim txt As String
On Error Resume Next
UserForm1.Hide
Err.Clear
Set excelApp = GetObject(, "Excel.Appication")
excelApp.Visible = True
Set shtObj = excelApp.Worksheets(1)
ListBox1.Clear
i = 1
txt = shtObj.Cells(i, 2)
Do While txt <> ""
ListBox1.AddItem txt
i = i + 1
txt = shtObj.Cells(i, 2)
Loop
UserForm1.Show

End Sub

Re: Read Excel

Anonymous — Mon, 31 Jul 2006 07:38:52 GMT

Thank you both. I would not have spotted the error AND you will probably save me future effort by being cautious with the "On Error Resume Next". Your help is much appreciated.

Re: Read Excel

Anonymous — Mon, 31 Jul 2006 10:50:06 GMT

Hi
Reading values from Excel is always problematic.you can make use of databse, unless your application demands excel ..
well i have found no mistake in your code but i dint understand why did u call excel in commnad button 1 click event by seeing your code i feel that u r just openning excel file in commandbuttton1 and agian openning excel and reading cell values in command button 2.Can you explain me wt r u doing in command button 1.

Re: Read Excel

Anonymous — Mon, 31 Jul 2006 11:01:50 GMT

Hi Benny
I am fairly new to VBA. I tried to simplfy the read from excel function by splitting the program into two parts - one to open excel and one to read from excel (see attached).

I really don't like the program opening excel and would much prefer it to just read the values. I am think about using a txt file to hold the data. I intend giving this program to many users who may have earlier copies of excel etc. Your thoughts on this would be helpful.

Re: Read Excel

Anonymous — Mon, 31 Jul 2006 11:17:52 GMT

Hi
try this code its working for me.

I had user form and 1 command button and 1 listbox.
I had prepared a excel document j.xls with names in column 2.

Try out this,it should work....Gud luck

Private Sub CommandButton1_Click()
Dim Excel As Object
Dim excelSheet As Object

Set Excel = CreateObject("Excel.Application")

Excel.Workbooks.Open FileName:="C:\j.xls", Editable:=True

Excel.Sheets("Sheet1").Select
Set excelSheet = Excel.ActiveWorkbook.Sheets("Sheet1")
Excel.Visible = True

Dim i As Integer
Dim txt As String
ListBox1.Clear
For i = 1 To 10
txt = excelSheet.Cells(i, 2).Value
If txt = "" Then Exit Sub
ListBox1.AddItem txt
Next i

End Sub