I am able to successfully export a query into an excel spreadsheet, but I'm 

having trouble finding a way to "move" the data on the worksheet itself. 

Specifically, I'd like to shift the data down a few rows, so I can put a 

heading at the top of the worksheet. Any idea on how to do the "shift", and 

then put in a heading  ?



Thanks !

Here are functions to create a spreadsheet and use the CopyfromRecordset

function to copy the data



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 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

   

  ' Create a name for the worksheet

  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



  ' 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



John Ruff - The Eternal Optimist :-)







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

From: Lloyd Levine [mailto:levinll@m...] 

Sent: Friday, November 16, 2001 7:48 PM

To: Access

Subject: [access] Office Automation





I am able to successfully export a query into an excel spreadsheet, but

I'm 

having trouble finding a way to "move" the data on the worksheet itself.



Specifically, I'd like to shift the data down a few rows, so I can put a



heading at the top of the worksheet. Any idea on how to do the "shift",

and 

then put in a heading  ?



Thanks !

---

You are currently subscribed to access as: papparuff@c... To

unsubscribe send a blank email to $subst('Email.Unsub')

Lloyd,



John Ruff provided you with a complete and excellent example, but if

you just want to know how to insert the rows:



Range("A1").Select

Selection.EntireRow.Insert

Selection.EntireRow.Insert



The macro recorder is an excellent source for gaining insight into all these

fuzzy things.



Walt