Full Version: Having Problem Write To An Excel File When Using Recordset
UtterAccess Forums > Microsoft® Access > Access Forms
Lin100
The code below open an existing Excel file and write data to it.
The code below did write to the Excel file with no problem

Excel_Worksheet.Range("A3").Value = "TEST 1" <-- It will write to this location
Excel_Worksheet.Range("A4").Value = 2 <-- It will write to this location

//////////////////////////////////////////

The code below give an error message "Application-defined or object-defined error
when write to the Excel file using the method CopyFromRecordset rsRecords.

Excel_Worksheet.Range(12, 1).CopyFromRecordset rsRecords <-- It crashed when writing to an Excel file using recordset

//////////////////////////////////////////

Private Sub Step_9_Click()
On Error GoTo ErrorHandler

Dim Query_Name As String
Dim Worksheet_Name As String

Dim File_Name As String
Dim File_Path As String
Dim Full_File_Name As String
Dim blnFileExists As Boolean



File_Name = "POSTAGE_MOORE_TEMPLATE" 'This is an Excel file .XLSM
File_Path = "\\ksgobtfs01\Groups\Accounting\DATABASES\Production\Monthend Processes\Moore Printing Bill\EXPORT_FOLDER\"
Full_File_Name = File_Path & File_Name & ".xlsm"



Worksheet_Name = "Support"
Query_Name = "IMPORT QRY #09: USE THIS TO RECONCILE TO INVOICE"

Call Open_An_Excel_Work_Book(Full_File_Name, Worksheet_Name, Query_Name)


ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox Err.Description
Resume ErrorHandlerExit
End Sub


////////////////////////////////////////////////////////

Private Sub Open_An_Excel_Work_Book(Full_File_Name, Worksheet_Name, Query_Name)
Dim Dbs As DAO.Database
Dim rsRecords As DAO.Recordset

Dim Excel_Application As Excel.Application
Dim Excel_Workbook As Excel.Workbook
Dim Excel_Worksheet As Excel.Worksheet
Dim Excel_Range As Excel.Range


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Set Dbs = CurrentDb
Set rsRecords = Dbs.OpenRecordset(Query_Name, dbOpenDynaset)

If rsRecords.EOF And rsRecords.BOF Then
MsgBox "Query or SQL returned no records.", vbCritical + vbOKOnly, "Error"
Exit Sub
End If


Set Excel_Application = CreateObject("Excel.Application")
Excel_Application.Visible = True


Set Excel_Workbook = Excel_Application.Workbooks.Open(Full_File_Name)
Set Excel_Worksheet = Excel_Workbook.Worksheets(Worksheet_Name)
Excel_Worksheet.Activate


Excel_Worksheet.Range(Excel_Worksheet.Cells(12, 1), Excel_Worksheet.Cells(16, 4)).Select
Excel_Worksheet.Range(Excel_Worksheet.Cells(12, 1), Excel_Worksheet.Cells(16, 5)).Clear


'WRITE DATA TO THE EXCEL SPREADSHEET
Excel_Worksheet.Range("A3").Value = "TEST 1" <-- It will write to this location
Excel_Worksheet.Range("A4").Value = 2 <-- It will write to this location

Excel_Worksheet.Range(12, 1).CopyFromRecordset rsRecords <-- It crashed when writing to an Excel file using recordset


rsRecords.Close
Set rsRecords = Nothing

End Sub
dmhzx
12,1 isn't a range is it??

surely a range starts with a letter?
Lin100
Thank you dmhzx for your help.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.