Stepping through cells in a range

[GerryS]

This code was originally written in Office 2003 and has worked for a year or so. All our PC's have recently been changed for W7 with Office 2007. It runs in a runtime Access application and is intended to open an Excel workbook, extract and manipulate some information and save it in the database. Microsoft Excel 12.0 Object Library is included in the references. The code executes without problems (other than being a little slow) in the full Access environment, but when packaged with AccessRuntime and distributed to my colleagues, it falls over on the line "For Each clSite In raSources". clSite was originally dim'd as Object, but I have also tried Cells, Range and Variant. raSources is dim'd as Excel.Range. The error message when it crashes is "The expression On-Click that you entered as the event property setting produced the following error: Type mismatch." (I have a jpg). Ellipses in the code indicate missing lines that I don't believe relevant.


Set oXLApp = CreateObject("excel.application")
oXLApp.Visible = True
Set oXLData = oXLApp.Workbooks.Open(fd.SelectedItems(1))
Set fd = Nothing

Set oXLEntry = oXLData.Worksheets("Data Entry")
Set oXLNames = oXLData.Worksheets("Site_Names")

...
oXLApp.WindowState = xlMinimized 'Better to see database than spreadsheet

oXLNames.Activate 'See warning above, just below Dims
Set raSitesUnScr = oXLNames.Range(oXLNames.Cells(7, 2), oXLNames.Cells(7, 2).End(xlDown)) 'changed at v10.01

oXLEntry.Activate
Set raSources = oXLEntry.Range(oXLEntry.Cells(2, 1), oXLEntry.Cells(2, 1).End(xlDown)) 'changed at v10.01

Set rstTestHist = dbRQL.OpenRecordset("TestHist", dbOpenDynaset)

MsgBox "Before outer loop", vbInformation + vbOKOnly, "Diag"

For Each clSite In raSources

MsgBox "Start of outer loop", vbInformation + vbOKOnly, "Diag"

If clSite.Value <> "Site_Names" Then
For Each clSiteNmU In raSitesUnScr
If clSite = clSiteNmU Then
stSite = clSiteNmU.Offset(0, -1)
...
Next clSiteNmU
End If
Next clSite

[Bob Phillips]

xlDown is an Excel constant, it doesn't exist in Axcess. Try using -4121 or adding your own constant

Const xlDown As Long = -4121

[GerryS]

Thanks XLD I will play around with that, but the "Microsoft Excel 12.0 Object Library" is referenced in the project and xlDown does appear in the object browser in consequence. - unless the library attachment doesn't carry across to the to the accdr, in which case, I'd have expected many more problems...