Excel update sheet from Access (Access 2002)

**patt** · 2004-10-18 22:41

I have an excel sheet that I keep tennis game difference results in.
I also have the actual results recorded in Access.
What I wish to do from Access is to:
1. Import the sheet from excel into Access...this I have no problems with.
2. Manually input a teams results into Access.
3. Update the Excel sheet with the updated teams results.

I have no idea on Excel automation, can somebody give me a head start please.

**Francois** · 2004-10-18 22:57

Never done this before, but you can link a excel sheet and treat it as a table.
Then with update an append queries or by code, you could update the sheet.

**patt** · 2004-10-18 23:38

Duh, talk about a mental block, I did not think about linking to an Excel Sheet.

Thanks Francois, that could be the way to go.

**patt** · 2004-10-30 05:46

I have used your linked sheet method to update the sheet.

I still need excel automation to set up the sheet initially. I know the actual cell addresses to update.
Can someone provide sample code to show me how to update a cell from Access.

**Leesha** · 2004-10-30 09:52

Hi Pat,

Question for you. I wasn't clear whether the data you were keeping in excel is the same as the data you are entering Access. You know I love Excel over Access, so my question is why not one over the other? If you are have already imported the existing spreadsheet into Access and are doing all of your data entry there, then need to send it back to Excel (for ex. is the user prefers and excel spreadsheet) you could simply export the entire database or the date range you choose to Excel from Access. I'm not sure if this is what you want, but as someone who has tons of stuff in Excel spreadsheets that I've automated and am now importing into Access and will maintain and manipulate in Access vs Excel, I don't understand why you need both.

Leesha

**HansV** · 2004-10-30 14:29

If you search this forum for excel.application, you will find examples of automating Excel from Access, e.g. <post#=376265>post 376265</post#>.

**patt** · 2004-10-30 20:52

Here we go.
I have an excel template that holds the games difference for all players (up to 6) in a tennis competition. I have all the appropriate formulas in place (quite proud of myself not ever used excel before) but all the columns show as double precision as the linked table in Access. At the top of some of these columns are the players names (text). I must admit I have not tried to put a name into the linked table at the appropriate position.

I have now and it does not work. I will have to use something to update the Excel cells with the names using automation.

**patt** · 2004-10-30 20:57

The post you named is more of formatting a sheet rather than setting up values at certain cells.
I did find another post which seems to give me what I need by MasterD. I think you also had some input with it as well, I cannot recall the thread number though.

The reason I am using Excel in this fashion is for it's fancy printing to highlight various columns and lines. I can do everything else oin Access.

**Leesha** · 2004-10-30 20:58

Can you send me a copy of your excel spread sheet (stop laughing).

Leesha

**patt** · 2004-10-30 21:23

Here it is. I'm not laughing as I do not know excel and any help is much appreciated.

**Leesha** · 2004-10-31 01:50

Hi Pat,

Nice looking spreadsheet, however I can see why linking or importing is an issue. I agree with your earlier post as to the ability to make the excel spreadsheet look nicer re formatting a report in Access. With that said, why is it that you're using both Access and Excel for this project? Seems to me it would be easier to just use the Excel spreadsheet to enter the data and forget access altogether.

Leesha

**patt** · 2004-10-31 02:46

>>Nice looking spreadsheet,<<
Careful, my head could swell. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

The reason why I'm doing it in Access is that I enter the actual scores (eg. 6 1 winner or 4 6 loser). What the Sheet shows is an accumulated difference. I have a function in Access that updates the sheets with these differences. So you see I could not use excel to record each weeks scores, then compute the differences.
I have built the report in Access but it's not as pretty. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

I have a template sheet that I copy to it's appropriate sheet name at the start of each season. I was going to use Excel automation to populate the Players names and each date and Team played of the season as it is entered into Access. I would also use automation to plug in the title. Then I would not need to enter Excel at all, only to print. The Access report is not too bad, it's just that Excel is prettier.

**Leesha** · 2004-10-31 12:33

Hi Pat,

Well I can't be of help automating the info from Access as I can barely automate my own stuff <img src=/S/bash.gif border=0 alt=bash width=35 height=39>. If I'm picturing your database correctly its simply for storage not for relationship type things, queries etc. If that is the case, what I'd do is add a second worksheet to your database called Database or something like that. Set it up similar to your table in Access (for that matter you can export it from Access and its all set. You can then link your data from the database sheet to your report.

My preference for my own personal use is always Excel. It's wicked smart, quicker to set up and you have more control over the report appearance. However, I find myself resorting to Access more and more since Excel isn't terribly user friendly and I find that most of my staff have no clue and I end up having to fix their blow ups whereas in Access they can't blow it up as easily.

Leesha

**patt** · 2004-10-31 20:24

>>I'm picturing your database correctly its simply for storage not for relationship type things, queries etc<<
What do you mean by this, it's a relationship database where there are tables for the SeasonsTeams (header for a team), Players (those players chosen to play in that team all season), DatePositions (all dates that describe when and what team they play), Results (Scores for each player in both singles and doubles). Does this describe your question?

Actually if I can tart up the Access report I can do away with the report in Excel. On the other hand, if people at the club want to keep the scores of their own team and they don't have Access they can just enter the differences into Excel and print the report from there.

Thread: Excel update sheet from Access (Access 2002)

Thread Tools