Macro to retrieve data from different spreadsheets

**bobs1** · 02-11-2010, 10:45 PM

Hello,

i currently use a spreadsheet that pulls sales data from several annual sales spreadsheets. but for the macro to work i need to have the spreadsheets open, and if i dont they return a #value. I need to modify the macro to pull the data through while the annual spread sheets are closed.

I think i need to use a filepath command or something but i dont really know macros very well. The current macro is:

PHP Code:


Function CLOOKUP(lookup) 
    arr = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec") 
    For Each entry In arr 
        With Workbooks("Sales Orders_2009.xls").Sheets("Sales" & entry) 
            For i = 10 To 34 
                If .Range("B" & i).Value = lookup Then 
                    CLOOKUP = .Range("H" & i).Value 
                    GoTo complete 
                End If 
            Next i 
        End With 
    Next entry 
complete: 
End Function

All the spread sheets are located in the same folder (R:\Market\Operational Reports). I tried putting the file path infront of the spreadsheet name, but that didnt work

any help would be great!

**Mallycat** · 02-12-2010, 12:17 AM

There is only one spreadsheet referenced in this function, which is Sales Orders_2009.xls. Do you have other functions that reference other sheets? The problem is that this is a function, and functions refresh automatically. I assume this function is used extensively in the sheet, so rewriting this as a macro would also not be trivial.

One thing you could try it turning off auto calc. The down side is you still have to refresh the sheet at some time, and you still need the other workbook open to do this.

A second approach would be to write a new macro to open the sheet (if it exists), turn on auto calc, then turn off auto calc, then close the sheet. You could put a button on your sheet to control this. First copy the code below into your module sheet. IMPORTANT. In the code, I refer to Book1.xls. You need to replace this with the name of your master workbook (the one with the #value errors).

To create a button, open the "forms" toolbar. Move to the first sheet in your summary sheet (the one that get's all the #value errors). click on the button on the form toolbar and then draw a button on your sheet. You will be prompted to select a macro - select OpenAndClose from the list. Then rename the button "refresh".

The macro assumes that your sheet in R:\Market\Operational Reports is closed.

If you have other sheets that you need to open, you will need to copy the Open and Close lines with the complete names of those files.

Please Login or Register  to view this content.

**bobs1** · 02-17-2010, 06:52 PM

ok giving it a go now

**bobs1** · 02-17-2010, 07:51 PM

ok so im giving the macro a go

PHP Code:


Sub OpenAndCalc() 
    On Error GoTo Error: 
    Workbooks.Open Filename:="R:\Market\Marketing Officer\Operational Reports\Sales Orders and Sales Proceeds Archive\Sales Orders_2009.xls" 
    Workbooks("Contract Analysis 2010.xls").Activate 
    Application.Calculation = xlAutomatic 
    Application.Calculation = xlManual 
    Workbooks("Sales Orders_2009.xls").Close 
GoTo ExitHere: 
Error: 
    y = MsgBox("file not found, file not updated", vbOKOnly) 
ExitHere: 
 
End Sub

if i open the spreadsheet with #value present and run the macro, it doesnt update them. If i open the spreadsheet, click into the cell and press enter have it still display #value, then run the macro it will update only that cell... help lol