Quote Originally Posted by Doc.AElstein View Post
...
Second attempt at code to get data from Big Closed Workbook
I will think about the Second attempt at code to get data from Big Closed Workbook with the “.........
_....................“perform the reference “ or “Get the reference” method or GetValue function way
GetReferrence = ExecuteExcel4Macro(FullReferrencecell)
Where GetReferrence , a String variable gets given the string of the held for the cell value..

Let GetValueOfClosedWBCell = ExecuteExcel4Macro(StrTemp)
.............................” ........idea !!...
-..So I looked at this at some length yesterday.... "Forays Down the Excel Range Referencing
( Addressing the Member Property .Address on referral Wonks)" ...)
http://www.excelforum.com/showthread...t=#post4483230
http://www.excelforum.com/showthread...t=#post4483344
http://www.excelforum.com/showthread...t=#post4483347
_.......
_..............And finally came up with a Function to replace a call to the spreadsheet like ( pseudo code )_..
StringValue = OpenWorkbook_Cell( r, c )_Value
_...
StringValue = FunctionGetFrmClsdWorkbook(ClosedWorkbook_Cell( r, c )

http://www.excelforum.com/showthread...t=#post4483417

_ It was quite an involved and detailed discussion and final code. It did all eventually work.. But I gave up waiting for the code to work through. So I redid the code, and hardcoded , rather than using the Function. As I understand it, Functions are only for convenience, and tidiness. In this case I had some extra lines checking that the closed File existed etc... etc.. So I thought if there was any mileage at all in this method I would strip it down to the minimum, so why I was at it I did away with the Function all together.
To replace the “tidiness” of a Function and for clarity, one just needs to very carefully write out a “Blue Print type code line, showing the direct equivalent of a “Spreadsheet Cell call interaction” with an Open Workbook with the equivalent = ExecuteExcel4Macro( code line.
_ Full details and explanations are given in the above referenced Threads. Here just summarised: ( using the actual Code lines for my code example )

Code lines Preliminary Stuff for open Workbook

110  '1a WB ws data
120  Dim WBBigACCESS As Workbook: Set WBBigACCESS = Workbooks("NutritionalValues2016.xlsx") ' Workbooks collection object of open Workbooks used to reference the massive data File
130  Dim wsBgAcs As Worksheet: Set wsBgAcs = WBBigACCESS.Worksheets("NutritionalValues")
140   wsBgAcs.Activate
150  Dim lr As Long: Let lr = wsBgAcs.Cells(Rows.Count, 10).End(xlUp).Row ' Last row in Nutritional Values Worksheet    ' The Range Object ( cell ) that is the last cell in the column of interest (CHOOSE a column typically that will always have a last Entry in any Data) ,( Row Number given by .Count Property applied to ( any Worksheet would do, so leaving unqualified is OK here, ) Spreadsheet Range Columns Property)    has the Property .End ( argument "Looking back up" ) applied to it. This Returns a new Range ( cell ) Object which is that of the first Range ( cell ) with something in it "looking back up" in the XL spreadsheet from that last Cell. Then the .Row Property is applied to return a long number equal to the Column number of that cell:   Rows.Count is the very last row number in your sheet. It is different for earlier versions of Excel.  The End(xlUp) is the same as pressing a Ctrl+UpArrow key combination. The final ".Row" returns the row where the cursor stops after moving up.
Code lines Preliminary Stuff for closed Workbook

110  '1a(ii) WB ws data for  GetValue(
'120  Dim FullPathAndFileName As String: Let FullPathAndFileName = "H:\ALERMK2014Marz2016\NährwerteTabelle\Sept2016\NutritionalValues2016.xlsx" 'Dim WBBigACCESS As Workbook: Set WBBigACCESS = Workbooks("NutritionalValues2016.xlsx") ' Workbooks collection object of open Workbooks used to referrence the massive data File
121  Dim FullPath As String: Let FullPath = "H:\ALERMK2014Marz2016\NährwerteTabelle\Sept2016": Let FullPath = ThisWorkbook.Path
122  Dim FullFileName As String: Let FullFileName = "NutritionalValues2016.xlsx"
130  Dim wsBgAcsName As String: Let wsBgAcsName = "NutritionalValues"                                        'Dim wsBgAcs As Worksheet: Set wsBgAcs = WBBigACCESS.Worksheets("NutritionalValues")
132  Dim RefClsdws As String: Let RefClsdws = "'" & FullPath & "\" & "[" & FullFileName & "]" & wsBgAcsName & "'" & "!": Debug.Print RefClsdws '  In Immediate Window (Ctrl+G) is seen    'H:\ALERMK2014Marz2016\NährwerteTabelle\Sept2016\Sept2016\[NutritionalValues2016.xlsx]NutritionalValues'!
140  '      wsBgAcs.Activate
150  Dim lr As Long: Let lr = 679239 'Hard Copy for initial experiments                                                      'Let lr = wsBgAcs.Cells(Rows.Count, 10).End(xlUp).Row ' Last row in Nutritional Values Worksheet. Hard copy in this test code
_...............................................

Typical “spreadsheet interaction” code bit for open workbook

StringValue = wsBgAcs.Range("J" & JayRow & "").Value

Typical equivalent using = ExecuteExcel4Macro(

StringValue = ExecuteExcel4Macro( RefClsdws & Range("J" & JayRow & "").Address (, , xlR1C1) )

_.. with JayRow = 2 the string for the ExecuteExcel4Macro argument looks like
'H:\ALERMK2014Marz2016\NährwerteTabelle\Sept2016\Sept2016\[NutritionalValues2016.xlsx]NutritionalValues'!R2C10

_.......................................

So I wrote the code _.... started it running_.... A day later it is still running, .. and hasn’t yet filled in the first 1 Dimensional String type Array ( 0 To 679238 ) .....

_...............

So while I am waiting I am looking along simplified things along these lines:
Quote Originally Posted by xlnitwit View Post
A further thought- if you have a database program such as Access or SQL Server Express, you may consider moving the data into it. It will be a better place to store so much data and you can query it from Excel as required. ...
ACCESS I do not have and SQL stuff is a big mystery to me my – sounds something to do with Computer stuff.. which I know nothing about, lol... I have a bit of experience in the VBA type stuff of the form like_...
Open FullTextFilePathAndName For Input As___
Open strFullPathAndFileNameForPrint For Output As ___
_.......and looping to read in or print out a line at time_...
_........... so i did a code for that, but I expect that is not the best way to do that...
Quote Originally Posted by xlnitwit View Post
.. Although you can do the same with a workbook as the data storage, it does not work well unless your data is of consistent type within each column.
Not quite sure what that last bit is saying..._..
_...But anyway I have started other Thread in parallel to look into this text file idea.. in particular I am thinking there is some way to efficiently get a whole column of data from a text file “in one go” rather than looping as I am in my Read ( Input ) and write ( Output ) text File attempts. Ideally I would want the columns put directly in an Array
http://www.excelforum.com/showthread...44#post4484344


Onward !
Alan
Bollox