error 438

[Guest]

hi,
i trying to read from excel file, i get error - "Object doesnt support this
property or method"

Private Sub import_data_Click()
Dim xlApp As Object
Dim xlSht As Excel.Worksheet
Dim xlRng As Excel.Range
Dim v_test As String

Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
xlApp.Workbooks.Open ("c:\test.xls")
xlSht = xlApp.Sheet(1) <<<------- here
xlRng = xlSht.Cells(1, 1)
v_test = xlRng.Value
MsgBox ("test" & v_test)
xlApp.Workbooks.Close
End Sub

thanks.

 

[Graham Mandeno]

There are two problems here:

First, the collection of sheets in a workbook is called "Sheets", not
"Sheet".

Second, when you assign an object variable, you must use "Set".

Also, xlApp.Sheets refers to the sheets in the *active* workbook. It is
better to be more specific and use a Workbook object variable for the
workbook you have opened.

So, your code should look like this:

Dim xlApp As Object
Dim xlWkb As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim xlRng As Excel.Range
Dim v_test As String

Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
Set xlWkb = xlApp.Workbooks.Open ("c:\test.xls")
Set xlSht = xlWkb.Sheets(1) <<<------- here
Set xlRng = xlSht.Cells(1, 1)
v_test = xlRng.Value
MsgBox ("test" & v_test)
xlWkb.Close

 

[Guest]

thanks you great

There are two problems here:

First, the collection of sheets in a workbook is called "Sheets", not
"Sheet".

Second, when you assign an object variable, you must use "Set".

Also, xlApp.Sheets refers to the sheets in the *active* workbook. It is
better to be more specific and use a Workbook object variable for the
workbook you have opened.

So, your code should look like this:

Dim xlApp As Object
Dim xlWkb As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim xlRng As Excel.Range
Dim v_test As String

Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
Set xlWkb = xlApp.Workbooks.Open ("c:\test.xls")
Set xlSht = xlWkb.Sheets(1) <<<------- here
Set xlRng = xlSht.Cells(1, 1)
v_test = xlRng.Value
MsgBox ("test" & v_test)
xlWkb.Close

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

hi,
i trying to read from excel file, i get error - "Object doesnt support
this
property or method"

Private Sub import_data_Click()
Dim xlApp As Object
Dim xlSht As Excel.Worksheet
Dim xlRng As Excel.Range
Dim v_test As String

Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
xlApp.Workbooks.Open ("c:\test.xls")
xlSht = xlApp.Sheet(1) <<<------- here
xlRng = xlSht.Cells(1, 1)
v_test = xlRng.Value
MsgBox ("test" & v_test)
xlApp.Workbooks.Close
End Sub

thanks.