Importing data from Pivot Table?

[jstoler]

rpeare...take a look at what I put together that might make it clearer into what I'm trying to accomplish. Thanks!




Josh

[jstoler]

rpeare, maybe this will help.


Josh

[rpeare]

Ok.

Is the ORIGINAL excel file linked to any external source? (SQL Tables for example)

If it isn't the data is in your excel file, we just have to find it.

So instead of cutting and pasting what amounts to a screenshot into another excel file you want to put the information into an Access table and do your reporting from there.

In terms of your current excel file you'd be doing what amounts to this:

1. Open your excel file
2. Go to your PIPE BY HSS section
3. Choose the first branch you're interested in the FF Server/Branch combo box
4. Select the information displayed in cells AD20 through AHXX where XX represents the last row number of data.
5. Paste that information into an Access table
6. Repeat steps 3 - 5 for all branches

I am assuming you do not want to end up with an excel file (like the file 07.13.13 Run.xlsx you posted) you want an actual access table you can manipulate and report on as you want. Is that correct?

If the original excel file IS linked to an external source that's another problem entirely but I doubt it is if you're downloading it from a sharepoint site.

[jstoler]

I think you are correct. The original file is not linked to an external source so we should be good there. The one question I do have with Step #4 in your list is what if the range changes and extends past the last row of data due to grow of that branch? Can I have a selection that accounts for growth in the data range?

Thanks!

[rpeare]

Is there any chance you can get permission to share the original spreadsheet that you download with me. I can give you a private email in PM's if there's privacy information on there. (I work with restricted information every day so I know it can be a pain to get permission) I really need to see the construction of that original file because I believe what's happening is there's a raw set of data and your excel sheet is basically acting like a poor man's database to filter the data based on the information you enter.

Don't worry about the XX (row indicator) it's should be easy to detect the last row of something and dynamically create the range.

The range issue should not be a problem if my suspicion of the source file is correct though. Or, alternately, you can dig into the formulas on the source spreadsheet and find the original tab/data within the excel file and just import that source data directly into access.

[jstoler]

Also, when I copy/paste into an Access table, how does it know the source to Run it again from?

[jstoler]

unfortunately, I can't send the restricted info. I wish I could, sorry.

[rpeare]

Well if you can't even ask that really limits what I can do because I don't know the construction but here's the good news.... I can almost 100% guarantee that the data you need is already in that spreadsheet we just have to find it.

So to start looking for it... in the dropdown (combo) box that you're using to pick your branch. Right click that combo box and bring up the properties. Unless those values are typed in by someone there should be a reference somewhere in the properties you're looking for the sheet and cell range. I'm guessing this will lead you to a sheet that contains just a list of all your branch names.

What happens when you pick a branch, is everything automatically updated or do you have to trigger the refresh of data some way?

If it's automatic it likely means that you have a series of formulas in the data area that you want to copy. If there are no formulas there is likely some VBA script running when you choose a new branch.

If you post either the vba code (the code is going to be related to whatever triggers new data to be populated, if that's simply choosing a branch it will be related to that combo box) or the formulas that make up a single row of the data you're interested in we can start tracking down where the data is.