Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    MatthewGrace is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    159

    Steal contents of Excel Cell


    Hello,

    I found a very cool snippet of code online which steals the contents of a cell from an Excel file and puts it into a variable in Access. It works like a charm... as long as that Excel file isn't actually open. Unfortunately, if it is open I get this message:


    Click image for larger version. 

Name:	Excel1.jpg 
Views:	25 
Size:	107.2 KB 
ID:	30368

    What I'm looking for is a way to get that cell even if the .XLS file is already open. I assume it will be 24/7.
    I suspect it's just a shortcoming of the .Open method, but I can't think of a workaround.
    Any ideas?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Try GetObject instead of CreateObject. Works for me.

    = GetObject(, "Excel.Application")

    I did not use the .Quit command.

    Unfortunately, the workbook keeps focus if it remains open.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    MatthewGrace is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    159
    Thank you June,

    You're right: While the .CreateObject works if the file is not already opened (and errors if it is), the .GetObject works if the file is opened (and errors if it is not).

    I suppose this means there is no way to find the cell regardless of whether the file is opened or not?

    If you don't know a way directly, perhaps you know a line of code that simply checks for whether it's already opened? If so, then I can easily "If opened Then .GetObject Else If Not opened Then .CreateObject" etc. Or is there a smarter way?

    Ty.

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    A link which may be useful: http://www.vbforums.com/showthread.php?t=391665

    There may be solution (untested) which may work when excel file was opened previously and when it wasn't:
    Create a new instance of Excel and create a new excel file;
    Into some cell, write the link formula to Excel file you want to read data from;
    Recalculate;
    Read wanted data from cell with link formula.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The GetObject worked for me whether the Excel file was open or closed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Check it for 2 cases:
    1 - you opened the Excel file yourself previously;
    2 - someone else in network opened the Excel file previously and keeps it opened.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I am not on a network. But good catch. In Task Manager discovered there was an Excel background process running although not listed under Apps. Removed it and now GetObject does not open file but don't get error either, just nothing happens because of "On Error Resume Next".

    Doing some testing and observing in Task Manager. Sometimes I get Excel background process and sometimes don't. If there is a residual background process then the file will reopen from the code. Now not getting the background process at all.

    Try:
    Code:
     On Error Resume Next
     Set xlx = GetObject(, "Excel.Application")
     If Err.Number <> 0 Then
           Set xlx = CreateObject("Excel.Application")
           blnEXCEL = True
     End If
     Err.Clear
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    Try out this dummy Excel trick I adviced before (create a new empty excel instance, use VBA to write a link into some cell on it to read data from other workbook (like ='YourPath\[ExcelFileWithData.xlsx]SheetWithData'!A2), use VBA to force recalculation of the formula in dummy Excel, read data from dummy Excel into Access, and close dummy Excel without saving).

    Link formulas aren't affected by source workbook being in use, and at same time they don't lock the source workbook - i.e. you can read data at any time without source file being in use or not affecting the process, and other users can open the workbook and work with it as usual. Data are read on open, or when you force calculation.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You could use parts of the code from Ken Snell's site http://www.accessmvp.com/kdsnell/EXC...m#WriteFileRst
    Delete the code from the "Set rs = " line to the "Loop" line and insert your code.......


    Something I found a while ago but haven't taken the time to explore, is to use a DNS-less connection to open a query on the workbook.
    http://www.accessmvp.com/kdsnell/EXC...ort.htm#ImpSQL

  10. #10
    MatthewGrace is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    159
    June: Thank you, I've copied your code and understand how it works. If the XLS file is currently open, it uses GetObject, if the XLS file is not currently open, it uses CreateObject. I like it. But I do have one problem you alluded to but cannot comprehend. I decided to check my Task Manager and notice that every time I press the command button which runs this code procedure, a new little excel icon is added to my "Processes" tab. If I press the button 6 or 7 times... I'm using half a gigabyte (see picture). Is there a way programmatically to ensure that these items are efficiently closed towards the end of my subroutine? I figured maybe "Set ExcelApp = Nothing"... this does not work.

    Click image for larger version. 

