compile error... method or data member not found

[claven123]

I have a compile error... method or data member not found.

I get this error on ALL the commands I click. In the past I have had no problem with these, been running just fine. Until now.

When I click I get the error and .RANGE is highlighted. This happens on all of the commands I have, all of them.

When I comment out that line, it then sticks, same error on the, !FullNameCPt

I thought it was the excel file, so I made a TESTONE and tried that, same error.


I'm not sure what happened.

Thanks

Dennis

Private Sub CmdOpen1203Insp_Click()
On Error GoTo SubError
Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook
Dim xlWks As Excel.Worksheet
Dim SQLCpt As String
Dim SQLLt As String
Dim SQLYearCur As String
Dim rsCpt As DAO.Recordset
Dim rsLt As DAO.Recordset
Dim rsYearCur As DAO.Recordset
SQLCpt = " SELECT TblMembers.LastName, TblMembers.FirstName, TblMembers.Position, 'Captain' & "" "" & [FirstName] & "" "" & [LastName] AS FullNameCpt " & _
    " FROM TblMembers " & _
    " WHERE TblMembers.Position='Capt [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] ' "
SQLLt = " SELECT TblMembers.LastName, TblMembers.FirstName, TblMembers.Position, 'Lieutenant' & "" "" & [FirstName] & "" "" & [LastName] AS FullNameLt " & _
    " FROM TblMembers " & _
    " WHERE TblMembers.Position='Lt [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] ' "
SQLYearCur = " SELECT 'October' & "" "" & Format(Now(),'yyyy') AS YearCur " & _
    "FROM TblMembers "
Set rsCpt = CurrentDb.OpenRecordset(SQLCpt, dbOpenSnapshot)
Set rsLt = CurrentDb.OpenRecordset(SQLLt, dbOpenSnapshot)
Set rsYearCur = CurrentDb.OpenRecordset(SQLYearCur, dbOpenSnapshot)
Set xlApp = New Excel.Application
'Set xlWkb = xlApp.Workbooks.Open(CurrentProject.Path & "\Master\Trk_Insp_1203.xlsx")
Set xlWkb = xlApp.Workbooks.Open(CurrentProject.Path & "\Master\TESTONE.xlsx")
Set xlWks = xlWkb.Sheets("Oct")
xlApp.Visible = True
    With xlWks
        '[COLOR=#ff0000].Range([/COLOR]"B6").NumberFormat = "mmmm yyyy"
        '.Range("B6").Value = (rsYearCur!YearCur)
        .Range("B9").Value = (rsCp[COLOR=#0000ff]t!FullNameCpt)[/COLOR]
        .Range("T9").Value = (rsLt!FullNameLt)
    End With
SubExit:
On Error Resume Next

 

[xenou]

You have probably lost the reference to Excel. Possibly because of whatever or because a new version of Excel was installed.

You can very likely get by with late binding anyway, otherwise you have to re-establish the reference to Excel in your project.

Private Sub CmdOpen1203Insp_Click()
On Error GoTo SubError
[COLOR="#FF0000"][B]Dim xlApp As Object 'Excel.Application
Dim xlWkb As Object 'Excel.Workbook
Dim xlWks As Object 'Excel.Worksheet[/B][/COLOR]
Dim SQLCpt As String
Dim SQLLt As String
Dim SQLYearCur As String
Dim rsCpt As DAO.Recordset
Dim rsLt As DAO.Recordset
Dim rsYearCur As DAO.Recordset
SQLCpt = " SELECT TblMembers.LastName, TblMembers.FirstName, TblMembers.Position, 'Captain' & "" "" & [FirstName] & "" "" & [LastName] AS FullNameCpt " & _
    " FROM TblMembers " & _
    " WHERE TblMembers.Position='Capt [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL]  ' "
SQLLt = " SELECT TblMembers.LastName, TblMembers.FirstName, TblMembers.Position, 'Lieutenant' & "" "" & [FirstName] & "" "" & [LastName] AS FullNameLt " & _
    " FROM TblMembers " & _
    " WHERE TblMembers.Position='Lt [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL]  ' "
SQLYearCur = " SELECT 'October' & "" "" & Format(Now(),'yyyy') AS YearCur " & _
    "FROM TblMembers "
Set rsCpt = CurrentDb.OpenRecordset(SQLCpt, dbOpenSnapshot)
Set rsLt = CurrentDb.OpenRecordset(SQLLt, dbOpenSnapshot)
Set rsYearCur = CurrentDb.OpenRecordset(SQLYearCur, dbOpenSnapshot)
[COLOR="#FF0000"][B]Set xlApp = CreateObject("Excel.Application") 'New Excel.Application[/B][/COLOR]
'Set xlWkb = xlApp.Workbooks.Open(CurrentProject.Path & "\Master\Trk_Insp_1203.xlsx")
Set xlWkb = xlApp.Workbooks.Open(CurrentProject.Path & "\Master\TESTONE.xlsx")
Set xlWks = xlWkb.Sheets("Oct")
xlApp.Visible = True
    With xlWks
        '.Range("B6").NumberFormat = "mmmm yyyy"
        '.Range("B6").Value = (rsYearCur!YearCur)
        .Range("B9").Value = (rsCpt!FullNameCpt)
        .Range("T9").Value = (rsLt!FullNameLt)
    End With
SubExit:
On Error Resume Next

 

[claven123]

Interestingly, I was able to get a previously saved version from my dropbox and it works perfectly (I can roll it back). And the latest saved one, does not.

I think another user used it on his laptop..... but I'm sure both are office 360.

I thought that if there was a chance that someone else would use access and interact with excel/word.... due to different possible different versions of them, I had to use late binding, right? Or, do I have it wrong?

D

 

[xenou]

You are correct but maybe thinking about it upside down - you should have less problems using late binding if you are sharing the program with users that have different versions of Excel. But your original code has early binding.

 

[Norie]

I know you have this sorted but did you ever check under Tools>References.. when you had the problem?