+ Reply to Thread
Results 1 to 7 of 7

Get data from unopened file

  1. #1
    Forum Contributor
    Join Date
    07-13-2015
    Location
    Poland
    MS-Off Ver
    MS Office 365
    Posts
    171

    Get data from unopened file

    Hi all,

    I'd like to get some data (using vba vlookup) from workbook which is not opened yet.

    Is it possible to get data without opening that file?
    What code I should use? Open? GetData? GetObject?

    If opening file is needed I'd like to stay that file invisible.
    Then I probably need to use Application.Screenupdating = false

    Am I right?

  2. #2
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Get data from unopened file

    The code would be like that
    Please Login or Register  to view this content.
    This would copy range from closed workbook to the active workbook
    Hope it helps you
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  3. #3
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Get data from unopened file

    Hi zico8
    Quote Originally Posted by zico8 View Post
    .....
    Is it possible to get data without opening that file?....
    Yes and no. You can get the value out of any cell in a closed Workbook. ( without opening it) It is called “performing a reference”
    See code here:
    http://www.excelforum.com/developmen...ml#post4213824
    and here:
    http://spreadsheetpage.com/index.php...a_closed_file/
    Have a go, and let me know if you need more specific help
    Alan
    Last edited by Doc.AElstein; 10-28-2015 at 06:31 AM.
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  4. #4
    Forum Contributor
    Join Date
    07-13-2015
    Location
    Poland
    MS-Off Ver
    MS Office 365
    Posts
    171

    Re: Get data from unopened file

    Thanks for it,

    but some times ago I was told to apply GetObject:
    Please Login or Register  to view this content.
    or just:
    Please Login or Register  to view this content.
    In fact - its works, but can you tell me what is the difference to between GetObject, Workbooks.Open and just Workbook?
    Last edited by zico8; 10-28-2015 at 03:12 AM.

  5. #5
    Forum Contributor
    Join Date
    07-13-2015
    Location
    Poland
    MS-Off Ver
    MS Office 365
    Posts
    171

    Re: Get data from unopened file

    any comments?

  6. #6
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Get data from unopened file

    Hi zico8,
    Quote Originally Posted by zico8 View Post
    any comments?
    I have tried to experiment a little and have looked a lot in Internet for info.

    I do not have a complete answer for you. But I can tell you the following
    This statement
    Dim WB As Workbook
    Assigns a variable for holding a Workbook object.

    Now to your specific questions
    _1) Set WB = Workbooks("myfile.xlsm")
    This Sets the WB Object to a specific File, here the opened file "myfile.xlsm". SO THE FILE "myfile.xlsm" MUST ALREADY BE OPEN FOR THIS TO WORK.

    _2) Set WB = Workbooks.Open(ThisWorkbook.path & "\myfile.xlsm")
    This opens the Workbook "\myfile.xlsm" and sets the WB Object to this file "\myfile.xlsm". So the File must exist but does not have to be open. If it is open this code line will not error

    _3) Set WB = GetObject(ThisWorkbook.path & "\myfile.xlsm")
    This appears to do the same as _2) , Except the file is not visible. I have not been able to find a way to make this visible. So to manipulate it you must use VBA code. It is good practice to add a line to release the reference to this Object.

    Hope that helps a bit.

    And here is a code i used to test the above:

    Please Login or Register  to view this content.

    Alan

    P.s. The further code below demonstrate how to do the “Performing a reference”, that is to say it gets data from a closed File without opening that File, as per your original request.

    Please Login or Register  to view this content.

  7. #7
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Get data from unopened file

    EDIT:
    To get code line 3) to work as code line 2) , an extra line is required to give in total these two code lines
    Set WB = GetObject(ThisWorkbook.path & "\myfile.xlsm")
    WB.Windows(1).Visible = True


    Thanks to snb
    http://www.excelforum.com/the-water-...ml#post4227653
    for that one
    Alan

    and again
    http://www.excelforum.com/the-water-...ml#post4228123
    Last edited by Doc.AElstein; 10-29-2015 at 07:07 AM.

+ 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. Reference to a cell in an unopened file
    By aresquare1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-12-2015, 09:10 AM
  2. [SOLVED] Is it possible to save data entered in a userform to an external (unopened) excel file?
    By mrapes in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 06-11-2012, 06:46 PM
  3. Open unopened file
    By emjbee in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-25-2011, 07:34 PM
  4. Open unopened file
    By emjbee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2011, 07:44 PM
  5. copying data from an unopened file programmatically
    By maxzsim in forum Excel General
    Replies: 2
    Last Post: 11-30-2005, 10:35 PM
  6. [SOLVED] Getting value from an unopened file (indirectly referenced)
    By learnexcel in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-26-2005, 09:05 PM
  7. [SOLVED] Push/write data into an unopened file
    By Rod in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-27-2005, 11:06 AM

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