Link to home
Start Free TrialLog in
Avatar of Derek Brown
Derek BrownFlag 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
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

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

Avatar of Derek Brown

ASKER

Thanks John

Does this overwrite existing Projects.xls?
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().)
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
Avatar of Mark Edwards
Mark Edwards
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That really helpful Guys. I will work on it at the weekend and report back.