We help IT Professionals succeed at work.

Exporting to Excel

Derek Brown
Derek Brown asked
on
134 Views
Last Modified: 2019-01-15
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

John TsioumprisIT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2019

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

Author

Commented:
Thanks John

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

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().)
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions