Steal contents of Excel Cell

[ArviLaanemets]

About how Excel works.

When you open some workbook in Excel, then this workbook is read into computers memory and the workbook itself is flagged as in use by you. Any changes you are making in workbook are made in workbook's 'copy' in computers memory - the real workbook remains unchanged until you save the data (there are TEMP files Excel creates too, but we can ignore them).

When you create a new workbook in Excel, then it is created opening your (default) workbook template (a special file with his own file type/extension). And again the copy of this template is read into memory. When you now save workbook, an new workbook is created in selected destination (you can't save it back as template accidently - by default it will be saved as ordinary workbook). And the template will not be locked in process - you can create as many new workbooks as you want/as much your computer can handle.

Either existing or new workbooks don't exist in 'code form' - they exist as virtual copies of real workbook or template.


Why to force calculation:

Let us follow an example.
You have an Excel file with table header DateTime in A1 and the formula =NOW() in A2 formatted as "yyyy.mm.dd hh:mm:ss". When you entered the formula, the datetime at this moment is displayed in A2. This remains unchanged until you
a) enter something into any cell (NOW() being a volatile function is calculated whenever something changes in workbook - btw. this is not the case with most functions);
b) open the workbook (and probably when you save it too);
c) you force calculation for workbook manually (pressing F9) or through VBA.
And any calculations are made in virtual copy of workbook - they are appearing in your real workbook only after they are saved.
When you open the workbook and leave it unchanged for 10 hours, the datetime in cell A2 of virtual copy remains same as at opening, and in workbook on hard drive or in network resource same as it was before opening.
When nobody opens the workbook for a year, it keeps year-old datetime in A2.

Now when you link this workbook into your Access app, you see it as a table there, you can open it - and you see the datetime which was saved there p.e. year ago. To read current datetime from linked table, you have to make the excel file to recalculate the formula.
(At least I haven't seen anywhere any mention that Access forces formulas in linked Excel tables to be recalculated.)

[MatthewGrace]

June: Through trial, I learned what causes it to sometimes run under "Apps" vs. "Background Processes". If you do a .visible = True (and you did for your "xlx" variable), it will show up in Apps. If you either .visible = False, or - surprisingly - do an =Nothing, it will show up in Background Processes.

I learned from Arvi's first post link that Set=Nothing doesn't remove it from memory, but rather: "'Disconnect from Excel (let the user take over)"

What that means using my technique and June's, is that our code is not feasible because as we run our procedures repeatedly, the Task Manager will accumulate more and more running processes, hogging memory and eventually crashing. I am shocked there is no easy method of the Excel.Application method to simply exit the program and remove from memory no differently than if you pressed the X in the upper right corner. What a shame.

Arvi: I now understand what you mean by forcing recalculation. I will still need to play in order to implement a full solution based on what you're recommending. I have yet to go through the entire post you linked previously.

[June7]

Ah, that makes sense. I did have .Visible = False at first.

Take a look at https://www.codeproject.com/articles...ic-application

[ssanfu]

It is very strange that Excel continues to run in the background.
I have 4 routines that formats different Excel worksheets after I have exported an query... I don't have problems with Excel continuing to run in the background.

Here is part of the routine: (I cut out a lot of lines)
(See the last 8 lines)

Sub EditVendorWkSht(pWkshtPathName As String, pVendor As String, pMthYr As String)
    Dim xlx As Object
    Dim xlw As Object
    Dim xls As Object
    Dim sRow As Integer
    Dim eRow As Integer
    Dim RowDiff As Integer
    Dim blnEXCEL As Boolean

    blnEXCEL = False

    ' Establish an EXCEL application object
    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
    On Error GoTo 0

    ' Change True to False if you do not want the workbook to be
    ' visible when the code is running
    xlx.Visible = False

    Set xlw = xlx.Workbooks.Open(pWkshtPathName)

    'get worksheet name
    ' (note that the worksheet must already be in the EXCEL file)
    Set xls = xlw.Worksheets(1)

    'add two rows at the top
    xls.Rows("1").EntireRow.Insert
    xls.Rows("1").EntireRow.Insert
    xls.Range("A1").Select
    xls.Range("A1").FormulaR1C1 = pVendor
    xls.Range("A2").Select
    xls.Range("A2").FormulaR1C1 = pMthYr

    If pVendor = "Statement Monthly Details" Then
        With xls
        '
        ' lots of formatting code
        '
        End With
    End If

    ' Close the EXCEL file while saving the file, and clean up the EXCEL objects
    Set xls = Nothing
    xlw.Close True   ' close the EXCEL file and save the new data
    DoEvents   'had to add this - seems like the save needs time to finish
    Set xlw = Nothing
    If blnEXCEL = True Then
        xlx.Quit   '<<--- I noticed you don't have this command in your code
    End If
    Set xlx = Nothing
End Sub

[VicM]

Matthew,

If you haven't already solved your problem, the way I would approach this is to copy the spreadsheet, opened or not, to a local drive using the FileSystem object. Then extract your data from the copy.

Vic