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
    End If
End If
Derek BrownMDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
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")
    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")

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
    End With

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

Set rst = Nothing

    Exit Sub

    MsgBox Err.Description
    Resume Exit_cmdExcel_Click
End Sub

Open in new window

Derek BrownMDAuthor Commented:
Thanks John

Does this overwrite existing Projects.xls?
Mark EdwardsChief Technology OfficerCommented:
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().)
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Mark EdwardsChief Technology OfficerCommented:
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!
Mark EdwardsChief Technology OfficerCommented:
p.s.s TransferSpreadsheet itself does NOT over write an existing workbook, just creates a new one if the file doesn't already exists.  It also does NOT mess with your formatting.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Derek BrownMDAuthor Commented:
That really helpful Guys. I will work on it at the weekend and report back.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.