exporting into a file with header in access

How do you export from access into a excel file with the excel file with a header already formatted.


I want to be able to export the data starting from row 15 in the excel file and how do you call the same file and still be able to export data again and again
Jass SainiAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this codes, replace objects as appropriate with your actual object names.

Sub exp2XL()
Dim rs As DAO.Recordset
Dim xlObj As Object
Dim Sheet As Object

Set rs = CurrentDb.OpenRecordset("select * from queryName")

If rs.EOF Then Exit Sub

    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.open "c:\folder\excelfile.xlsx"
      
      Set Sheet = xlObj.activeworkbook.Sheets("sheet1")
      
       Sheet.Range("A15").CopyFromRecordset rs  'copy the data
      
    Set Sheet = Nothing
    xlObj.Quit
    Set xlObj = Nothing
      
end sub
0
 
Kelvin SparksCommented:
To do this, you need to make Access start an instance of Excel, and use Excel vba to write to the worksheet.

Typically I'd use code something like

Dim xl As Object
Dim xlw As Object
Dim xls As Object

Set xl = CreateObject("Excel.Application")
xl.DisplayAlerts = False
xl.Visible = False

Set xlw = xl.Workbooks.Add
xlw.SaveAs sWB, FileFormat:=56 ''Fileformat 56 is the excel 97-2003 file format for xls files

Set xls = xlw.Worksheets("Sheet1")
xls.Name = "Record Sheet"
xls.SELECT
xls.Activate

From there you need to manage what cell or cells you are using and what you want to happen.


Kelvin
0
 
Jass SainiAuthor Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.