I am using this code to get a cell value from a closed workbook...
Private Function GetValueFromClosedWorkbook(path, file, sheet, ref) Dim arg As String ' Check to see if th efile exists If Right(path, 1) <> "\" Then path = path & "\" If Dir(path & file) = "" Then GetValueFromClosedWorkbook = "File Not Found" Exit Function End If 'Create the argument arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1) 'MsgBox arg 'Run an XLM macro 'All refences must be given as R1C1 string GetValueFromClosedWorkbook = ExecuteExcel4Macro(arg) End Function Sub TestGetValueFromClosedWorkbook() Dim p As String, f As String Dim s As String, a As String p = ThisWorkbook.path f = "Number.xlsx" s = "Sheet1" a = "B1" 'MsgBox GetValueFromClosedWorkbook(p, f, s, a) ActiveSheet.Range("I10") = GetValueFromClosedWorkbook(p, f, s, a) End Sub
How can this code be changed so that it increments the number stored in cell "B1" in the "Number.xlsx" workbook by 1 and places that number in "I10" in the active workbook. Then takes the new number and places it back into cell "B1" (overriding the previous number).
Does that make sense?