+ Reply to Thread
Results 1 to 26 of 26

Getting data from a closed workbook based on a cell reference with dynamic data.

  1. #1
    Registered User
    Join Date
    03-21-2017
    Location
    Tucson, AZ
    MS-Off Ver
    Office 365
    Posts
    27

    Getting data from a closed workbook based on a cell reference with dynamic data.

    I'm new to VBA and need some help. I've been able to write a script that will open a workbook based on the value of a cell and the predefined path to the workbook, get a piece of data and close the workbook and paste the value in a cell in the original workbook.
    1. I would like to get it so the macro doesn't open the workbook to get the data as there will be about 40 cells worth of data to retrieve per "job number".
    2. I can't figure out how to write the code so it does the entire range A2:A1500 without have a block of code for each cell. When I make it a range it puts the same data in all the cells in column M instead of referencing the job number from column K

    Please note that in the example workbook I have attached (hopefully), the sheet named RTR1 is a copy from a workbook named 4819.xls
    Maybe I'm going about this all wrong, but if anybody could please help it would be greatly appreciated.

    Josh
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    Opening the workbook is not a big deal - but you can also create links to specific cells on a know sheet:

    Please Login or Register  to view this content.
    Last edited by Bernie Deitrick; 03-23-2017 at 10:35 AM.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    03-21-2017
    Location
    Tucson, AZ
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    oh wow. ok let me try this out and see where I get. Thanks so much.

  4. #4
    Registered User
    Join Date
    03-21-2017
    Location
    Tucson, AZ
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    I've tried the code and it works as long as I select the sheet name I want for every cell it is linking. I changed
    Please Login or Register  to view this content.
    to be
    Please Login or Register  to view this content.
    but it still won't find that sheet in my workbooks. Also when it is setting the cell formulas I tried using
    Please Login or Register  to view this content.
    after
    Please Login or Register  to view this content.
    but I can't get that to work either. Any ideas? Thanks, Josh
    Last edited by 2015imsjfw; 03-23-2017 at 09:41 AM.

  5. #5
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    Are you creating links to cells from different sheets within the workbook? You would need to either loop, or use multiple formula strings as the basis of the formula.

    If you are capturing a row to use for the cells with the new links, then you would change, for example

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    03-21-2017
    Location
    Tucson, AZ
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    oh ok so use the .Cells variable...ok let me test that out. thanks much

  7. #7
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    Just for completeness, you could also use .Range (I just prefer .Cells - it is faster, generally)

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    03-21-2017
    Location
    Tucson, AZ
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    Please Login or Register  to view this content.
    That returned a Run time error 1006 on the .Cells line

  9. #9
    Registered User
    Join Date
    03-21-2017
    Location
    Tucson, AZ
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    I'm sorry if I am having trouble following you. I'm trying to read about VBA and how to structure code and learn when to use what variables and such.

  10. #10
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    Hi,

    You're adding a space to the sheet name here
    Please Login or Register  to view this content.
    That line should read
    Please Login or Register  to view this content.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  11. #11
    Registered User
    Join Date
    03-21-2017
    Location
    Tucson, AZ
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    It works!!! Thanks so much
    Now if I could figure out the whole row thing. Am I supposed to have "B" defined somewhere?

  12. #12
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    "B" is just a column letter, not a row.

  13. #13
    Registered User
    Join Date
    03-21-2017
    Location
    Tucson, AZ
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    Please Login or Register  to view this content.
    Got it now.... WOW Thank you all so much. Now that it works I can add some functions to make it easier... I have 10300 lines I have to do that for.

  14. #14
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    Good catch! Thanks - don't know where that space came from

  15. #15
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    IF there is some pattern to your cells on the sheet, you could use nested loops rather than hardcoded addresses...

  16. #16
    Registered User
    Join Date
    03-21-2017
    Location
    Tucson, AZ
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    Nested loops..... oh no..... you might have opened a bigger can of worms if I try to learn/use that....

  17. #17
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    I received an email where you talked about a run-time error. Note that you need to set the value for intRow prior to using it, like

    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    03-21-2017
    Location
    Tucson, AZ
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    yes I found my mistake after I looked back over my code and added the definition of intRow. How difficult would it be to add a userform that has a text box and a button that would activate the macro? the Form named get data with the layout and now I double clicked on the button to add the code but it stops at the top on the ().
    Please Login or Register  to view this content.

  19. #19
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    Start by removing the line

    Please Login or Register  to view this content.
    Every Sub declaration needs to be terminated with the line
    Please Login or Register  to view this content.
    before the next one - if you want to call another macro, just use its name, like

    Please Login or Register  to view this content.
    without the Sub or the ()
    Last edited by Bernie Deitrick; 03-23-2017 at 11:36 AM.

  20. #20
    Registered User
    Join Date
    03-21-2017
    Location
    Tucson, AZ
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    Please Login or Register  to view this content.
    Ok now it says Compile error:
    Method or data member not found

    and highlights the .txtValue part of the ID1 = line

    I'm guessing the ID1 is supposed to get whatever I put in the text box on the UserForm?

  21. #21
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    That line should be

    Please Login or Register  to view this content.
    What you have would work if the name of your userform is GetData and the name of your text box on GetData is txtValue. Try substituting the actual value - if you use autocomplete, try typing

    Please Login or Register  to view this content.
    After you type the . a selection box should come up, with all the objects and methods that apply to the userform, which you can select and then type another . etc..

    This shows where the names of you objects are found, when the text box is selected in you project:

    Capture.JPG
    Last edited by Bernie Deitrick; 03-23-2017 at 11:52 AM.

  22. #22
    Registered User
    Join Date
    03-21-2017
    Location
    Tucson, AZ
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    Ok I'm ready to learn nested loops. I've been trying to learn for the past few hours and I haven't gotten very far. I now have all our job numbers in Column B so I need the same macro to run then move down one row and instead of a dialog box opening for me to choose the file it can use Column B for the workbook name.
    Please Login or Register  to view this content.

  23. #23
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    Well, you need to know the path to the file and how the values in column B relate to the actual file name. But, your looping would be like this, though you appear to be overwriting column B

    Please Login or Register  to view this content.

  24. #24
    Registered User
    Join Date
    03-21-2017
    Location
    Tucson, AZ
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    Awesome. I am running it right now. If only the people that created the files would have used 1 format I would be home free.

  25. #25
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    You could have conditional loops based on some feature of the workbook that could be extractable with a formula, along the lines of:

    Set the formula to cell A1, calculate the formula - if it is "This" then use subroutine1, if it is "That" then use subroutine2, etc.

    The other thing you may want to consider is converting the formulas to values once they have been calculated - if you are doing a lot of files, re-calculation will be slow because of those links.

  26. #26
    Registered User
    Join Date
    03-21-2017
    Location
    Tucson, AZ
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Getting data from a closed workbook based on a cell reference with dynamic data.

    Yes I will certainly convert the formulas to values once it is done. Thanks for all your help. I'm sure I will be posting other questions soon.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Transfer data to closed workbook based on more conditions
    By Villalobos in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-20-2017, 01:39 PM
  2. Replies: 9
    Last Post: 09-13-2016, 09:47 AM
  3. Reference data in a closed workbook!!!!
    By marvinabou7890 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-15-2016, 05:22 PM
  4. Matching data from closed workbook with open workbook based on cell values
    By kbkrueger in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-14-2015, 07:42 PM
  5. Replies: 0
    Last Post: 03-27-2014, 12:38 PM
  6. [SOLVED] Data Validation List to Return Variable/Dynamic Data based on Reference Table
    By dilbert1865 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-13-2014, 09:08 AM
  7. [SOLVED] Filling multiselect listbox based on closed workbook data
    By FKurzhals in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 08-23-2013, 02:56 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1