Valami ilyesmi lesz a megoldás (nem teszteltem):

Function Open_Excel()
    Dim appexcel As Object, WB As Object, Month_Lee As String
    
    wNum = IsoWeekNumber(Now()) - 1
    Set appexcel = CreateObject("Excel.Application")
    appexcel.Visible = True
    Set WB = appexcel.Workbooks.Open("D:Weekly_Template_ChannelKnowledge_SuppliesInvalidPNs.xlsx")
    Month_Lee = WB.Sheets(1).Range("A1").Value
    WB.SaveAs "D:ChannelKnowledge_InvalidPNs W" & wNum & ".xls"
    Set WB = appexcel.Workbooks.Open("D:Monthly_Template_ChannelKnowledge_SuppliesInvalidPNs.xlsx")
    WB.SaveAs "D:ChannelKnowledge_InvalidPNs " & Month_Lee & ".xls"
End Function