Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    jstoler is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2013
    Posts
    14

    Importing data from Pivot Table?

    Is there a way to import data from a pivot table like the "*****Pipe by HSS****" shown in the attachment? We have roughly 200 FF Server/Branches that I would like to have run automatically thru Access on daily basis instead of copying and pasting each Server/Branch after the source data has been updated. Unfortunately, I do not have access to the source data.



    Thank you.

    Josh
    Attached Files Attached Files

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    So you want to import only a part of that spreadsheet? it's possible to import ranges of cells into access.

    Here's one example:

    http://stackoverflow.com/questions/1...o-not-start-at

  3. #3
    jstoler is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2013
    Posts
    14
    Thanks rpeare, but will I be able to preset my database to pick up the different filters?

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    the picture of your spreadsheet isn't big enough for me to see individual items and I'm not sure what you mean by 'pick up different filters'. If you're talking about being able to find a particular string in one of your import fields you probably can yes.

  5. #5
    jstoler is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2013
    Posts
    14
    rpeare, I would like to send you the file to look at, but the data linked is secure information...how can I show you better?

  6. #6
    jstoler is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2013
    Posts
    14
    rpeare, is this attachment any better?
    Attached Files Attached Files

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    just create a bogus spreadsheet (take one you already have and just put garbage data in and save it with a new file name) then zip it up and upload it here and tell me what portions of the spreadsheet you're trying to import.

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    what portions of that spreadsheet are you trying to import? I had assumed you wanted *all* the data in cells starting under FF Server and HSS

  9. #9
    jstoler is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2013
    Posts
    14
    I'm having a hard time zipping the file, but what I have to do is pull the data string under the FF Server/Branch filter about 65 times for each different Server/Branch, then copy the data in the cells under FF Server and HSS. I'm trying to find a way to import the pivot and pull each individual data string automatically.

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    what do you mean 'filter about 65 times'. What you should be doing is importing ALL the data and separating it via queries in Access. I'm getting lost in your use of jargon.

    In terms of a database a STRING is just a text value (i.e. AAA10001, 10001, ABCDEF)

    Look at the first record in the picture you attached.

    What you want to end up with in your Access table is a record that has 5 data elements:

    FF_Server_and_HHS
    Purchase
    Refinance
    Piggyback_HE
    StandAlone_HE

    (I am leaving off the totals column because it's a calculated value and you can get the number by writing a formula in Access)

    And you want one line for each line in your spreadsheet.

    Is that correct?

  11. #11
    jstoler is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2013
    Posts
    14
    Unfortunately, that is not correct. Sorry, I'm not sure how to explain it without having access to the actual file. I'll try later to Zip something to show you.

  12. #12
    jstoler is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2013
    Posts
    14

    Attached Zip

    rpeare...see attached Zip for the output that I have to gather...you can see that I pull down the FF Server/Branch for each of these units then copy/paste the data under FF Server and HSS. I hope this helps. Thanks!
    Attached Files Attached Files

  13. #13
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok I think I see what you're saying.

    You have a spreadsheet that has multiple tabs
    Each tab has multiple sections of loan information
    each section of loan information may have 1 or more of the categories:

    Purchase
    Refinance
    Piggyback_HE
    StandAlone_HE

    You want to build a module that will extract the information for you and put it into a single table.

    I think it can probably be done but I do have one question.

    If this data is coming to you from an outside source, can you request the information in a different format or is this all you're going to get?

    I'll take a look at the code needed to handle it in the mean time.

  14. #14
    jstoler is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2013
    Posts
    14
    Unfortunately, I don't think you are seeing it correctly. That is not what the end result is supposed to be...Give tonight to put together something more clear and I'll reach out to you tomorrow. I appreciate you trying though. Thanks!

  15. #15
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok then I am totally lost as to what exactly you're trying to import, because each sheet has a different number of columns of loan information and different people and different locations within a state region.

    Perhaps if you give an example of what you expect your data to look like once it's done being imported.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export Data to Excel Pivot Table Loss of Formatting
    By Damian in forum Import/Export Data
    Replies: 1
    Last Post: 05-17-2013, 07:56 PM
  2. Replies: 2
    Last Post: 01-23-2013, 04:57 PM
  3. Pivot Chart using data from more than 1 table
    By mitch_pearce79 in forum Access
    Replies: 2
    Last Post: 12-16-2012, 06:36 AM
  4. Replies: 5
    Last Post: 12-03-2012, 05:03 PM
  5. Importing Excel data to an existing table
    By tonyrhills in forum Import/Export Data
    Replies: 3
    Last Post: 12-23-2011, 09:19 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums