[AccessD] Can't write to an Excel spreadsheet

And here's some more code (using the CopyFromRecordset property.

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



John V. Ruff - The Eternal Optimist :-)
 
"Commit to the Lord whatever you do,
                and your plans will succeed." Proverbs 16:3
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: Friday, January 06, 2006 9:47 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Can't write to an Excel spreadsheet

Oh yea, I remember.  What a PITA.  That throws a major wrench in the works.
I was hoping to open a predefined spreadsheet, with locked columns, named
ranges etc., then just export data to it, attach to an email and send.

John W. Colby
www.ColbyConsulting.com 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Friday, January 06, 2006 12:41 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Can't write to an Excel spreadsheet

There were a couple of threads on this a few weeks ago.

Apparently M$ lost some law suit and the end result was they had to disable
the ability of Access to update Excel. You can still export to Excel, but
that's it - I think.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: Friday, January 06, 2006 12:30 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Can't write to an Excel spreadsheet


I created a spreadsheet in a workbook.  Placed text names in the top row for
four columns, saved the sheet.  I then linked the sheet to Access.  With no
data in the spreadsheet, the table shows no data, and there is no new record
row.  If I try to append data to Columns A and B using an append query, I
get a "you are using a non-updateable query" or something similar.  If I
name a range and link to the named range, with actual data in a few records,
the entire range shows, the data shows in the first few rows, but the whole
thing is non-updateable.

I thought that an Excel spreadsheet was updateable from Access.  Am I doing
something wrong or is Excel indeed not updateable from Access?

John W. Colby
www.ColbyConsulting.com 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com