Code to open filepath with inputs coming from cells?

[jckhnln]

Hey there,

I'm pretty new to writing VBA, but I'm making an attempt at automating a process I do monthly. Basically, the current issue is that I have a workbook I'm writing this Macro in and have a cell where the current date is pulled in, then some hidden cells which determine the previous month in multiple formats. I'm trying to write something that inserts the values in those cells into the filepath string to make it work month over month.

Currently what I have is...

Sub ProcessReport()
dim Fname as String
Dim wb as Workbook
Dim prevDate as String
prevDate - Format(Date, "MM MMM YYY")

Fname = "Z:\CURRENT\XXXX\Fixed Assets\2021 FA\" & Range(G6) & "\XXXXXXX\XXXX\" & Range(F6) & "XXXX Cost Rollforward"
Set wb = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, Notify:=False)
End sub

I'm getting a constant issue with the filepath string.
Like I said I'm very VERY new to this world or VBA so if there's a better way too feel free to let me know! I want this macro the open the prior month file which is in a folder that I want the macro to determine each month based on dates. Then I want it to create a copy of that file and save the copy into the current month folder with a new name. Ultimately so that I can work to update that new file after all this. Let me know your suggestions?? Or redirection if this is so wrong!
Thanks!!

 

[rlv01]

When you use a cell reference such as Range(G6), you must put quotation marks around the cell reference Range("G6"). Also, when building a filepath string it helps to initially use a MsgBox statement so you can inspect FName to see if it is giving you a valid file path.

A bit more advanced is to test whether ot not your path string is valid. See example below.

Sub ProcessReport()
    Dim Fname As String
    Dim wb As Workbook
    Dim prevDate As String
    
    prevDate = Format(Date, "MM MMM YYY")
    
    Fname = "Z:\CURRENT\XXXX\Fixed Assets\2021 FA\" & Range("G6").Value & "\XXXXXXX\XXXX\" & Range("F6").Value & "XXXX Cost Rollforward"
    
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    'Test to see if Fname is a file that exists.
    If Not fso.FileExists(Fname) Then
        MsgBox "File Not Found: " & vbCr & vbCr & Fname
        Exit Sub                                 'abort
    End If
    
    Set wb = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, Notify:=False)
End Sub