asked on
Private Sub Export_Quarterly_Reports_Click()
DoCmd.Echo False, "Exporting, Please Wait.........."
Dim RootMIdir As String
Dim FilePath As String
Dim xlPath As String
Dim rs As DAO.Recordset, sql As String
Dim xlobj As Object, wb As Object, ws As Object
xlPath = "\Report Templates\"
RootMIdir = "\Output Files"
Set xlobj = CreateObject("excel.application")
Set wb = xlobj.Workbooks.Open(xlPath & "BB_Template.xls")
Set ws = wb.Worksheets("AA_DATA")
sql = "SELECT *"
sql = sql & " FROM _DATA"
Set rs = CurrentDb.OpenRecordset(sql)
ws.Range("a2").CopyFromRecordset rs
wb.SaveAs Filename:=RootMIdir & "\REPORT" & Format(Now(), " dd-mmm-yy") & ".xls"
wb.Close
xlobj.Quit
Set ws = Nothing
Set wb = Nothing
Set xlobj = Nothing
Set rs = Nothing
DoCmd.Echo True