Macro to retrieve data from different spreadsheets
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
Re: Macro to retrieve data from different spreadsheets
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.
Re: Macro to retrieve data from different spreadsheets
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
Bookmarks