Access Table to Sheet1 of already created Excel spreadsheet 1

[mshone]

I use a VB Module to extract Number of Tasks and Man Hours for 3 Departments into a Table. We already have an Excel spreadsheet which uses these values, entered manually, but would like to use VB code to export these values to this already created spreadsheet. Can anyone help.<br><br>Regards<br><br>Mark

 

[Quehay]

Here's code that I use to export a query to Excel and automatically print a copy (thank you Jimmy the Geek for the kernel ideas!).&nbsp;&nbsp;By setting the print/export to begin at a specific row you can keep the column headers intact (row headings would be protected by adjusting the beginning row). Each time the routine is run the file is saved with a date value appended to the name--hope this helps:<br><br>Private Const XLS_LOCATION As String = &quot;C:\folder\'nother\file.xls&quot;<br><br><br>Public Sub ExportToExcel()<br>'Exports the qry_Visits to an Excel template<br>'and prints it<br><br>On Error GoTo ExportToExcel_Err<br>&nbsp;&nbsp;&nbsp;&nbsp;'DAO object variables<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Db As Database<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim rs As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim qdfMonitorVisits<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;'Excel object variables<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim objXL As Object, objSheet As Object<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strSaveAs As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim RowNum As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.Hourglass True<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set Db = CurrentDb()<br>&nbsp;&nbsp;&nbsp;&nbsp;Set qdfVisits = Db.QueryDefs(&quot;qry_VisitsbySite&quot<br>&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;'Open, Visits.xls (based on Template) and make it visible<br>&nbsp;&nbsp;&nbsp;&nbsp;Set objXL = GetObject(XLS_LOCATION)<br>&nbsp;&nbsp;&nbsp;&nbsp;objXL.Application.Visible = True<br>&nbsp;&nbsp;&nbsp;&nbsp;objXL.Parent.Windows(1).Visible = True<br>&nbsp;&nbsp;&nbsp;'Open the qry_Visits recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs = qdfVisits.OpenRecordset(dbOpenSnapshot)<br>&nbsp;&nbsp;&nbsp;&nbsp;Set objSheet = objXL.Worksheets(&quot;Visits&quot<br>&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;objSheet.Activate<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;RowNum = 2<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;'Insert the data from the qry_Visits recordset into the Visits worksheet<br>&nbsp;&nbsp;&nbsp;&nbsp;With rs<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Do Until .EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objXL.ActiveSheet.Cells(RowNum, 1).Value = rs![Site]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objXL.ActiveSheet.Cells(RowNum, 2).Value = rs![VisitDate]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objXL.ActiveSheet.Cells(RowNum, 3).Value = rs![Type]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objXL.ActiveSheet.Cells(RowNum, 4).Value = rs![Emp]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objXL.ActiveSheet.Cells(RowNum, 5).Value = rs![DaysOnSite]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objXL.ActiveSheet.Cells(RowNum, 6).Value = rs!RowNum = RowNum + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.MoveNext<br>Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End With<br><br>&nbsp;&nbsp;&nbsp;'objSheet.PrintOut<br><br>&nbsp;&nbsp;&nbsp;&nbsp;' Set the save string, and save the spreadsheet<br>&nbsp;&nbsp;&nbsp;&nbsp;strSaveAs = &quot;C:\my documents\folder\filename.xls&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;strSaveAs = strSaveAs & &quot;SiteVsts_&quot; & Format(Date, &quot;medium date&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;objXL.SaveAs strSaveAs<br>&nbsp;&nbsp;&nbsp;'Quit Excel<br>&nbsp;&nbsp;&nbsp;&nbsp;objXL.Application.DisplayAlerts = False<br>&nbsp;&nbsp;&nbsp;&nbsp;objXL.Parent.Quit<br>&nbsp;<br>&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;'Empty Object Variables<br>&nbsp;&nbsp;&nbsp;&nbsp;Set objXL = Nothing<br>&nbsp;&nbsp;&nbsp;&nbsp;Set objSheet = Nothing<br>&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs = Nothing<br>&nbsp;&nbsp;&nbsp;&nbsp;Set qdfMonitorVisits = Nothing<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;Export to Excel successful.&nbsp;&nbsp;Report should be ready at printer!&quot;<br>

 

[Quehay]

NB<br>&quot;Rownum = Rownum +1&quot; should be on a separate line and Rownum,6 would have another [field] value like the lines above it.

 

[Quehay]

The printout is also 'commented just to step through and check without printing every time.