How to dynamically update an external Excel sheet cell by cell

[Xin]

Here is my problem. After each time I updated my Access record, I need my
Excel sheet to be updated as well. For example, I entered patient interview
date in Access form, my excel sheet cell (1,1) should have patientID uploaded
automatically. Then I go to next patient record, excel cell(2,1) should be
uploaded, so on so forth.

Since I am very new to Access coding, I don't know how to solve this
problem. Any help would be greatly appreciated.

The following is the code that I wrote, right now it just static update
cell(2,1), how to moveNext, I need your guidance.

Private Sub Sdate1_AfterUpdate()
If Me.sdate1.Value <> "" Then
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim dbs As DAO.Database
Dim rstD As DAO.Recordset
Dim i As Integer

Set dbs = CurrentDb

'Open existing workbook
Set oExcel = CreateObject("Excel.Application")
'Select Case Forms!frm_project_health_link!metcode
Select Case Me.metcode
Case 421, 422, 443, 487
Set oBook = oExcel.Workbooks.Open _
("C:\RCT follow up\Access Database\Link Random Table.xls")
'Add data to cells of a worksheet
Set oSheet = oBook.Worksheets("Link therapist 421,422,443,487")

oSheet.Cells(2, 1).Value = Me.SubjID
'Save the Workbook and Quit Excel
oBook.Save
oExcel.Quit
End Select
End If

End Sub

 

[JethroUK©]

provided your data is in all in a flat table (one table) - there's really
only one solution

export all your data from the table into an XL spreadsheet (copy/paste is
easiest)

Create a new table that is dynamically linked to the spreadsheet:

Tables>New>'Linked Table'

the two objects are now dynamically linked, which is to say that whichever
is updated, the update is reflected in the other

 

[Xin]

Since it is ongoing data entry, I can't export the data as a whole. I was
wondering is there a way to use Access VBA to update my XL sheet after each
time the field in Access form has been entered ?

Thanks,

 

[John Nurick]

This won't work in Access 2002 or 2003 with current patches: it's no
longer possible to update an Excel sheet via a linked table.

 

[John Nurick]

Hi Xin,

Updating an Excel sheet every time the user touches a record in your
database is usually a bad idea. For example, what happens if someone has
the workbook open at the time?

Almost certainly there's a better way to accomplish your real objective,
perhaps by storing all the data in Access and exporting it to Excel as
and when needed.

 

[Xin]

John,

Thanks for the response. Your suggestion was really valuable. However, I
still want to know whether using VBA (by twicking my code) can achieve my
goal if I am the only user to control both access and excel.

 

[JethroUK©]

you can export the whole table in less time than it takes for me to write
this reply

open the table
ctrl + A
ctrl + c
open a spreadsheet
control+ v

that's it

 

[JethroUK©]

i've got Access 2002 - works fine for me

not quite sure why m.s. would want to lose this feature

 

[John Nurick]

i've got Access 2002 - works fine for me

not quite sure why m.s. would want to lose this feature

They didn't want to, but had to after losing a legal action over a
patent.

 

[John Nurick]

The general idea would be to separate the code that creates and destroys
the Excel application object from the code that opens the workbook and
updates the cells. For instance, you could declare

Dim oExcel As Excel.Application

at the beginning of the form's module and launch Excel in the form's
Load event procedure.

Put code to quite Excel in the form's Unload event procedure, e.g.

Do Until oExcel.Workbooks.Count = 0
oExcel.Workbooks(1).Close False
Loop
oExcel.Quit
Set oExcel = Nothing



Then in your AfterUpdate event, you could do something like

Set oBook = oExcel.Workbooks.Open _
("C:\RCT follow up\Access Database\Link Random Table.xls")
Set oSheet = oBook.Worksheets("Link therapist 421,422,443,487")

You then need to keep track of which cell you need to poke the data
into. From what you've said so far, it sounds as if you want to put the
new value at the bottom of the column of previously inserted values. You
can use something like this to get the row number of the first blank
cell in the column:

Dim lngRowNum As Long
...
lngRowNum = oSheet.Cells(1, 1).End(xlDown).Row + 1

and then use

oSheet.Cells(lngRowNum, 1).Value = Me.SubjID

before saving and closing the workbook.


....

If opening and closing the workbook each time make things too slow for
your liking, you could move these operations into the form's Load and
Unload procedures, so the workbook would be open the whole time the form
is. But this increases the risk of problems if something goes wrong.