Avatar of Derek Brown
Derek Brown
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Exporting to Excel

Is it possible to export data to an excel template starting at sheet1 cell A:5?

How do you delete existing content without deleting the existing format.

Currently I use this code:
If Dir("C:\MyFolder\Projects.xls") <> "" Then
    If MsgBox("This action will create a spread sheet file listing all projects. Do you want to REPLACE the existing Excel File with this later version?", vbYesNo) = vbYes Then
        Kill ("C:\MyFolder\Projects.xls")
        DoCmd.OpenQuery "Projects", acNormal, acEdit
    Else
        DoCmd.CancelEvent
    End If
End If
Microsoft Access

Avatar of undefined
Last Comment
Derek Brown

8/22/2022 - Mon
John Tsioumpris

Do you mean something like this
Public Sub ExportToExcel()
Dim rst As DAO.Recordset
    Const StartRow = 5
    Dim MaxRow As Integer
    Dim objXL As Object
    Dim objWkb As Object
    Dim objSht As Object
    Dim FieldNames() As String
    Dim x As Integer
    Dim strSQL As String
    Dim strFields As String
    Dim ctl As control
    
    
    
    
    Set rst = CurrentDb.OpenRecordSet("Projects")
    rst.MoveLast
    rst.MoveFirst
    MaxRow = rst.RecordCount
    
    ReDim FieldNames(rst.Fields.Count)
    For x = 0 To rst.Fields.Count - 1
       FieldNames(x + 1) = Nz(rst.Fields(x).NAME, "N.A")
    Next


Set objXL = CreateObject("Excel.Application")
    With objXL
        .Visible = True
        
        Set objWkb = .Workbooks.Add

        Set objSht = objWkb.Worksheets(1)
        objSht.NAME = "Projects"

        With objSht
        


            .Range(.Cells(StartRow, 1), .Cells(StartRow + MaxRow, 1)) _
                    .CopyFromRecordset rst
                    For x = 0 To rst.Fields.Count
                    .Cells(1, 1 + x).Value = Nz(FieldNames(x), "N.A")
                    Next x
        End With
        .Columns.autofit
    End With

Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing


Set rst = Nothing

Exit_cmdExcel_Click:
    Exit Sub

Err_cmdExcel_Click:
    MsgBox Err.Description
    Resume Exit_cmdExcel_Click
End Sub

Open in new window

Derek Brown

ASKER
Thanks John

Does this overwrite existing Projects.xls?
Mark Edwards

You can also target a named-range in Excel with the Access TransferSpreadsheet() function.  Just include the name of the named-range as the "Range" parameter.  The size of the named-range will be automatically resized to fit the data block.  (Forget what Microsoft says.  You CAN target a named-range with TransferSpreadsheet().)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Mark Edwards

p.s.  If you don't mind creating/opening your workbook, you can use VBA in your Access app to create a single-cell named-range anywhere in your workbook and then save your changes, close the workbook file, and target it with TransferSpreadsheet().  The size of the named-range will be automatically resized to fit the data block.   You gotta open it anyway to use CopyFromRecordset, may as well.

Works like a charm!
ASKER CERTIFIED SOLUTION
Mark Edwards

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Derek Brown

ASKER
That really helpful Guys. I will work on it at the weekend and report back.