Macro into a Network Folder

[Jennifer25]

Hi looking for a bit of help please

I have my own C drive folder which I have a suite of reports set up which all contain macros that look to documents contained in my C Drive folder. I asked my company to create a local team folder so everyone in the team can access the reports and run the macros. My company have done this however the macro isn't working;
This is the old macro which works

Sub _Actuals()

Dim Fname As String
Dim SrcWbk As Workbook
Dim DestWbk As Workbook

strPath2 = "C:\Users\jmmaxwell\Desktop\PERSAPS\Bids_Projects\Variance Reports\MASTERCOPY_ Actuals"

Set DestWbk = ThisWorkbook
Set wbkWorkbook2 = Workbooks.Open(strPath2)
Range("A2:V20000").Select
Selection.Copy
Windows("Monthly Variance Report.xlsm").Activate
Sheets("Actuals").Select
Range("C2").Select
ActiveSheet.Paste

wbkWorkbook2.Close SaveChanges:=False

End Sub

This is the new Macro

Sub Actuals()

Dim Fname As String
Dim SrcWbk As Workbook
Dim DestWbk As Workbook

strPath2 = "\\rfrnhoffil01.rest.techscience.net\Cost Tracker\MASTERCOPY_ Actuals"

Set DestWbk = ThisWorkbook
Set wbkWorkbook2 = Workbooks.Open(strPath2)

Range("A2:V20000").Select
Selection.Copy
Windows("Monthly Variance Report YR1.xlsm").Activate
Sheets("Actuals").Select
Range("C2").Select
ActiveSheet.Paste
wbkWorkbook2.Close SaveChanges:=False

End Sub

I'm assuming the problem is down to strPath2 = "\\rfrnhoffil01.rest.techscience.net\OASIS Team Folder\Cost Tracker\MASTERCOPY_ Actuals"
It would appear that my company stores local folders onto a network drive, is there any way to make this macro work looking into documents stored into a folder on a network folder?

Thanks

Jennifer

 

[dmt32]

Hi

You show one path to file in your updated code yet show another further along?

Perhaps if users are accessing the file over the network may be better to offer ability to select file if hard coded path fails

Untested but see if this update to your code helps

Sub Actuals()
  
    Dim StrPath As Variant
    Dim wbkWorkbook2 As Workbook, DestWbk As Workbook
  
    strPath2 = "\\rfrnhoffil01.rest.techscience.net\OASIS Team Folder\Cost Tracker\MASTERCOPY_ Actuals"
  
    On Error GoTo myerror
  
    If Dir(strPath2, vbDirectory) = vbNullString Then
        strPath2 = GetFile
'Cancel Pressed
        If strPath2 = False Then Exit Sub
    End If
  
  Application.ScreenUpdating = False
    Set DestWbk = ThisWorkbook
    Set wbkWorkbook2 = Workbooks.Open(strPath2, False, True)
  
    With wbkWorkbook2
        .Sheets(1).Range("A2:V20000").Copy DestWbk.Sheets("Actuals").Range("C2")
        .Close False
    End With
myerror:
    Application.ScreenUpdating = True
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Function GetFile() As Variant

    Dim FileFilter As String
    Dim FilterIndx As Integer

    FilterIndx = IIf(Val(Application.Version) < 12, 1, 2)

    FileFilter = "Excel 2003 (*.xls),*.xls," & _
              "Excel 2007 > (*.xlsx),*.xlsx," & _
              "All Excel Files (*.xl*),*.xl*," & _
              "All Files (*.*),*.*"

    GetFile = Application.GetOpenFilename(FileFilter, FilterIndx, "Select One File To Open")
  
End Function

Ensure you copy BOTH CODES to standard module

Hope helpful

Dave