worksheet.cell.text method throws error 438

[Arafat]

Hello All,

I am trying to use late binding in creating a worksheet and then passing the values from the opened workbook but i get "error 438 : object doesn't support this method or property" in this line

Public Sub CompareHeader(shtsExcelNew As Object, shtsExcelDiff As Object)
dim strDSAbnBl as string
Set shtsExcelNew = CreateObject("Excel.Sheet")
Set shtsExcelDiff = CreateObject("Excel.Sheet")

strDSAbnBl = shtsExcelNew(1).Cells(3, 7).Text --- I get Error 438 in this line

end sub

I tried changing it to range("G3").value but it didn't help.

can someone point me where am i doing it wrong ?

 

[Norie]

You can't create an Excel sheet on it's own so where you have CreateObject("Excel.Sheet") you will be creating a new workbook with a single sheet.

If you want to refer to that single sheet you need to use the Sheets collection of the workbook.

For example.

strDSAbnBl = shtsExcelNew.Sheets(1).Cells(3, 7).Text

By the way, where are you running this code?

 

[Arafat]

Hello Norie,

Thank you for the reply, I tried this and i didn't get the error but also it returned empty value.

I am running this code to do some validation between workbooks. This code was written earlier in Early Binding but now we need to convert it to late binding to avoid MS Office version issues. This code is getting executed from MS Access VBA modules.

 

[Norie]

The code you posted creates empty workbooks, with empty sheets.

How are you calling CompareHeader and what are you passing to the sub?

If you are passing references to existing sheets to the sub there's no need to use CreateObject.

 

[Arafat]

Hello Norie, We are calling this from another function and which will open the excel file and add workbooks, When i removed the create object i got the same error, we are passing this parameters to the sub

call CompareHeader(.Workbooks(strAbnahmeblattNewShort).sheets, .Workbooks(strAbnahmeblattDiffShort).sheets)

 

[Norie]

Why don't you pass either the specific sheets you want to compare the headers of to the function or the workbook(s) containing the sheets you want to compare?

 

[Arafat]

I will try that method, but just curious to know why it worked in early binding and in late binding it fails ?

 

[Norie]

To be honest I don't see how it would work in early-binding, but then again I've only seen partial code.

 

[Arafat]

Hello Norie,

Thank you for your suggestion and Help, I passed the workbook names as argument and then used the the sheets and it worked.

call CompareHeader(.Workbooks(strAbnahmeblattNewShort).sheets, .Workbooks(strAbnahmeblattDiffShort).sheets)

---TO---

call CompareHeader(.Workbooks(strAbnahmeblattNewShort), .Workbooks(strAbnahmeblattDiffShort))