Incrementing a value from another workbook

Feb 22nd 2016

#1

Hello,

I am using this code to get a cell value from a closed workbook...

Code

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

Display More

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?

Feb 23rd 2016

#2

Re: Incrementing a value from another workbook

This code opens the Numbers file and saves the new number in B1 in addition to putting the new number in I10.

Code

Private Function GetValueFromClosedWorkbook(path, file, sheet, ref) As String
    Dim arg As String, w As Workbook, l As Long
    ' 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


    'Check to see if the file is already open
    On Error Resume Next
    Set w = Workbooks(file)
    On Error GoTo 0
    If w Is Nothing Then Set w = Workbooks.Open(path & file)


    l = w.Sheets(sheet).Range(ref).Value + 1
    w.Sheets(sheet).Range(ref).Value = l
    w.Close True
    GetValueFromClosedWorkbook = CStr(l)
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"
    
    s = GetValueFromClosedWorkbook(p, f, s, a)
    If s <> "File Not Found" Then
        ActiveSheet.Range("I10") = CLng(s)
    Else
        MsgBox s
    End If
    
End Sub

Display More

SaraO

Student

Points

555

Posts

71
Feb 23rd 2016
- #3
Re: Incrementing a value from another workbook

Thank you shknbk2!!

I have another set of numbers that I want to use this code for, that number set has a letter before the number (B-160000).
shknbk2

Intermediate

Points

1,155

Posts

229
Feb 23rd 2016
- #4
Re: Incrementing a value from another workbook

You're welcome.

Are you additionally asking for more help with the number/letter combination or can you take it from here?
SaraO

Student

Points

555

Posts

71
Feb 23rd 2016
- #5
Re: Incrementing a value from another workbook

I tried but failed so I would really appreciate your help.

Please and thank you!

Feb 23rd 2016

#6

Re: Incrementing a value from another workbook

OK. Slight change to the function. "arg" gets the B1 value (which is a string), and L is set to the part of the text after the hyphen (in case the hyphen is not always in the second position) plus one. B1 is updated with the beginning string of the original value plus the new L.

Code

Private Function GetValueFromClosedWorkbook(path, file, sheet, ref) As String
    Dim arg As String, w As Workbook, l As Long
    ' 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


    'Check to see if the file is already open
    On Error Resume Next
    Set w = Workbooks(file)
    On Error GoTo 0
    If w Is Nothing Then Set w = Workbooks.Open(path & file)


    arg = w.Sheets(sheet).Range(ref).Value
    l = Right(arg, Len(arg) - InStr(1, arg, "-")) + 1
    w.Sheets(sheet).Range(ref).Value = Left(arg, InStr(1, arg, "-")) & CStr(l)
    w.Close True
    GetValueFromClosedWorkbook = CStr(l)
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"
    
    s = GetValueFromClosedWorkbook(p, f, s, a)
    If s <> "File Not Found" Then
        ActiveSheet.Range("I10") = CLng(s)
    Else
        MsgBox s
    End If
    
End Sub

Display More

Feb 23rd 2016

#7

Re: Incrementing a value from another workbook

Better, yet. This code below combines the two. If there is a hyphen, it is handled one way, and if not, another way.

Code

Private Function GetValueFromClosedWorkbook(path, file, sheet, ref) As String
    Dim arg As String, w As Workbook, l As Long
    ' 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




    'Check to see if the file is already open
    On Error Resume Next
    Set w = Workbooks(file)
    On Error GoTo 0
    If w Is Nothing Then Set w = Workbooks.Open(path & file)




    arg = w.Sheets(sheet).Range(ref).Value
    If InStr(1, arg, "-") > 0 Then
        l = Right(arg, Len(arg) - InStr(1, arg, "-")) + 1
        w.Sheets(sheet).Range(ref).Value = Left(arg, InStr(1, arg, "-")) & CStr(l)
    Else
        l = w.Sheets(sheet).Range(ref).Value + 1
        w.Sheets(sheet).Range(ref).Value = l
    End If
    w.Close True
    GetValueFromClosedWorkbook = CStr(l)
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"
    
    s = GetValueFromClosedWorkbook(p, f, s, a)
    If s <> "File Not Found" Then
        ActiveSheet.Range("I10") = CLng(s)
    Else
        MsgBox s
    End If
    
End Sub

Display More

SaraO

Student

Points

555

Posts

71
Feb 24th 2016
- #8
Re: Incrementing a value from another workbook

Thank you!

I would never have been able to figure this out on my own.

The code works great, just does not add the letter and hyphen when it places the number in "I10".

Feb 24th 2016

#9

Re: Incrementing a value from another workbook

Well, in that case, use this:

Code

Private Function GetValueFromClosedWorkbook(path, file, sheet, ref) As String
    Dim arg As String, w As Workbook, l As Long
    ' 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


    'Check to see if the file is already open
    On Error Resume Next
    Set w = Workbooks(file)
    On Error GoTo 0
    If w Is Nothing Then Set w = Workbooks.Open(path & file)


    arg = w.Sheets(sheet).Range(ref).Value
    If InStr(1, arg, "-") > 0 Then
        l = Right(arg, Len(arg) - InStr(1, arg, "-")) + 1
        w.Sheets(sheet).Range(ref).Value = Left(arg, InStr(1, arg, "-")) & CStr(l)
        GetValueFromClosedWorkbook = Left(arg, InStr(1, arg, "-")) & CStr(l)
    Else
        l = w.Sheets(sheet).Range(ref).Value + 1
        w.Sheets(sheet).Range(ref).Value = l
        GetValueFromClosedWorkbook = CStr(l)
    End If
    w.Close True
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"
    
    s = GetValueFromClosedWorkbook(p, f, s, a)
    If s <> "File Not Found" Then
        If InStr(1, s, "-") > 0 Then
            ActiveSheet.Range("I10") = s
        Else
            ActiveSheet.Range("I10") = CLng(s)
        End If
    Else
        MsgBox s
    End If
    
End Sub

Display More

SaraO

Student

Points

555

Posts

71
Feb 24th 2016
- #10
Re: Incrementing a value from another workbook

You are my hero!

Thank you so very much mucho!!
SaraO

Student

Points

555

Posts

71
May 3rd 2017
- #11
Re: Incrementing a value from another workbook

I am trying to use this code for a number set that does not contain the "-" and I get a run time error highlighting this line in the code...

Code

l = w.Sheets(sheet).Range(ref).Value + 1

Any idea on how to fix that?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login

Share

This site uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close

Your browser has JavaScript disabled. If you would like to use all features of this site, it is mandatory to enable JavaScript.