analyst

We have shared drives at work with macros in different excel spreadsheets. These macros work fine for my colleagues, but not for me. I am using the same excel file as them so I am puzzled as to why I get blocked with the error message ‘\USFIDATAFUTURES$ICEBNUBNU_MBCAA_YYYY01DD.csv’ cannot be found. Check your spelling, or try a different path.

when I copy and paste this link in my browswer, it’s fine and I can access the folder.

This is the macro in question, with the issue/bug in bold and highlighted:

Sub LoadMBCAA()
‘ ImportMBCAA Macro
 Dim Wb As Workbook
    Set Wb = ActiveWorkbook
    Set Wb = ThisWorkbook
    ‘ This macro will open the BNU MBCAA file of the day’
    Sheets(“BNU_MBCAA”).Select
    Columns(“B:L”).Select
    Selection.clearcontents
    Sheets(“Sheet1”).Select
    PathName = Range(“B4”).Value
    Filename = Range(“A2”).Value
    TabName = Range(“D5”).Value
    ControlFile = ActiveWorkbook.Name
    Workbooks.Open Filename:=PathName & Filename
    Range(Selection, Selection.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Wb.Activate
    Sheets(“BNU_MBCAA”).Select
    Range(“B1”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range(“A1”).Select
              ActiveCell.Copy
        Workbooks(Filename).Close
        Sheets(“SUMMARY”).Select

 

when I hover the mouse over the yellow, I see Filename = ”BNU_MBCA_YYYY01DD” under PathName I see the drive path and under FileName I see the file name. So I’m very puzzled as to why it’s not working and cannot get picked up.

if anyone can help, I will be forever grateful.

By: michael

Leave a Reply

Your email address will not be published. Required fields are marked *