object does not support this property or method 1

[davyre]

Hi,

I have this code

Private Function insertBOM(strInputFileName As String)

Dim itemNo As Long
Dim partNumber As String
Dim description As String
Dim qty As Long
Dim allBlank As Long
Dim excelLine As Long

Dim insTblAssyQry As String
Dim insTblPartQry As String
Dim insTblSubAssyQry As String

Dim excelApp As Excel.Application
Dim workbook As Excel.workbook
Dim worksheet As Excel.worksheet
Dim dbs As Database

Set excelApp = CreateObject("Excel.application")
Set workbook = GetObject(strInputFileName)
Set worksheet = workbook.Worksheets("Sheet1")
Set dbs = CurrentDb

allBlank = 0
excelLine = 1

Do While allBlank < 5
    partNumber = worksheet.Cells(excelLine, "B")
    If partNumber = "" Then
        allBlank = allBlank + 1
        excelLine = excelLine + 1
    Else
        allBlank = 0
        If worksheet.Cells(excelLine, "B").interior.colorindex = 3 And worksheet.Cells(excelLine, "A") <> "" Then 'if RED with UnitNO
            itemNo = worksheet.Cells(excelLine, "A")
            description = worksheet.Cells(excelLine, "C")
            insTblAssyQry = "INSERT INTO TblASSYTEST([Number],[AssyDesc]) " _
                          & "VALUES('" & partNumber & "','" & description & "')"
            MsgBox (insTblAssyQry)
            DoCmd.SetWarnings False
            DoCmd.RunSQL insTblAssyQry
            DoCmd.SetWarnings True
        ElseIf worksheet.Cells(excelLine, "B").interior.colorindex = 3 And worksheet.Cells(excelLine, "A") = "" Then 'if RED without UnitNo
            description = worksheet.Cells(excelLine, "C")
            insTblPartQry = "INSERT INTO TblPARTTEST([PartNumber],[PartDesc]) " _
                          & "VALUES('" & partNumber & "','" & description & "')"
            MsgBox (insTblPartQry)
            DoCmd.SetWarnings False
            DoCmd.RunSQL insTblPartQry
            DoCmd.SetWarnings True
            '=================for TblAssyPart======================
            Dim ctr As Long
            Dim setCell As String
            ctr = 1
           [highlight #FCE94F] worksheet("Sheet1").Cells(excelLine, "A").Select[/highlight]
            setCell = ActiveCell
            Do Until setCell <> ""
                setCell = ActiveCell.Offset(-ctr, 0)
                ctr = ctr + 1
            Loop
            msgbox(Activesheet.cells(excelLine-ctr,"B").value)
            '======================================================
..
..
..

The yellow blocked code was the error source, saying "Object does not support this property or method". I have added Microsoft Excel 12.0 Object Library references in Tools>References.
What I want to achieve in the "for TblAssyPart" block, is to get the cell B value in the Excel file where cell A value is not empty (thus <> "" at the Do Until setCell). I guess my code is not proper (have not done VBA for excel file for a long time). Can anyone give me hint to solve my problem? Thanks

 

[PHV]

First, don't use variable named as referenced object (e.g. worksheet)
Second, use full qualified names (i.e. excelApp.ActiveCell)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

Hi, PHV
Thanks for the advice. But I do not understand with the first advice (Don't use variable named as referenced object). I did not make any variable named worksheet. I have tried

Sheets("Sheet1").Cells(ExcelLine, "A").Select

or

activeworkbook.worksheet("Sheet1").Cells(ExcelLine,"A").Select

none of them worked. What do you suggest? THanks

 

[PHV]

I did not make any variable named worksheet
Set worksheet = workbook.Worksheets("Sheet1")

What do you suggest?
[!]excelApp.[/!]ActiveWorkbook.Worksheet[!]s[/!]("Sheet1").Cells(ExcelLine,"A")

Anyway, I'd open the file like this:
Set excelApp = CreateObject("Excel.Application")
Set wkb = excelApp.Workbooks.Open(strInputFileName)


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

excelApp.ActiveWorkbook.Worksheets("Sheet1").Cells(ExcelLine,"A")

why do I get error message "Object does not support this property or method" ?
I tried to assign the cell ExcelLine,"A" to a variable X, then this error message appears.

I just found that instead of doing that, using Range is more convenient :
X=Range("B2") -> worked effortlessly
but then when I tried to use variables to change the cell coordinates (i.e range(excelLine,2)), it does not work.

 

[PHV]

What about this to calculate ctr ?

ctr = 1
Do Until worksheet.Cells(excelLine, "A").Offset(-ctr, 0).Value <> ""
  ctr = ctr + 1
Loop

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

yeah, thats what I was doing and it worked and you posted the same! Thanks

 

[davyre]

PHV, one more question,
do I need to set those variables to nothing?
i.e

set excelApp=nothing
set dbs=nothing

I dont know about this, but the guy who worked on this database before me always do this. Thanks

 

[SkipVought]

Yes

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[davyre]

thanks Skip. What else do I need to set to nothing? Any theory/reason behind this? THanks