CODEWITHSUNDEEP
excelvba
z_11122
z_11122

Reputation: 327

Updating file paths in excel/vba

I have a formula in an excel workbook that pulls data from another workbook as part of a macro. These two workbooks are in the same folder. However, I plan on sharing the folder with others so that they can utilize my macro to run reports. If another user opens these files, will the VBA code update automatically to wherever the new user saves the folder?

If not, is there a script I can write to update the file path automatically? Or is there at least a way I can make it easy for users who don't know VBA to update the macro so it works on their PC?

Here is my formula:

"=IFERROR(IF(RC[-12]=""Adjustment"",VLOOKUP(R[-1]C[-11],'C:\Attendance Audits FINAL\Test Final Package\Kronos Only\[Kronos Adjustment.xlsx]Sheet1'!R2C1:R531C3,3,FALSE)-R[-1]C[-1],""""),0)"

I am not really an expert in VBA myself, but my initial thought is to define a variable that contains the filepath so users would only have to change the name of the variable.

dim FP as string
FP = 'C:\Attendance Audits FINAL\Test Final Package\Kronos Only\

So then my formula would become:

"=IFERROR(IF(RC[-12]=""Adjustment"",VLOOKUP(R[-1]C[-11],FP[Kronos Adjustment.xlsx]Sheet1'!R2C1:R531C3,3,FALSE)-R[-1]C[-1],""""),0)"

I am not sure this will work. Any thoughts would be greatly appreciated!

Upvotes: 2

Views: 2620

Answers (3)

Ben Mega
Ben Mega

Reputation: 522

If you can be sure the two workbooks are in the same folder you can use the below code to replace your FP string.

Edit

Credit to VBasic2008 for this clearer code:

FP = ThisWorkbook.Path & "\"

Also, your code is going enter the letters "FP" into your formula instead of the value of the FP string. You need to concatenate like this:

"=IFERROR(IF(RC[-12]=""Adjustment"",VLOOKUP(R[-1]C[-11]," & FP & "[Kronos Adjustment.xlsx]Sheet1'!R2C1:R531C3,3,FALSE)-R[-1]C[-1],""""),0)"

Upvotes: 1

VBasic2008
VBasic2008

Reputation: 54807

Copy a Formula

  • In Excel, e.g. for the cell M2 you can do:
=IFERROR(IF(A2="Adjustment",VLOOKUP(B1,'[Kronos Adjustment.xlsx]Sheet1'!$A$2:$C$531,3,FALSE)-L1,""),0)

For the cells above and to the left, you will get wrong (unexpected) results.

  • In VBA you can use:
"=IFERROR(IF(RC[-12]=""Adjustment"",VLOOKUP(R[-1]C[-11],'[Kronos Adjustment.xlsx]Sheet1'!R2C1:R531C3,3,FALSE)-R[-1]C[-1],""""),0)"

Don't forget the first single quote '.

A VBA Practical Example

Sub WriteFormula()
    
    ' Source
    Const swbName As String = "Kronos Adjustment.xlsx"
    Const sName As String = "Sheet1"
    Const sfRow As Long = 2
    Const slRow As Long = 531
    Const sfCol As Long = 1
    Const slCol As Long = 3
    Const sCritCol As Long = 3
    ' Destination
    Const dName As String = "Sheet1" ' unknown
    Const dAddress As String = "M2:M21" ' unknown, possibly calculated
    Const drMin As Long = 2
    Const dcMin As Long = 13
    ' Both
    Const Criteria As String = "Adjustment"
    
    Dim dwb As Workbook: Set dwb = ThisWorkbook
    Dim dws As Worksheet: Set dws = dwb.Worksheets(dName)
    Dim drg As Range: Set drg = dws.Range(dAddress)
    ' Validate because there maybe unexpected results.
    With drg ' or With drg.Cells(1)
        If .Row < drMin Then Exit Sub
        If .Column < dcMin Then Exit Sub
    End With
    
    Dim FolderPath As String: FolderPath = dwb.Path
    
    ' Since the files are in the same folder, you could
    ' replace 'FolderPath & "\' with '"' in the formula
    ' and remove the previous line.
    ' But this could become useful e.g.
    ' in the case of 'FolderPath = dwb.Path & "\" & SubFolderName'.
    
    Dim dFormula As String
    dFormula = "=IFERROR(IF(RC[" & 1 - dcMin & "]=""" & Criteria _
        & """," & "VLOOKUP(R[" & 1 - drMin & "]C[" & 2 - dcMin _
        & "]," & "'" & FolderPath & "\[" & swbName & "]" & sName _
        & "'!" & "R" & sfRow & "C" & sfCol & ":R" & slRow & "C" & slCol _
        & "," & sCritCol & ",FALSE)-R[" & 1 - drMin _
        & "]C[" & 12 - dcMin & "],""""),0)"
    'Debug.Print dFormula
    
    drg.Formula = dFormula

End Sub

Upvotes: 0

sog
sog

Reputation: 532

I don't know if I quite understand your question but you can use this line to get the filepath regardless of the user: ThisWorkbook.Path()

Upvotes: 1

Related Questions