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]

Well, I'm out, so will leave you this. This will give you 5 col by 5000 row ranges of equal size. Adjust to suit and I believe that should work

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> GetRangeOfVals()<br><SPAN style="color:#007F00">''Source Sheet Name: Revenue Detail</SPAN><br><SPAN style="color:#007F00">''Source Workbook Name: Complete Transaction History - OPM - D. Stephens N. Osborn - 2009</SPAN><br><br><SPAN style="color:#007F00">''Destination Sheet Name: new revenue</SPAN><br><SPAN style="color:#007F00">''Destination Workbook Name: Monthly Summaries - OPM - D. Stephens N. Osborn - 2009</SPAN><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> = "Complete Transaction History - OPM - D. Stephens N. Osborn - 2009.xls"<br><SPAN style="color:#00007F">Const</SPAN> WKS_NAME <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Revenue Detail"<br><br>    <SPAN style="color:#00007F">Set</SPAN> wksDest = ThisWorkbook.Worksheets("new revenue")<br>    <SPAN style="color:#00007F">Set</SPAN> rngDest = wksDest.Range("C10:G5009")<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 & "]" & WKS_NAME & "'!R2C2:R5001C6"<br>    <SPAN style="color:#00007F">Else</SPAN><br>        strFull = "='" & strPath & "[" & WB_NAME & "]" & WKS_NAME & "'!R2C2:R5001C6"<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>

Hope this helps,

Mark