updating Excel Spreadsheet from Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I regularly import spreadsheet data and run one sided queries to match and
update fields brought over from Excel.

Is there a way to update the original Excel Spreadsheet fields after this is
done from Access?

The fields are all text. Thank You
 
Yes, it can be done. A couple of ideas come immediately to mind, but not
knowing the relationship of your Access data to the Excel spreadsheet, it is
difficult to choose the best approach.

One possibility will only work if you can replicate the data in the
spreadsheet from your Access data. That is to say, that if you have a way to
recreate the spreadsheet, then the easiest (least coding) way would be to
delete the existing spreadsheet and use the TransferSpreadsheet method to
recreate it with the updates.

If that method will not work for you, then you will need to become familiar
with automation to open and manipulate a spreadsheet as an object. The first
time you do this, it will be confusing and difficult. Once you understand
it, it will be a valuable tool in your bag.
 
I import the Excel fields I need to do comparisons, there is one long integer
field which relates to other tables I have in access. I create one-sided
joins in a query and based on whether fields match or not I fill in a
spreadsheet field with an appropriate answer using an iif statement. Process
now is to print the resulting table and then open Excel Spreadsheet and fill
in the field.

The Spreadsheet is a corporate template so I cannot reproduce it. I would
just like to fill in this last field with the results of my access query.

Thanks for your continued help.
 
Okay, here is some sample code that will get you started. Basically what you
need to do is establish an Excel Object, A Workbook Object, and a Worksheet
Object. Then you can read and write to specific cells in the worksheet.

First, you need to establish the above objects:

'Open Excel
On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo LoadAdjustedActuals_Err
DoEvents
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
xlBook.Worksheets("Actuals_res_export").Activate

****************

The above code calls this sub:

Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim Hwnd As Long
' If Excel is running this API call returns its handle.
Hwnd = FindWindow("XLMAIN", 0)
If Hwnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage Hwnd, WM_USER + 18, 0, 0
End If
End Sub

**********************

In this example, I am reading data from cells and populating an Access table:

For intRowCount = 3 To intLastRow
rstAccess.AddNew
For intColCount = 6 To 42
rstAccess.Fields(intColCount - 6) = _
IIf(intColCount < 26, ActiveSheet.Cells(intRowCount,
intColCount), _
Nz(ActiveSheet.Cells(intRowCount, intColCount), 0))
Next intColCount
rstAccess.Update
Next intRowCount
*********************
And finally, it is most important to shut it down correctly; otherwise, you
can end up with an instance of Excel still running. You will not see it in
th Applications tab of task manager, it will show up in the Processes tab.
This can cause the computer to hang up if a user tries to open Excel. You
have to be careful about correctly associating all your Excel objects. If
Access cannot figure out what excel object a workbook, worksheet, or other
object belongs to, it will create a new instance of Excel on its own. Your
code will not know about this instance and not kill it, so it will still be
running and cause problems:

xlBook.Close
Set xlBook = Nothing
'If we createed a new instance of Excel
If blnExcelWasNotRunning = True Then
xlApp.Application.Quit
End If
Set xlApp = Nothing
********************

And lastly, before you start, make sure you have a good supply of aspirin
and Preparation H

Good Luck
 
thank you , first glance this appears beyond my limited knowledge of the
program but I will play with it.
 
Back
Top