How to put access data in excel field on a specified sheet 2

[Cantor]

I have Access data (one column) and I want to put these data in a specified field in Excel in a SPECIFIED sheet.<br><br>I know how to export from Access to Excel but how do I choose my worksheet?

 

[jimmythegeek]

Let's say you have a spreadsheet called &quot;StateStats.xls&quot;<br>On there you have 4 sheets (Seattle, Pittsburgh, Dallas, Miami)<br>In Cell B5 of the &quot;Dallas&quot; Sheet you want to enter data, and continue down from there.<br>I put the following code on Click of a button called cmdExport, you can put it where you want.<br><br>There are comments in between some stuff. Replace the bolded areas with your own info:<br><br>-----<br><br>Private Sub cmdExport_Click()<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim rs As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strSQL As String, strSpreadsheet As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim objXLT As Object<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim objWorksheet As Object<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim x As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>On Error GoTo Export_Err<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.Hourglass True<br>&nbsp;&nbsp;&nbsp;&nbsp;' Create the string for the recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;strSQL = <b>&quot;SELECT TestDate FROM tblTest WHERE (((Status)= 'Active'));&quot;</b><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;strSpreadsheet = <b>&quot;C:\My Documents\Spreadsheets\StateStats.xls&quot;</b><br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb()<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' If there are records, then<br>&nbsp;&nbsp;&nbsp;&nbsp;With rs<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If .RecordCount &gt; 0 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Open the template<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set objXLT = GetObject(strSpreadsheet)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Make it visible<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objXLT.Application.Visible = True<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objXLT.Parent.Windows(1).Visible = True<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set objWorksheet = objXLT.worksheets(<b>&quot;Dallas&quot;</b>)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objWorksheet.Activate<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Insert the data from the recordset into the appropriated cells on the template<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;x = 5<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Do Until .EOF&nbsp;&nbsp;&nbsp;' The first number (x) is the row, the second number is the column<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objXLT.activesheet.cells(x, 2).Value = !<b>testdate</b><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'objXLT.activesheet.cells(x, 3).Value = !BegSeqNo&nbsp;&nbsp;&nbsp;' If you had more than one field<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;x = x + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rs.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;End With<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>Export_Exit:<br>&nbsp;&nbsp;&nbsp;&nbsp;rs.CLOSE<br>&nbsp;&nbsp;&nbsp;&nbsp;Set objXLT = Nothing<br>&nbsp;&nbsp;&nbsp;&nbsp;Set objWorksheet = Nothing<br>&nbsp;&nbsp;&nbsp;&nbsp;db.CLOSE<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.Hourglass False<br>&nbsp;&nbsp;&nbsp;&nbsp;Exit Sub<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>Export_Err:<br>&nbsp;&nbsp;&nbsp;&nbsp;MsgBox Err.Number & &quot;: &quot; & Err.Description<br>&nbsp;&nbsp;&nbsp;&nbsp;Resume Export_Exit<br>End Sub<br><br>-----<br><br>This will work, have done it many times. Let me know if you have trouble. <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development

 

[LarryDeLaruelle]

I think I can use this concept for a project I'm working on, but I have a couple of questions.

1. Can a Workbook be created &quot;on the fly&quot; from within the VB code and can the individual worksheets be renamed at that same time? Or, does the Workbook have to already exist in order to be referenced?

2. The data I need to insert into the worksheets will come from a series of cross tab queries where the number of columns can vary from month to month. Is it possible to capture the number of returned columns (similar to .RecordCount)?

3. Is there an alternative to this process? What I'm thinking of would be similar to pasting a Pivot Table into an existing worksheet where only the beginning cell reference (i.e., J21) needs to be supplied.

Thanks in advance.

Larry De Laruelle
larry1de@yahoo.com