How do I write data from an ACCESS table to and EXCEL worksheet?

[Guest]

I have developed several routines that I have used in the past to write data
into specific cells in a EXCEL spreadsheet that is linked to an ACCESS
database. I don't know what I did differently this time, but I find that the
EXCEL spreadsheet is Read Only, and I am not able to write to the
spreadsheet. I am currently using ACCESS and EXCEL 2003, and I developed the
original Softweare under previous versions of EXCELL and ACCESS. If the
software has changed to disallow writing to EXCEL, is there another method
for writing to EXCEL which mimics the methods used with tables?

 

[james.igoe]

There were recent changes, forced by patent issues, that now prevent
Access from linking to Excel, or using Excel files as tables. Are you
familiar with this?

News Snippet:

In 1990 Carlos Armando Amado filed a patent for software which helped
transfer data between Excel spreadsheets and Microsoft's Access
database using a single spreadsheet. He said he tried to sell this
technology to Microsoft in 1992 but they turned him down. According to
Amado, Microsoft started including his software in their releases
between 1995 and 2002.



As for code, below is material I use for writng to Excel files, usually
via queries, but with some modification it will write tables:


Public Function FX_OutputToNewExcelFile(ByVal gstrQueryName, ByVal
gstrNewFileName, ByVal bolOverwrite As Boolean, Optional ByVal
strPivotType As String) As String

''accept inputs: template name, output file
''create excel object
''create recordset
''copyfromrecordset into template

On Error GoTo ErrorTrap

Dim dbCurrent As DAO.Database
Dim rstLocalData As DAO.Recordset
Dim strQuerySQL As String

Dim objExcelApplication As Excel.Application
Dim objExcelWorkbook As Excel.Workbook
Dim objExcelWorksheet As Excel.Worksheet

Dim lngCounter As Long

'gets sql of query and opens it
Set dbCurrent = CurrentDb
strQuerySQL = dbCurrent.QueryDefs(gstrQueryName).SQL
Set rstLocalData = dbCurrent.OpenRecordset(strQuerySQL)

'opens Excel file to use as template
'creates Excel Workbook
Set objExcelApplication = CreateObject("Excel.Application")
Set objExcelWorkbook = objExcelApplication.Workbooks.Add

'remove comment when debugging
'objExcelApplication.Visible = True

With objExcelApplication.Workbooks(1)

.Activate

With .Worksheets(1)

'add headers
For lngCounter = 0 To (rstLocalData.Fields.count - 1)

.Cells(1, lngCounter + 1) =
rstLocalData.Fields(lngCounter).Name

Next lngCounter

.Range("A2").CopyFromRecordset rstLocalData

End With

If bolOverwrite = True Then
.Application.DisplayAlerts = False
End If

.SaveAs gstrNewFileName

End With

'close objects
objExcelApplication.Quit
If IsObject(rstLocalData) Then Set rstLocalData = Nothing
If IsObject(objExcelApplication) Then Set objExcelApplication =
Nothing
If IsObject(objExcelWorkbook) Then Set objExcelWorkbook = Nothing
If IsObject(objExcelWorksheet) Then Set objExcelWorksheet = Nothing

FX_OutputToNewExcelFile = gstrNewFileName

Exit Function

ErrorTrap:

MsgBox Err.Description

FX_OutputToNewExcelFile = "Error"

'closes objects
objExcelApplication.Quit
If IsObject(rstLocalData) Then Set rstLocalData = Nothing
If IsObject(objExcelApplication) Then Set objExcelApplication =
Nothing
If IsObject(objExcelWorkbook) Then Set objExcelWorkbook = Nothing
If IsObject(objExcelWorksheet) Then Set objExcelWorksheet = Nothing

End Function



James Igoe

http://code.comparative-advantage.com/

 

[onedaywhen]

There were recent changes, forced by patent issues, that now prevent
Access from linking to Excel, or using Excel files as tables.

Didn't they replace the code, rather than drop the feature?

Jamie.

--

 

[Guest]

Thank you for your response. The response you gave me is close to what I
need. The main difference is that I need to be able to search through an
existing spreadsheet for a specific row, and then enter data from an ACCESS
table into the columes of that row in the spreadsheet. Is there soemthing
closer to that?