Appending to Excel File from Access VBA

[NDayave]

How do,

I want to be able to append the data from a recordset to the end of an excel file.

Currently I can do this with text files through the .write method, and I can create excel sheets with data in, but not append to the end of them.

I have spent a fair while looking for a solution on this and have come up with literally nothing.

Is there any way to, rather than replace the data in an excel file, append the new data to the end of what is already present?

Thanks in advance,

NDayave

[beacon]

So you do know how to replace existing entries in a spreadsheet? If you do, then appending should be pretty easy. I haven't tested this out from within Access, but I have code for a macro in Excel that I assigned to a command button that appends 10 rows worth of data to the next unused row.

Here's the code I use in Excel, which you should be able to modify to meet your needs from within Access:

Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
 

That will highlight the next unused row in your spreadsheet. You can use ActiveCell.Offset(1, 0).Value to insert the actual value into the cell. Then you can adjust the offset to (1, 1) to move to the next column to the right and can increment that number as many times as needed to fill in the appropriate columns.

[NDayave]

No...

What I can do at the moment is create an Excel file from a Query in Access, not replace the existing fields.

This is done via the Access VBA

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExport", varFileName, True

If the filename used already exists, this overwrites the file with the new data.

I'm not sure on how you would run the excel code from within the Access VBA.

[beacon]

Oh, when you mentioned the .write method, I thought that you were creating an instance of Excel in order to write to files.

You would need to create an instance of Excel first, then you can insert code similar to mine above into that code. Something like the following (I got part of this code from some http://www.excelguru.ca):

Sub AppendToExcel()
 
Dim objExcel As Object
 
On Error Resume Next
 
Set objExcel = GetObject( , "Excel.Application")
 
If Err.Number <> 0 Then
'Could not get instance, so create a new one
Err.Clear
On Error GoTo ErrHandler
Set objExcel = CreateObject("Excel.Application")
With objExcel
.Visible = True
.Workbooks.Add
End With
Else
'Bound to instance, activate error handling
On Error GoTo ErrHandler
End If
 
'Add some text to the document
With objExcel.ActiveWorkbook
.Worksheets(1).Range("A65536").Select
Selection.End(xlUp).Select 
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = yourFieldValue
End With
 
ErrHandler:
'Release the object and resume normal error handling
Set objExcel = Nothing
On Error GoTo 0
End Sub
End Sub
 

I may have left out some code to close Excel. Plus, you may want to set the .Visible line of code to False if you don't actually want to see Excel open while you're working in the database.

I haven't tested this out either, but let me know how it works out.

[ADezii]

1. Open the Excel Spreadsheet via Automation.
2. Determine the Last Row which contains Data.
3. Use the CopyFromRecordset Method to copy the Recordset starting from Range("A<Last Row + 1>).
4. If you need further help, let us know.

[ADezii]

Had some spare time, so I wrote the code anyway. Let's suppose that you wanted to Export the Output of qryEmployees to Sheet1 in C:\Stuff\Employees.xls, and that you also wanted to Append the Data to any existing Data (next Row after existing Data on Sheet1):

Dim appExcel As Excel.Application
Dim lngLastDataRow As Long
Dim RS As DAO.Recordset
 
Set appExcel = CreateObject("Excel.Application")
Set RS = CurrentDb.OpenRecordset("qryEmployees", dbOpenSnapshot)
 
With appExcel
  .Visible = True
  .UserControl = True
 
   With .Workbooks.Open("C:\Stuff\Employees.xls")
     lngLastDataRow = .Worksheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row
     .Worksheets("Sheet1").Range("A" & CStr(lngLastDataRow + 1)).CopyFromRecordset RS
   End With
End With
 
RS.Close
 
appExcel.WindowState = xlMaximized
 
Set RS = Nothing
Set appExcel = Nothing

[NDayave]

That is very kind, I'll get back to you both tomorrow regarding what has and hasn't worked.

Many Thanks,

NDayave

[NDayave]

I've had a play with both sets of code. The first doesn't like the line

Selection.End(xlUp).Select

stating that "An Object is required". If I add in .Worksheets("Worksheetname") then it says the Object doesn't support the property or method.

Adezii's code, which looks like this after altering it to fit my needs:

        Dim appExcel As Object
        'Excel.Application
        Dim lngLastDataRow As Long
 
        Set appExcel = CreateObject("Excel.Application")
        Set RS = CurrentDb.OpenRecordset("qryExport", dbOpenSnapshot)
 
        With appExcel
          .Visible = True
          .UserControl = True
 
           With .Workbooks.Open(varFileName)
             lngLastDataRow = .Worksheets("qryExport").Cells.SpecialCells(xlCellTypeLastCell).Row
             .Worksheets("qryExport").Range("A" & CStr(lngLastDataRow + 1)).CopyFromRecordset RS
           End With
        End With
 
 
        RS.Close
 
        'appExcel.WindowState = xlMaximized
 
        Set RS = Nothing
        Set appExcel = Nothing

You'll note that "Dim appExcel As Excel.Application" has changed to Object as Excel.Application apparently isn't a valid declaration and the xlMaximised is commented out as this refused to work and is for all purposes redundant at this moment in time.

The problem here is on line 13, where it is "Unable to get the SpecialCells property of the Range Class" (Error 1004)

I can't see what is wrong here as I have used similar code within Excel to set the Print area to only those cells that contain data rather than formulas.

Any help is most appreciated,

NDayave

[ADezii]

1. Do you have a reference set to the Microsoft Excel X(X).X Object Library?
2. appExcel should be Explicitly Declared as Excel.Application (Early Binding) to avoid possible problems with Late Binding utilizing the vague Object Type.

[NDayave]

I do now and it is working perfectly.

I'll learn one day.

Cheers,

NDayave