I have a new database set to replace hand counting and entering values 

into and excel spreadsheet.  I now collect the data via data entry into my 

database.  I have all of my reports in Excel (for now)  I need to insert 

fields in Access into Excel Cells which will build my reports for me, 

giving me time to build the 60+ reports in Access.



Whew......



Any help offered will be greatly appreciated

You can build the queries in Access that will return the data you need in

Excel and from the Query Datasheet view select Tools->Office Links->Analyze

It with MSExcel.  It will open an Excel workbook with the data all there.

It will not export all the rows if there are more than 16535 data rows this

way but there is another way to get 64k+ rows.



>-----Original Message-----

>From: Noel Wilson [mailto:nwilson4@t...]

>Sent: Wednesday, January 30, 2002 6:08 PM

>To: Access

>Subject: [access] Access data to Excel Reports

>

>

>I have a new database set to replace hand counting and entering values

>into and excel spreadsheet.  I now collect the data via data entry into my

>database.  I have all of my reports in Excel (for now)  I need to insert

>fields in Access into Excel Cells which will build my reports for me,

>giving me time to build the 60+ reports in Access.

>

>Whew......

>

>Any help offered will be greatly appreciated

>




>

If you don't want to have to manually format each xls report then you

will want to use automation and the CopyFromRecordset function.



If you go to 

http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q260410 (watch

any word-wrapping), download auto2000.exe and install the program, it

has excellent samples how to do this.  



Here is is some basic code that will get you started:



Public Function CreateSpreadsheet() as boolean

  ' This routine permits you to quickly save data in a recordset to a

spreadsheet.  



  Dim xlApp As Excel.Application

  Dim xlBook As Excel.Workbook

  Dim xlSheet As Excel.Worksheet

  Dim dbsTemp As Database

  Dim rstTemp As Recordset

  Dim bolIsExcelRunning As Boolean

  Dim strFileName as string

  Dim strDirectory as string

  Dim intCount as integer  

  Dim intColCount as integer



  Set dbsTemp = CurrentDb



  Set rstTemp 

dbsTemp.OpenRecordset("qSel_AgingReport_Summary_Spreadsheet_Final")



  ' This is the directory where the spreadsheet will be saved to

  strDirectory="N:\COMMON\MSSP\AGING REPORT\"

  ' This is the File Name

  strFileName="MySpreadsheet.xls"



  ' Determine if Excel is open

  bolIsExcelRunning = IsExcelRunning()

 

  ' Create a Worksheet Object

  If bolIsExcelRunning Then

    Set xlApp = GetObject(, "Excel.Application")   

  Else

    Set xlApp = CreateObject("Excel.Application")

  End If

    

  ' Create a new workbook

  Set xlBook = xlApp.Workbooks.Add()

  

  ' Provide a name for the worksheet

  For Each xlSheet In xlBook.Worksheets

    If xlSheet.Name = "Sheet1" Then

      xlSheet.Name = "Aging Report Summary"

    End If

    If xlSheet.Name = "Sheet2" Then

      xlSheet.Name = "Aging Report Detail"

    End If

  Next

   

  Set xlSheet = xlBook.Worksheets("Aging Report Summary")



  xlSheet.Visible = True

  xlSheet.Activate



  intColCount = rstTemp.Fields.Count

 

  ' Place Column Headers onto the spreadsheet starting in cell A1

  With xlSheet.Range("A1")

    For intCol = 0 To intColCount - 1

      .Cells(1, intCol + 1).Value = rstTemp.Fields(intCol).Name

    Next intCol

  End With

  

  ' Copy the recordset to the spreadsheet starting in cell A2

  xlSheet.Range("A2").CopyFromRecordset rstTemp





 

  ' Saving and quitting

 

  ' Save the file

  xlBook.Close savechanges:=True, FileName:=strDirectory & strFileName



  ' Quit

  If Not bolIsExcelRunning Then

    xlApp.Quit

  End If

  

  Set xlSheet = Nothing

  Set xlBook = Nothing

  Set xlApp = Nothing



  ' Reset error object to 0

  Err = 0



End Function



Public Function IsExcelRunning() As Boolean

' This function determines if Excel is running

    Dim xlApp As Excel.Application

    On Error Resume Next

    Set xlApp = GetObject(, "Excel.Application")

    IsExcelRunning = (Err.Number = 0)

    Set xlApp = Nothing

    Err.Clear

End Function







-----Original Message-----

From: Noel Wilson [mailto:nwilson4@t...] 

Sent: Wednesday, January 30, 2002 6:08 PM

To: Access

Subject: [access] Access data to Excel Reports





I have a new database set to replace hand counting and entering values 

into and excel spreadsheet.  I now collect the data via data entry into

my 

database.  I have all of my reports in Excel (for now)  I need to insert



fields in Access into Excel Cells which will build my reports for me, 

giving me time to build the 60+ reports in Access.



Whew......



Any help offered will be greatly appreciated