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

Last Comment

Derek Brown

8/22/2022 - Mon

John Tsioumpris

1/9/2019

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

1/9/2019

ASKER

Thanks John

Does this overwrite existing Projects.xls?

Mark Edwards

1/9/2019

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

1/9/2019

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

1/9/2019

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

1/10/2019

ASKER

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