Read many cell values from closed workbook

[dsxtreme21]

Hi all--

I've recently discovered the usefulness of VB in excel and have managed to write some basic macros that enhance many of my workbooks. BUT, I've bumped into a problem I can't seem to solve. Please help!

I'm looking to import the values in a large range of cells ( 5 x 5000) in a closed workbook into a range that i specify in an open one, where both workbooks are housed in the same directory. I also want to include logic that allows me to move the 2 files to different directories (the names will never change, only the paths) and have the code still work--I believe i'd use relative path references?

I've found lots of snippets of code on the topic but can't seem to get any of them to work. For instance: http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/

Problem is, I'm so new that I don't even know where to begin pasting the code (objects vs. modules, etc) in the VBA editor.

thanks in advance to anyone who'll spend the time walking me through this!

 

[GTO]

I'm looking to import the values in a large range of cells ( 5 x 5000) in a closed workbook into a range that i specify in an open one, where both workbooks are housed in the same directory.

I also want to include logic that allows me to move the 2 files to different directories (the names will never change, only the paths) and have the code still work--I believe i'd use relative path references?

I've found lots of snippets of code on the topic but can't seem to get any of them to work. For instance: http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/

Greetings,

The link you provided shows an example of using ExecuteExcel4Macro to grab the value of a cell from a closed wb. While this is a handy feature indeed, when looping thru a bigger range, you may find this a bit slow. In essence, while snatching info from a closed wb can be faster than opening the wb, the wb is still loaded into memory ea time it is being read from. As you can imagine, calling the function 2500 times could slow things a bit.

Anyways, ADO would be one way to go, but I am awfully spotty on that, so how about we look at a couple of simple solutions:

These are based upon the two workbooks being in the same folder. "New Microsoft Excel Worksheet.xls" gets the code and is the destination wb, and "New Microsoft Excel Worksheet (2).xls" is the source. We'll get the values from "Sheet1" cells B2:I40, and stick these in "Sheet3" of the destination wb.

To simply create an array formula, then overwrite the range w/just the values:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> GetRangeOfVals()<br><SPAN style="color:#00007F">Dim</SPAN> wksDest <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> rngDest <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> strFull <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br>    <SPAN style="color:#00007F">Set</SPAN> wksDest = ThisWorkbook.Worksheets("Sheet3")<br>    <SPAN style="color:#00007F">Set</SPAN> rngDest = wksDest.Range("B2:I40")<br>    <br>    strPath = ThisWorkbook.Path & Application.PathSeparator<br>    strFull = "='" & strPath & "[New Microsoft Excel Worksheet (2).xls]Sheet1'!R2C2:R40C9"<br>    <br>    rngDest.FormulaArray = strFull<br>    rngDest.Value = rngDest.Value<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Or, is you don't mind programatically opening the wb momentarily, we could use Evaluate.


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> EvalRangeOfVals()<br><SPAN style="color:#00007F">Dim</SPAN> wksDest <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> rngDest <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> wbSource <SPAN style="color:#00007F">As</SPAN> Workbook<br><br>    <SPAN style="color:#00007F">Set</SPAN> wksDest = ThisWorkbook.Worksheets("Sheet3")<br>    <SPAN style="color:#00007F">Set</SPAN> rngDest = wksDest.Range("B2:I40")<br>    <br>    strPath = ThisWorkbook.Path & Application.PathSeparator<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wbSource = Workbooks.Open(strPath & "New Microsoft Excel Worksheet (2).xls", , <SPAN style="color:#00007F">True</SPAN>)<br>    <br>    rngDest.Value = Evaluate("='" & strPath & "[New Microsoft Excel Worksheet (2).xls]Sheet1'!B2:I40").Value<br>    <br>    wbSource.Close <SPAN style="color:#00007F">False</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Hope this helps,

Mark

 

[dsxtreme21]

Mark,

Thanks! It seems that this is exactly what I'm looking for and quite fast.

A few questions:

1. I seem to get an error when the source workbook is open too. Why and what, if anything, can be done to prevent this?

2. How exactly to I control which cells are retrieved and where they are ultimately placed? Do both ranges have to be the same?

Again, thank you for your time!

-Dan

 

[GTO]

1. I seem to get an error when the source workbook is open too. Why and what, if anything, can be done to prevent this?

