General discussion


Reporting Error from Access form

By nancy.van ·
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
Exit Function
End If

Set oExcel = New Excel.Application

oExcel.SheetsInNewWorkbook = 1

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

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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Related Discussions

Related Forums