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
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") NextSet 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 WithSet objSht = NothingSet objWkb = NothingSet objXL = NothingSet rst = NothingExit_cmdExcel_Click: Exit SubErr_cmdExcel_Click: MsgBox Err.Description Resume Exit_cmdExcel_ClickEnd Sub
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.
Open in new window