[AccessD] Can't write to an Excel spreadsheet

And here is an example of adding data to an Excel file from the Automation
help file...

Sub bulkTransfer()

   Dim xlApp As Excel.Application
   Dim xlBook As Workbook
   Dim xlSheet As Worksheet

   'Start a new workbook in Excel.
   Set xlApp = CreateObject("Excel.Application")
   Set xlBook = xlApp.Workbooks.Add

   'Create an array that contains 3 columns and 100 rows.
   Dim aryData(1 To 100, 1 To 3) As Variant
   Dim intCount As Integer

   For intCount = 1 To 100
      aryData(intCount, 1) = "ORD" & Format(r, "0000")
      aryData(intCount, 2) = Rnd() * 1000
      aryData(intCount, 3) = aryData(intCount, 2) * 0.7
   Next

   'Add headers to the worksheet on row 1.
   Set xlSheet = xlBook.Worksheets(1)
   xlSheet.Range(xlSheet.Cells(1,1),xlSheet.Cells(1,3)).Value = Array("Order
ID", "Amount", "Tax")

   'Transfer the array to the worksheet, starting at cell A2.
   xlSheet.Range("A2").Resize(100, 3).Value = aryData
      
   'Save the workbook and quit Excel.
   xlBook.SaveAs "C:\My Documents\ArrayDump.xls"
   xlApp.Quit
   Set xlSheet = Nothing
   Set xlBook = Nothing
   Set xlApp = Nothing

End Sub

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:47 PM
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