Link cells in Excel to Access table fields

Question

Consider I have a macro in access 2003 that uses the transferSpreadsheet function to export a Union query to a excel 2007 spreadsheet; the exported data depending on a date range input by the user.

Now suppose this UNION ALL Query grouped a table and a query. The query being a LEFT JOIN of a linked table and another table to add missing fields to.

Is it possible to allow updating of the cells in excel to simutaneously update the proper fields of the records in the three tables (Table, Query(2 Tables)) which are part of the Union (which can't be edited directly and only used to get all the information in one table)? If so can someone show me the proper way to do this or at least somewhat guide me in the right direction?

I would like it if while I was exporting and formatting the excel files in vba, I linked the cells. As the end user would not know what to do to link the cells. I ask because the file is generated monthly changed throughout the month into the next month and sometimes is still updated during the next month and both are worked on simutaneously. It would be easier if they where linked so the excel file would not have to be continually generated.

Using:

VBA (not vb.net)

Access 2003

Excel 2007

Is it possible to allow updating of the cells in excel to simutaneously update the proper fields of the records in the three tables (Table, Query(2 Tables)) To answer this question - which is your only question in the post - Yes, it is possible! — Scott Holtzman
This is going to be long, tedious and probably not a good idea. You can write some queries and feed them parameters with ADO, but disaster may be a click away. You can feed the Excel table back to Access and run updates, which may be safer. You will need to try. — Fionnuala
@Remou well I'm unfamilair with that methodology but another problem with using the SQL Update function, which i'm guessing i would use, is that not only does the data have a range depending on date, the table used to generate it is also a union. Once more the ID's are not exported with the rest of the Project Report — kdgwill
If you have no IDs, you cannot do this in Excel, I can suggest something that would work in Access. — Fionnuala

Lance Lance · Accepted Answer · 2016-01-22T01:46:40

kdgwill, after performing your transferspreadsheet function, you can then perform the following. This will take the names of the spreadsheet just saved, manipulate what you need to change, then save it back to Excel with either the same name or new name. This happens very fast depending on the size of your data. 65,000 rows of data many take a minute to save the file. You can add data to tables even when calling this function. Work with this to see if you can manipulate it to your means.

Dim objExcel            As New Excel.Application
Dim objWB               As Excel.Workbook
Dim objWS               As Excel.Worksheet
Dim objWS2              As Excel.Worksheet
Dim objCell             As Excel.Range



Set objExcel = CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open("path of the original excel file\excelfile.xls")

objWB.Activate
'Do not show the excel window during this operation.  By doing this the processing is faster.
objExcel.Visible = False

sSection = "Open Spreadsheet"
If objWB.Worksheets.Count = 0 Then
    objWB.Worksheets.Add
End If

'Open the first sheet named what ever you want to name it
 Set objWS = objWB.Sheets(1)

objWS.Name = "Name of Spreadsheet"
objWS.Activate

'Change the font to black or anything else you want to change
With objWS
    .Cells.Select
    .Cells.Activate
    .Cells.Font.Size = "12"
    .Cells.Font.Color = 0
    .Cells.Range("A1", "A1").Select
End With

    objExcel.Workbooks(1).SaveAs "path of new excel file to be saved\excelfile.xls"

'Clean up 
objWB.Close
Set objWB = Nothing
Set objExcel = Nothing

Link cells in Excel to Access table fields

1 Answers