Name:	Untitled-1.jpg 
Views:	16 
Size:	149.2 KB 
ID:	30410

    Thank you to everyone else who has contributed, although some things are plainly over my head (especially regarding being on a network).

    @Arvi, if I understand you correctly, you're suggesting I create a new hidden .XLS file that nobody uses/knows about, which has cells linked to the important XLS. Then, use this .XLS as a "middleman", having confidence that it will not be opened. The .CreateObject will work every time in this model. Is that right?

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Additional code I did not include in earlier post:

    Set xlc = Nothing
    Set xls = Nothing
    'xlw.Close False
    Set xlw = Nothing
    'If blnEXCEL = True Then xlx.Quit
    Set xlx = Nothing
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Join Date
    Apr 2017
    Posts
    1,673
    About Excel icons in Task Manager: You open a new Excel instance every time you want to read data, but you don't close it after you read data. Read through link I did give before - there is info about this case.

    About dummy Excel: In general yes. The way I described it, this Excel file doesn't really exist anywhere - you simply create it in computers memory, use it, and then discard.
    Of-course you also can create a real Excel file and design needed link formulas into some table there (assumed addresses from where you read data from source file don't change), and link this table to your database. But anyway before reading the data from linked table, you have someway to force calculation in dummy Excel file (the real one saved on certain location, not the link in your database) to get fresh data (you can do it from Access using VBA, or you make a Window's scheduled task which opens, saves and then closes the dummy workbook - you get data 'not older as ...', or in case you use it seldom you can simply open the dummy workbook manually and save whenever you need to get fresh data).

  13. #13
    MatthewGrace is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    159
    I have tested, but the Task Manager icons do remain, which leads me to believe that the objects we created in order to manipulate Excel are still running and consuming space (90-100mb each). The only way to close them is to Ctrl+Alt+delete and manually find them in the Task Manager (see picture in previous post).

    In my quest for understanding, I typed in each line, one at a time, ran the procedure, then examined the Task Manager to see if an Excel icon would show up. I then flagged the line that causes its appearance. I then tried the usual .Close and/or Set = Nothing lines, but they have no affect on the background processes.

    Code:
    Private Sub cmdPermanentlyInTaskManager_Click()
    Dim ExcelApp As Excel.Application
    Set ExcelApp = CreateObject("Excel.Application")
    Dim WkBk As Excel.Workbook
    
    'The line below is the one that causes the the icon in the Task Manager.
    Set WkBk = ExcelApp.Workbooks.Open(FileName:="C:\Users\Matthew\OneDrive\NAS\New Age Manager\Master Costs3.xlsx")
    
    'Surprisingly, lines like these do nothing to remove them from the Task Manager:
    WkBk.Close
    Set WkBk = Nothing
    Set ExcelApp = Nothing
    End Sub

  14. #14
    MatthewGrace is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    159
    Arvi: I want to better understand your method but am too "wet behind the ears" to grasp it. I will have to play to understand more thoroughly how to assign cells to a XLS that only exists in code form, and also, why and how to "force calculation". I'm willing to learn, but I'm having trouble fleshing it out in my mind.

    That said, I'd like to know why it is not possible to close the Excel icon from the Task Manager, the way objects are normally discarded using .Close and Set=Nothing, thus liberating resources. Using June's method, I have everything working beautifully, save this one problem.

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Here is my complete procedure. For some reason I no longer see Excel listed in Task Manager Processes, although it does list under Apps. I even tested with the = Nothing lines commented and still not seeing. Something changed somewhere because I was seeing the Process before.
    Code:
    Dim xlx As Excel.Application, xlw As Excel.Workbook, xls As Excel.Worksheet, xlc As Excel.Range
    Dim blnEXCEL As Boolean
    blnEXCEL = False
    On Error Resume Next
    Set xlx = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Set xlx = CreateObject("Excel.Application")
        blnEXCEL = True
    End If
    Err.Clear
    xlx.Visible = True
    Set xlw = xlx.Workbooks.Open("C:\Users\June\Forums\test.xlsx", , True) ' opens in read-only mode
    Set xls = xlw.Worksheets("Rates")
    Set xlc = xls.Range("A1")
    Debug.Print xlc
    Set xlc = Nothing
    Set xls = Nothing
    'xlw.Close False
    Set xlw = Nothing
    'If blnEXCEL = True Then xlx.Quit
    Set xlx = Nothing
    Last edited by June7; 09-22-2017 at 11:02 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Exporting by unique values, and name/folder by cell contents
    By dakpluto in forum Import/Export Data
    Replies: 7
    Last Post: 02-18-2014, 11:48 AM
  2. Replies: 4
    Last Post: 01-20-2014, 11:50 AM
  3. Importing cell notes from excel
    By timmy in forum Import/Export Data
    Replies: 1
    Last Post: 03-12-2011, 01:34 PM
  4. Import single cell from excel
    By zippy483 in forum Import/Export Data
    Replies: 9
    Last Post: 02-24-2010, 02:16 PM
  5. Can we post Access cell data to Excel cell properties?
    By Zethro in forum Import/Export Data
    Replies: 1
    Last Post: 12-13-2005, 08:42 AM

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