Sorry, had a problem with posting … there should only be this entry.
I’ve searched various forums and found the error message, but the solutions were already coded. A user Excel macro was rewritten to add new features. The macro MAIN is the driver that calls other user Excel macros, but no macro is large. The user macros are grouped by functionality into one of 11 code modules.
When Access user macro executes the:
Set xlBook = xlApp.Workbooks.Open(Temploc & SheetName)
the following error message occurs:
Run-time error ‘-2147417851 (80010105)’:
Method ‘Open’ of object ‘Workbooks’ failed
I can run the Access queries and copy & paste into the workbook and manually execute the MAIN macro with no problems. I can change the workbook to the previous user version and run the old macro with no problem.
What can cause Access to have problems opening a workbook? Is there a size limit of the workbook? Specific lines of code? Remember, I don’t even execute my Excel user macro … the problem is with opening the workbook. Is there anything I can do to isolate the nature of the problem further?
Both Excel and Access are 2003 SP2 with Visual Basic level of 6.3.
In advance, thanks for any help folks provide.
Below is the Access user macro that has the problem. Highlighted in bold red is the line of code that the error occurs on. Highlighted in bold green and underline is the where I switch from my previous workbook (v10) to my new workbook (v11).
Public Function MonthlyMetricsBatchAutomation()
Dim cnn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cnn = CurrentProject.Connection
Dim TempLoc As String
Dim SheetName As String
Dim FileOut As String
Dim DirOut As String
Dim DirOut2 As String
Dim RetVal As String
Dim ExcelTemplateName As String
Dim ExcelSystemName As String
ExcelTemplateName = "Monthly Metrics Report Template v5.xls"
ExcelSystemName = "Monthly Metrics Report System v11.xls"
Call ResetSelects
TempLoc = "C:\Temp\"
If Dir(TempLoc) <> "" Then
Kill (TempLoc & "*.*")
End If
DirOut = "L:\InetPub\wwwroot\DCC\Reports\Monthly\"
If Dir(DirOut) <> "" Then
Kill (DirOut & "*.*")
End If
DirOut2 = "L:\InetPub\wwwroot\DCC\Reports\Master Metrics\"
If Dir(DirOut2) <> "" Then
Kill (DirOut2 & "*.*")
End If
Call TwelveMonthsThroughLastMonth
rs.Open "SELECT DISTINCT dbo_Groups.MonthlyReportingGroup " _
& "FROM dbo_Groups " _
& "WHERE (((dbo_Groups.MonthlyReportingGroup) <> 'N/A')) " _
& "ORDER BY dbo_Groups.MonthlyReportingGroup;", cnn, adOpenDynamic, adLockPessimistic
While Not rs.EOF
Forms!Main!MonthlyFAGroupSelect = rs.Fields(0)
'Copy Master Templates
FileCopy "C:\TEMP\BaseTemplate\" & ExcelTemplateName, "C:\Temp\" & ExcelTemplateName
FileCopy "C:\TEMP\BaseTemplate\" & ExcelSystemName, "C:\Temp\" & ExcelSystemName
SheetName = ExcelSystemName
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ReportFilterOptions", TempLoc & SheetName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MonthlyMetrics-Detail", TempLoc & SheetName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MonthlyMetrics-Tasks", TempLoc & SheetName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MonthlyMetrics-Users", TempLoc & SheetName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MonthlyMetrics-Projects", TempLoc & SheetName, True
Dim StartingColumn As Long
Dim StartingRow As Long
Dim FinalColumn As Long
Dim FinalRow As Long
Dim I As Integer
Dim xlApp As excel.Application
Dim xlBook As excel.Workbook
Dim xlSheet As excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(TempLoc & SheetName)
xlApp.Visible = True
' xlApp.Application.Run "'Monthly Metrics Report System v11.xls'!Main"
xlApp.Application.Run "'" & ExcelSystemName & "'!Main"
xlBook.Close
xlApp.Quit
rs.MoveNext
Wend
rs.Close
Kill "C:\Temp\" & ExcelTemplateName
Kill "C:\Temp\" & ExcelSystemName
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile TempLoc & "*.xls", DirOut2
fs.MoveFile TempLoc & "*.xls", DirOut
If DatePart("d", Date) < 5 Then
RetVal = Shell("C:\DCCMonthly1.bat", 1)
Else
RetVal = Shell("C:\DCCMonthly2.bat", 1)
End If
SkipIt:
End Function
I’ve searched various forums and found the error message, but the solutions were already coded. A user Excel macro was rewritten to add new features. The macro MAIN is the driver that calls other user Excel macros, but no macro is large. The user macros are grouped by functionality into one of 11 code modules.
When Access user macro executes the:
Set xlBook = xlApp.Workbooks.Open(Temploc & SheetName)
the following error message occurs:
Run-time error ‘-2147417851 (80010105)’:
Method ‘Open’ of object ‘Workbooks’ failed
I can run the Access queries and copy & paste into the workbook and manually execute the MAIN macro with no problems. I can change the workbook to the previous user version and run the old macro with no problem.
What can cause Access to have problems opening a workbook? Is there a size limit of the workbook? Specific lines of code? Remember, I don’t even execute my Excel user macro … the problem is with opening the workbook. Is there anything I can do to isolate the nature of the problem further?
Both Excel and Access are 2003 SP2 with Visual Basic level of 6.3.
In advance, thanks for any help folks provide.
Below is the Access user macro that has the problem. Highlighted in bold red is the line of code that the error occurs on. Highlighted in bold green and underline is the where I switch from my previous workbook (v10) to my new workbook (v11).
Public Function MonthlyMetricsBatchAutomation()
Dim cnn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cnn = CurrentProject.Connection
Dim TempLoc As String
Dim SheetName As String
Dim FileOut As String
Dim DirOut As String
Dim DirOut2 As String
Dim RetVal As String
Dim ExcelTemplateName As String
Dim ExcelSystemName As String
ExcelTemplateName = "Monthly Metrics Report Template v5.xls"
ExcelSystemName = "Monthly Metrics Report System v11.xls"
Call ResetSelects
TempLoc = "C:\Temp\"
If Dir(TempLoc) <> "" Then
Kill (TempLoc & "*.*")
End If
DirOut = "L:\InetPub\wwwroot\DCC\Reports\Monthly\"
If Dir(DirOut) <> "" Then
Kill (DirOut & "*.*")
End If
DirOut2 = "L:\InetPub\wwwroot\DCC\Reports\Master Metrics\"
If Dir(DirOut2) <> "" Then
Kill (DirOut2 & "*.*")
End If
Call TwelveMonthsThroughLastMonth
rs.Open "SELECT DISTINCT dbo_Groups.MonthlyReportingGroup " _
& "FROM dbo_Groups " _
& "WHERE (((dbo_Groups.MonthlyReportingGroup) <> 'N/A')) " _
& "ORDER BY dbo_Groups.MonthlyReportingGroup;", cnn, adOpenDynamic, adLockPessimistic
While Not rs.EOF
Forms!Main!MonthlyFAGroupSelect = rs.Fields(0)
'Copy Master Templates
FileCopy "C:\TEMP\BaseTemplate\" & ExcelTemplateName, "C:\Temp\" & ExcelTemplateName
FileCopy "C:\TEMP\BaseTemplate\" & ExcelSystemName, "C:\Temp\" & ExcelSystemName
SheetName = ExcelSystemName
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ReportFilterOptions", TempLoc & SheetName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MonthlyMetrics-Detail", TempLoc & SheetName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MonthlyMetrics-Tasks", TempLoc & SheetName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MonthlyMetrics-Users", TempLoc & SheetName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MonthlyMetrics-Projects", TempLoc & SheetName, True
Dim StartingColumn As Long
Dim StartingRow As Long
Dim FinalColumn As Long
Dim FinalRow As Long
Dim I As Integer
Dim xlApp As excel.Application
Dim xlBook As excel.Workbook
Dim xlSheet As excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(TempLoc & SheetName)
xlApp.Visible = True
' xlApp.Application.Run "'Monthly Metrics Report System v11.xls'!Main"
xlApp.Application.Run "'" & ExcelSystemName & "'!Main"
xlBook.Close
xlApp.Quit
rs.MoveNext
Wend
rs.Close
Kill "C:\Temp\" & ExcelTemplateName
Kill "C:\Temp\" & ExcelSystemName
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile TempLoc & "*.xls", DirOut2
fs.MoveFile TempLoc & "*.xls", DirOut
If DatePart("d", Date) < 5 Then
RetVal = Shell("C:\DCCMonthly1.bat", 1)
Else
RetVal = Shell("C:\DCCMonthly2.bat", 1)
End If
SkipIt:
End Function