ACCESS - Export Multiple Blank Header Lines

WO2015
WO2015 used Ask the Experts™
on
Hello,

I have a query that has been working correct for years. The client has 1 request, they want 3 BLANK header lines above the regular header and data.  What I have set up is:

A macro that runs three queries and a RunCode() Module. That Module runs and exports the final query and names the file.  How can I add these 3 blank header lines?  Thank you!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Is it text file? Can you show piece of code with export?

Author

Commented:
Here is the module, the other items are just queries populating tables for this query to put all together for the output.

Function Discover()
DoCmd.OutputTo acOutputQuery, "CallReport", acFormatXLSX, "N:\CALLS_" & Format(Date, "mmddyy") & ".xlsx"
End Function
Top Expert 2016
Commented:
try this codes

Function ExportToExcel()
dim xlObj as Object, j as integer
dim rs as dao.recordset
set rs=currentdb.openrecordset("CallReport")
set xlObj=createObject("excel.application")
      xlObj.workbooks.add
     with xlObj
           for j=0 to rs.fields.count-1
                 .cells(4, j+1).value=rs(j).name
           next
                  .range("a5").copyfromrecordset rs

                   .activeworkbook.saveas  "N:\CALLS_" & Format(Date, "mmddyy") & ".xlsx"
     end with
      xlObj.quit

end function


another way is to open the excel file after your export and insert three blank lines on top

Function Discover()
DoCmd.OutputTo acOutputQuery, "CallReport", acFormatXLSX, "N:\CALLS_" & Format(Date, "mmddyy") & ".xlsx"
dim xlObj as object
set xlObj=createObject("excel.application")
      xlObj.workbooks.open  "N:\CALLS_" & Format(Date, "mmddyy") & ".xlsx"
     with xlOb
              .worksheets(1).activate
              .Rows("1:3").Select
              .Selection.Insert Shift:=-4121, CopyOrigin:=0
              .activeworkbook.save
      end with
      xlObj.quit
End Function

Author

Commented:
Thank you, that worked perfectly!!

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