Exporting to Excel

Derek Brown
Derek Brown used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
John TsioumprisSoftware & Systems Engineer

Commented:
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 BrownMD

Author

Commented:
Thanks John

Does this overwrite existing Projects.xls?
Mark EdwardsChief Technology Officer

Commented:
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().)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
Mark EdwardsChief Technology Officer

Commented:
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!
Chief Technology Officer
Commented:
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.
Derek BrownMD

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial