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?