I imagine you are talking about the first code, not the second... Not tested, but this should work. Basically if the workbook is open, the formula would not include the path.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> GetRangeOfVals()<br><SPAN style="color:#00007F">Dim</SPAN> wksDest <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> rngDest <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> strFull <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br><SPAN style="color:#00007F">Const</SPAN> WB_NAME <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "New Microsoft Excel Worksheet (2).xls"<br><br><br>    <SPAN style="color:#00007F">Set</SPAN> wksDest = ThisWorkbook.Worksheets("Sheet3")<br>    <SPAN style="color:#00007F">Set</SPAN> rngDest = wksDest.Range("B2:I40")<br>    <br>    strPath = ThisWorkbook.Path & Application.PathSeparator<br>    <br>    <SPAN style="color:#00007F">If</SPAN> Workbook_IsOpen(WB_NAME) <SPAN style="color:#00007F">Then</SPAN><br>        strFull = "[" & WB_NAME & "]Sheet1'!R2C2:R40C9"<br>    <SPAN style="color:#00007F">Else</SPAN><br>        strFull = "='" & strPath & "[" & WB_NAME & "]Sheet1'!R2C2:R40C9"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    rngDest.FormulaArray = strFull<br>    rngDest.Value = rngDest.Value<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Function</SPAN> Workbook_IsOpen(wbName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> wb <SPAN style="color:#00007F">As</SPAN> Workbook<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wb = Workbooks(wbName)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> wb <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> Workbook_IsOpen = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

Again, not tested, so shout out if it goobers...

2. How exactly to I control which cells are retrieved and where they are ultimately placed? Do both ranges have to be the same?

In either of these we are specifying a source range. The Evaluate method returns an array of values, but requires the source wb to be open. The first sub first plasters in an array formula and just overwrites the range with the values. Think of it like Copy/PasteSPecial(values).

If be the "same" you mean does the source and destination have to be on the same place in a sheet - no. If you were getting A1:B10, it could go to D11:E20 or wherever.

The source and destination ranges must be equally sized of course.

Mark

 

[dsxtreme21]

Mark,

hmmm...I'm still struggling grabbing the right cells. I want to "copy" E9:K5000 and paste to C10:H5001. Could you suggest the values I'd need to change in the code?

 

[GTO]

Give me:

1. The source sheet name
2. The soource workbook name
3. the destination sheet name
4. the destination workbbok name
5. IF they are to be in different folders, the full path to the source wb

Mark

 

[dsxtreme21]

Here is the code, is there somthing else you'd like me to give you? Thank you so much!



Option Explicit

Sub GetRangeOfVals()
Dim wksDest As Worksheet
Dim rngDest As Range
Dim strPath As String
Dim strFull As String

Set wksDest = ThisWorkbook.Worksheets("new revenue")
Set rngDest = wksDest.Range("C10:H5001")

strPath = ThisWorkbook.path & Application.PathSeparator
strFull = "='" & strPath & "[Complete Transaction History - OPM - D. Stephens N. Osborn - 2009.xls]Revenue Detail'!R2C2:R40C9"

rngDest.FormulaArray = strFull
rngDest.Value = rngDest.Value

End Sub

 

[GTO]

Okay, I see a problem. Your original post states a source range of 5 columns by 5000 rows.

Your destination range of C10:H5001 is 6 columns by 4992 rows.

Your source range of R2C2:R40C9 is 8 columns by 39 rows.

In R1C1 reference, the 'R' refers to the row (ie R1 is row 1) and the C is the column (ie R1C1:R10C3 would be the same as A1:C10)

That may be enough, but if not, just list the actual range from Revenue Detail in A1 style, for instance: "C2:H5001 is the source range"

Mark

 

[dsxtreme21]

Source Sheet Name: Revenue Detail
Source Workbook Name: Complete Transaction History - OPM - D. Stephens N. Osborn - 2009
Destination Sheet Name: new revenue
Destination Workbook Name: Monthly Summaries - OPM - D. Stephens N. Osborn - 2009
5. Always same folder

 

[GTO]

Source Sheet Name: Revenue Detail
Source Workbook Name: Complete Transaction History - OPM - D. Stephens N. Osborn - 2009
Destination Sheet Name: new revenue
Destination Workbook Name: Monthly Summaries - OPM - D. Stephens N. Osborn - 2009
5. Always same folder

Thank you, but I still need an accurate description of the source range, like "the source range is C2:H5001" or whatever it actually is.

Mark