ActiveWorkbook.Path issues with UNC

[mdex]

I've been learning more and more about Macros and VBA to automate a small report I need to perform on a daily basis.

I still can't write code from memory but understand a little which is why I'm dangerous. With a bit of googling and modification the code was tested and all working locally before moving to the network drive location. After moving, my code falls down at pretty much the first action of finding the name of a file to copy into the existing report.

The reason I'm not using explicit filenames is because there is date added to the file I'm copying in.

This line is where I get a Run-time error '52' Bad filename or number.

sFound = Dir(ActiveWorkbook.Path & "\MM Report*.xlsx")    'the first one found

When it fails out hovering over activeworkbook.path show's the UNC path \\blahblah\folder\folder2 but sFound is = "".

Sub Copy_PasteMM()
'Find the last used row in both sheets and copy and paste data below existing data.

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Dim sFound As String

sFound = Dir(ActiveWorkbook.Path & "\MM Report*.xlsx")    'the first one found

  'Set variables for copy and destination sheets
  Set wsCopy = Workbooks(sFound).Worksheets("Sheet1")
  Set wsDest = Workbooks("Contractor Validation Report Creator.xlsm").Worksheets("Master")
    
  '1. Find last used row in the copy range based on data in column A
  lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
    
  '2. Find first blank row in the destination range based on data in column A
  'Offset property moves down 1 row
  lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

  '3. Copy & Paste Data
  wsCopy.Range("A2:I" & lCopyLastRow).Copy _
    wsDest.Range("A" & lDestLastRow)
    
End Sub

Can I modify the existing code in any way to work with UNC paths?

[humdingaling]

see if this helps

Set wsCopy = Workbooks(ActiveWorkbook.Path & "\" & sFound).Worksheets("Sheet1")

[mdex]

see if this helps

Set wsCopy = Workbooks(ActiveWorkbook.Path & "\" & sFound).Worksheets("Sheet1")

It fails out before we even get to the copy stage. It fails defining sFound.

[romperstomper]

How long is the string returned by activeworkbook.path?

[mdex]

How long is the string returned by activeworkbook.path?

101 characters.

It's a mapped drive to a SharePoint document library if that makes any difference. It also has a @ in the path which could be causing the issue?

[romperstomper]

I don't have access to a Sharepoint folder to test but the @ shouldn't be a problem for Dir.

[mdex]

I don't have access to a Sharepoint folder to test but the @ shouldn't be a problem for Dir.

Definitly a sharpoint issue. Moved to regular network drive and it works fine.

I've since noticed that activeworkbook.path isn't returning the UNC path but the https:// path instead.

[romperstomper]

Ah, yes - that would be a problem.

[mdex]

Ah, yes - that would be a problem.

I've had to specify the UNC path to sharepoint rather than use ActiveWorkbook.path. Not the end of the world.