GerryS
11-17-2015, 06:01 AM
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
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