Import External Data

matthewR
5StarLounger
Posts: 627
Joined: 03 Feb 2010, 15:02

Import External Data

Post by matthewR »

I have a query in Access that is linked to a spreadsheet in Excel.
The query in Access is set up with the same columns as another spreadsheet in Excel.
What I am currently doing is, I have the spreadsheet that is linked to an Access query and then I have a spreadsheet that is set up by Manager.
This spreadsheet has totals and other formatting so I just copy what is needed from the Access query spreadsheet.
I put a + in the first cell of the first manager area, then I go to the Access query spreadsheet and highlight the first cell and hit enter which takes me back to the formatted spreadsheet.
Then I just copy over and down as far as the manager info goes.
I do this for each of the managers.

I just wondered if there was a more efficient way to do this. I have to do this mthly. The Access query will change every mth - shrink or expand. Any ideas would be appreciated.

User avatar
HansV
Administrator
Posts: 78987
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import External Data

Post by HansV »

Welcome to Eileen's Lounge!

Why do you have to repeat this each month? Once you've set up the formulas, they will update automatically. Or am I missing an essential point?
Best wishes,
Hans

matthewR
5StarLounger
Posts: 627
Joined: 03 Feb 2010, 15:02

Re: Import External Data

Post by matthewR »

The data could grow or shrink every month. I have to get rid of the excess rows if there is less data and add more rows if the data grows.

User avatar
HansV
Administrator
Posts: 78987
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import External Data

Post by HansV »

Where are the data entered? In Access or in Excel? That's not entirely clear to me from your description.
Best wishes,
Hans

matthewR
5StarLounger
Posts: 627
Joined: 03 Feb 2010, 15:02

Re: Import External Data

Post by matthewR »

The data is entered in Excel. I have an Access query that I use the "Import External Data" under the Data tab. Once this data is imported into Excel it is linked to the query in Excel. All I do when the query in Access changes is refresh the data in Excel. I then have another spreadsheet that is formated (meaning for every manager there is a total line and additional columns that are set up. I just have about 6 columns that I have to refresh for each manager. This formatted spreadsheet was set up by another person. She wants me to just enter the data around all her preformatting. I created the data (with corresponding columns) in Access to match the columns that I have to refresh. I sort it by manager name and then I just take each managers data and fill in her spreadsheet.

I just thought maybe there would be a more automated way to do this like with Offset etc.

User avatar
HansV
Administrator
Posts: 78987
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import External Data

Post by HansV »

I'm sorry, but your description is ambiguous. You write both "The data is entered in Excel" and "this data is imported into Excel". Which of the two is it?
Best wishes,
Hans

matthewR
5StarLounger
Posts: 627
Joined: 03 Feb 2010, 15:02

Re: Import External Data

Post by matthewR »

There are two spreadsheets - One spreadsheet is imported into Excel - I will call that spreadsheet 1. That spreadsheet has the updated information. The other spreadsheet (spreadsheet 2), has areas (by manager) where I fill in the data from Spreadsheet 1.

I setup the Access query (spreadsheet 1) to have the same columns as spreadsheet 2.

I have attached a spreadsheet with 2 tabs. One is the imported query from Access and the other is the spreadsheet I fill in with data from that query. I only fill in the data in columns A-H.

User avatar
HansV
Administrator
Posts: 78987
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import External Data

Post by HansV »

Ah, it's becoming clearer now, thank you. You could achieve the output in the second worksheet in the form of a report in Access based on the query, grouped by RVP. You could format that the way you want. Would that be feasible?
Best wishes,
Hans

matthewR
5StarLounger
Posts: 627
Joined: 03 Feb 2010, 15:02

Re: Import External Data

Post by matthewR »

I have to use the spreadsheet (spreadsheet 2) that is provided me. She has columns that she added that have calculations. The example I sent doesn't have all her calculations.

User avatar
HansV
Administrator
Posts: 78987
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import External Data

Post by HansV »

You could reproduce the calculations in the Access report.

Doing this in Excel is going to be nasty.
Best wishes,
Hans

matthewR
5StarLounger
Posts: 627
Joined: 03 Feb 2010, 15:02

Re: Import External Data

Post by matthewR »

What I am doing now works so maybe this is the best way. Some of her calculations are after the fact that she does to adjust the data so I don't think I can reproduce those in Access.

User avatar
HansV
Administrator
Posts: 78987
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import External Data

Post by HansV »

In that case, it might be best to continue as now.
Best wishes,
Hans