I am running a method using a Access form to retrieve data from SQL to provide a report in Excel. From what I have found in my queries on the internet, this seems to be a known problem. Has anyone found a fix for this.
“-2147467259 Method ‘CopyfromRecordset’ of object ‘Range’ failed.
Below is the function that I am running.
Private Function ExportReliabilityToExcel()
On Error GoTo ProcessError
Dim rExportRecordset As ADODB.Recordset
Dim oExcel As Excel.Application
Dim lRow As Long
Dim lCol As Long
DoCmd.Hourglass True
Set rExportRecordset = New ADODB.Recordset
rExportRecordset.Open sRepairOrderSelection, CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText
If rExportRecordset.EOF Then
DoCmd.Hourglass False
MsgBox “No data was found to generate export.”, vbExclamation, conApplicationTitle
Set rExportRecordset = Nothing
DoCmd.Close
Exit Function
End If
Set oExcel = New Excel.Application
oExcel.SheetsInNewWorkbook = 1
oExcel.Workbooks.Add
oExcel.ActiveWorkbook.Worksheets(1).Activate
Me.ProgressBar.Value = 0
Me.ProgressBar.Min = 0
Me.ProgressBar.Max = rExportRecordset.RecordCount
Me.ProgressBar.Value = (rExportRecordset.RecordCount * 0.25)
For lCol = 1 To 14
oExcel.ActiveSheet.Cells(1, lCol).Value = InsertSpaceIntoString(rExportRecordset.Fields(lCol – 1).Name)
oExcel.ActiveSheet.Cells(1, lCol).Interior.ColorIndex = 15
Next
Me.ProgressBar.Value = Me.ProgressBar.Value + (rExportRecordset.RecordCount * 0.25)
oExcel.ActiveSheet.Cells(2, 1).CopyFromRecordset rExportRecordset
Me.ProgressBar.Value = rExportRecordset.RecordCount
oExcel.ActiveSheet.PageSetup.PrintTitleRows = “$1:$1”
oExcel.ExecuteExcel4Macro (“PAGE.SETUP(, “”Page &P of &N””, 0, 0, 0, 0, , True, True, False, 2, 1, 70, “”Auto””, 1, , , 0.5, 0.5, False,False)”)
oExcel.ActiveWorkbook.Worksheets(1).Name = “Repair Order – Reliability”
oExcel.Columns(“A:G”).HorizontalAlignment = xlLeft
oExcel.Columns(“H:H”).Ho
