none
Dir() problem over a network . . . RRS feed

  • Question

  • Hy,

    I am using Dir() to manipulate some files using VBA in Excel 2007 and the macro runs in Excel 2003.
    The Dir() works flawlessly on files and folder on the same machine but problems when used to open files on another pc on a network.
    I have the workbook containing the macro on my machine and the folder containing files to be manipulated on some other computer on the network. I have set the path correctly but when i run the macro it returns zero files in the folder.

    I even used a simple file counting macro to count some files in a folder and set the path to another computer on a network but my macro returns 0 count because the Dir() returns no file.

    How do i know it's the Dir()? That is because i tested the same file counting macro with FileSearch and it worked. So for now i only know that there is something wrong with Dir() but i don't know what.

    I could use FileSearch but as i said i am using Excel 2007 and the bigger problem is that i have already completed most of the work using Dir()s and i can't just simply change all of it without installing 2003.

    So please tell me what could be the problem, i remember searching for some solution and somewhere i found the use of Trim() on the path but can't remember exactly, please help me out.

    Any help will be appreciated.

    Regards.

    Saturday, May 22, 2010 7:16 PM

All replies

  • What argument did you give to the first call of Dir ?
    What version of Windows are you using?
    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    Sunday, May 23, 2010 6:53 AM
  • Before calling Dir() i set the path using chdir("XXXX") then dir("*.xls").

    I am using XP sp3 for development and there is XP as well on the computers on network but don't know about the service pack, most probably it'll be 2 or 3.

    The problem is not because of arguments or windows version, as i said, the macro gives correct results if the folder exists on the same computer from which macro is being run but returns no result if the folder is on some other computer. And that FileSearch returns correct results in both cases.

    Thanks for your reply Bill, waiting for a solution.

    Regards.

    Sunday, May 23, 2010 7:12 AM
  • The problem is with ChDir, I think.  It doesn't do what you think for a network folder.

    Try Dir("XXXX\*.xls")


    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    Sunday, May 23, 2010 7:51 AM
  • I'll go further than Bill: I don't believe it even works on the one
    computer, only one the one drive. Even on your computer you would need to do
    a ChDrive as well as a ChDir for it to work properly
     
    You really should specify the full path on the first call to Dir. According
    to Help an error occurs if you don't, although that clearly isn't true.
     
    --
     
    "casanovaC4" wrote in message news:157526fb-9d7f-4d1f-bd21-9691049c390f...
    > Before calling Dir() i set the path using chdir("XXXX") then dir("*.xls").
    >
    > I am using XP sp3 for development and there is XP as well on the computers
    > on network but don't know about the service pack, most probably it'll be 2
    > or 3.
    >
    > The problem is not because of arguments or windows version, as i said, the
    > macro gives correct results if the folder exists on the same computer from
    > which macro is being run but returns no result if the folder is on some
    > other computer. And that FileSearch returns correct results in both cases.
    >
    > Thanks for your reply Bill, waiting for a solution.
    >
    > Regards.
    >
     
     

    Enjoy,
    Tony
    www.WordArticles.com
    Sunday, May 23, 2010 10:23 AM
  • Maybe this will help
     
    See the second macro that use ChDirNet
     
     
     
     

    --

    Regards Ron de Bruin
    http://www.rondebruin.nl/tips.htm
     
     
    "Tony Jollans" wrote in message news:92a76cce-87b4-4895-8186-2ddc725b1791...
    I'll go further than Bill: I don't believe it even works on the one
    computer, only one the one drive. Even on your computer you would need to do
    a ChDrive as well as a ChDir for it to work properly
     
    You really should specify the full path on the first call to Dir. According
    to Help an error occurs if you don't, although that clearly isn't true.
     
    --
     
    "casanovaC4" wrote in message news:157526fb-9d7f-4d1f-bd21-9691049c390f...
    > Before calling Dir() i set the path using chdir("XXXX") then dir("*.xls").
    >
    > I am using XP sp3 for development and there is XP as well on the computers
    > on network but don't know about the service pack, most probably it'll be 2
    > or 3.
    >
    > The problem is not because of arguments or windows version, as i said, the
    > macro gives correct results if the folder exists on the same computer from
    > which macro is being run but returns no result if the folder is on some
    > other computer. And that FileSearch returns correct results in both cases.
    >
    > Thanks for your reply Bill, waiting for a solution.
    >
    > Regards.
    >
     
     

    Enjoy,
    Tony
    www.WordArticles.com

    Regards Ron de Bruin http://www.rondebruin.nl/tips.htm
    Sunday, May 23, 2010 11:44 AM
  • This is how i setup the path when macro looks for files in the same machine,

    chdir("c:\folder\subfolder")
    fileName = dir("*.xls")

    And, this is the network path,

    chdir("\\targetmachine0123\")
    fileName = dir("*.xls")

    in case of network there is no drive letter in the path, but then why does this work in FileSearch if not in Dir()?

    Hope this helps.

    Sunday, May 23, 2010 4:37 PM
  • > chdir("c:\folder\subfolder")
     
    .. works because your current drive is C:
     
    > chdir("\\targetmachine0123\")
     
    After this, try doing "Debug.Print CurDir" to see what effect it has.
     
    --
    Enjoy,
    Tony
     
    www.WordArticles.com
     
    "casanovaC4" wrote in message news:a38944e2-6553-4e76-8d54-1239035ca5ee...
    > This is how i setup the path when macro looks for files in the same
    > machine,
    >
    > chdir("c:\folder\subfolder")
    > fileName = dir("*.xls")
    >
    > And, this is the network path,
    >
    > chdir("\\targetmachine0123\")
    > fileName = dir("*.xls")
    >
    > in case of network there is no drive letter in the path, but then why does
    > this work in FileSearch if not in Dir()?
    >
    > Hope this helps.
    >
     
     

    Enjoy,
    Tony
    www.WordArticles.com
    Sunday, May 23, 2010 5:32 PM
  • Here's some overkill, but might help...

    '/========================================================/
    '  Sub Purpose:
    '   History:
    '      07/15/2000 added hyperlink
    '      07/17/2000 added filename filter
    '      07/20/2000 added # files found info & criteria info
    '      07/27/2000 added extension as separate column
    '      08/03/2000 changed # files found to 'count' formula
    '      10/23/2000 add status bar 'Wait' message
    '      01/18/2007 change to FileDialog property
    '      05/14/2009 change from FileSearch to recursive DIR
    '                 for 2007 comparability (Dir Recursive
    '                 basic concept from MrExcel.com)
    '      05/04/2010 change to array for adding data to wrkst
    '
    '/========================================================/
    '
    Public Sub ListFilesToWorksheet()
      Dim blnSubFolders As Boolean
      Dim dblLastRow As Long
      Dim R As Integer, x As Integer
      Dim y As Integer, iWorksheets As Integer
      Dim i As Long, j As Long, k As Long
      Dim FSO As Object
      Dim Msg As String, strDirectory As String, strPath As String
      Dim strResultsTableName As String, strFileName As String
      Dim strWorksheetName As String
      Dim strArr() As String
      Dim strName As String
      Dim strFileNameFilter As String, strDefaultMatch As String
      Dim strExtension As String, strFileBoxDesc As String
      Dim strMessage_Wait1 As String, strMessage_Wait2 As String
      Dim varSubFolders As Variant, varAnswer As String

      On Error Resume Next

      '- - - - V A R I A B L E S - - - - - - - - -
      strResultsTableName = "File_Listing"
      strDefaultMatch = "*.*"
      R = 1
      i = 1
      blnSubFolders = False
      strMessage_Wait1 = _
        "Please wait while search is in progress..."
      strMessage_Wait2 = _
        "Please wait while formatting is completed..."
      ReDim strArr(1 To 65536, 1 To 10)
      '- - - - - - - - - - - - - - - - - - - - - -

      strFileNameFilter = _
        InputBox("Ex:  *.* with find all files" & vbCr & _
        "     blank will find all Office files" & vbCr & _
        "     *.xls will find all Excel files" & vbCr & _
        "     G*.doc will find all Word files beginning with G" _
        & vbCr & _
        "     Test.txt will find only the files named TEST.TXT" _
        & vbCr, _
        "Enter file name to match:", Default:=strDefaultMatch)

      If Len(strFileNameFilter) = 0 Then
        varAnswer = _
          MsgBox("Continue Search?", vbExclamation + vbYesNo, _
          "Cancel or Continue...")
        If varAnswer = vbNo Then
          GoTo Exit_ListFiles
        End If
      End If

      If Len(strFileNameFilter) = 0 Then
          strFileBoxDesc = "*.*"
          strFileNameFilter = "*.*"
        Else
          strFileBoxDesc = strFileNameFilter
      End If

      Msg = "Select location of files to be " & _
          "listed or press Cancel."
     
      'Allow user to select folder(s)
      With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .InitialFileName = Left(ActiveWorkbook.FullName, _
           Len(ActiveWorkbook.FullName) - Len(ActiveWorkbook.name))
        .Title = Msg
        .Show
        strDirectory = .SelectedItems(1)
      End With
     
      If strDirectory = "" Then
        Exit Sub
      End If
     
      If Right(strDirectory, 1) <> Application.PathSeparator Then
        strDirectory = strDirectory & Application.PathSeparator
      End If

      varSubFolders = _
        MsgBox("Search Sub-Folders of " & strDirectory & " ?", _
        vbInformation + vbYesNoCancel, "Search Sub-Folders?")

      If varSubFolders = vbYes Then blnSubFolders = True
      If varSubFolders = vbNo Then blnSubFolders = False
      If varSubFolders = vbCancel Then Exit Sub

      'check for an active workbook
      ' if no workbooks open, create one
      If ActiveWorkbook Is Nothing Then
        Workbooks.Add
      End If

      'save name of current worksheet
      strWorksheetName = ActiveSheet.name

      'Count number of worksheets in workbook
      iWorksheets = ActiveWorkbook.Sheets.Count

      'Check for duplicate Worksheet name
      i = ActiveWorkbook.Sheets.Count
      For x = 1 To i
        If UCase(Worksheets(x).name) = _
          UCase(strResultsTableName) Then
          Worksheets(x).Activate
          If Err.Number = 9 Then
            Exit For
          End If
          Application.DisplayAlerts = False 'turn warnings off
          ActiveWindow.SelectedSheets.Delete
          Application.DisplayAlerts = True  'turn warnings on
          Exit For
        End If
      Next

      'Add new worksheet where results will be located
      Worksheets.Add.Move After:=Worksheets(ActiveSheet.name)

     'Name the new worksheet and set up Titles
      ActiveWorkbook.ActiveSheet.name = strResultsTableName
      Range("A1:J1") = _
        Array("Hyperlink", "Path", "FileName", "Extension", _
        "Size", "Last Modified", "Last Accessed", "Created", _
        "Attribute", "Type")
      Range("A1:E1").Font.Bold = True

    '  R = R + 1

      'get 1st filename
      strName = Dir(strDirectory & strFileNameFilter)

      On Error Resume Next
      Application.StatusBar = strMessage_Wait1
     
      'put filenames and file info into array
      Do While strName <> vbNullString
        k = k + 1
        strArr(k, 1) = strDirectory & strName
        strArr(k, 2) = FileLen(strDirectory & strName)
        strArr(k, 3) = FileDateTime(strDirectory & "\" & strName)
        strName = Dir()
      Loop
     
      'create late-binding file objects
      Set FSO = CreateObject("Scripting.FileSystemObject")
     
      'get subfolder filenames if subfolder option selected
      If blnSubFolders Then
        Call recurseSubFolders(FSO.GetFolder(strDirectory), _
          strArr(), k, strFileNameFilter)
      End If
       
      'put file info into array
      If k > 0 Then
        For i = 1 To k
          strFileName = ""
          strPath = ""
          For y = Len(strArr(i, 1)) To 1 Step -1
            If Mid(strArr(i, 1), y, 1) = _
              Application.PathSeparator Then
              Exit For
            End If
            strFileName = _
              Mid(strArr(i, 1), y, 1) & strFileName
          Next y
          strPath = _
            Left(strArr(i, 1), _
            Len(strArr(i, 1)) - Len(strFileName))
          strExtension = ""
          For y = Len(strFileName) To 1 Step -1
            If Mid(strFileName, y, 1) = "." Then
              If Len(strFileName) - y <> 0 Then
                strExtension = Right(strFileName, _
                  Len(strFileName) - y + 1)
                strFileName = Left(strFileName, y - 1)
                Exit For
              End If
            End If
          Next y
          strArr(R, 1) = strArr(i, 1)
          strArr(R, 2) = strPath
          strArr(R, 3) = strFileName
          strArr(R, 4) = strExtension
          strArr(R, 5) = Format(FileLen(strArr(i, 1)), "#,##0")
          strArr(R, 6) = _
            Format(FSO.GetFile(strArr(i, 1)).DateLastModified, _
            "mm/dd/yyyy  hh:mm:ss")
          strArr(R, 7) = _
            Format(FSO.GetFile(strArr(i, 1)).DateLastAccessed, _
            "mm/dd/yyyy  hh:mm:ss")
          strArr(R, 8) = _
            Format(FSO.GetFile(strArr(i, 1)).DateCreated, _
            "mm/dd/yyyy  hh:mm:ss")
          strArr(R, 9) = GetFileAttributeName(GetAttr(strArr(i, 1)))
          strArr(R, 10) = FSO.GetFile(strArr(i, 1)).Type
          R = R + 1
        Next i
      End If
     
      'put file info on worksheet
      Application.ActiveSheet.Range("A2:J" & R) = strArr
     
      'add hyperlink to data
      Range("A2").Select
      R = 0
      Do
        ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell.Offset(R, 0), _
          Address:=ActiveCell.Offset(R, 0).value
        R = R + 1
      Loop While Len(ActiveCell.Offset(R, 0).value) <> 0
     
      'formatting
      Application.StatusBar = strMessage_Wait2
      ActiveWindow.Zoom = 75
      Columns("E:E").Select
      With Selection
          .NumberFormat = _
            "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
      End With
      Columns("F:F").Select
      With Selection
          .HorizontalAlignment = xlLeft
      End With
      Columns("A:J").EntireColumn.AutoFit
      Columns("A:A").Select
      If Selection.ColumnWidth > 12 Then
          Selection.ColumnWidth = 12
      End If

      Range("A2").Select
      ActiveWindow.FreezePanes = True

      Rows("1:1").Select
      Selection.Insert Shift:=xlDown
     
      dblLastRow = 65000

      ActiveWorkbook.ActiveSheet.Range("A1").WrapText = False
      If Len(strFileNameFilter) = 0 Then
        strFileNameFilter = "*.*"
      End If
      If blnSubFolders Then
        strDirectory = "(including Subfolders) - " & strDirectory
      End If

      Application.ActiveCell.Formula = "=SUBTOTAL(3,A3:A" & _
        dblLastRow & ") & " & Chr(34) & _
        " files(s) found for Criteria: " & _
        strDirectory & strFileNameFilter & Chr(34)
      Selection.Font.Bold = True

      Range("B3").Select
      Selection.Sort Key1:=Range("B3"), _
        Order1:=xlAscending, Key2:=Range("A3") _
        , Order2:=xlAscending, HEADER:=xlGuess, _
        OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom

      Range("A3").Select
     
    Application.Dialogs(xlDialogWorkbookName).Show

    Exit_ListFiles:
      Application.StatusBar = False
      Exit Sub

    Err_ListFiles:
      MsgBox "Error: " & Err & " - " & Err.Description
      Resume Exit_ListFiles

    End Sub

    '======================================================
    '  Sub Purpose:  recursive for filesearch 2007
    '/========================================================/
    '
    Private Sub recurseSubFolders(ByRef Folder As Object, _
      ByRef strArr() As String, _
      ByRef i As Long, _
      ByRef searchTerm As String)
      Dim SubFolder As Object
      Dim strName As String
     
      On Error GoTo err_Sub

      For Each SubFolder In Folder.SubFolders
        'get 1st filename in subfolder
        strName = Dir(SubFolder.Path & "\" & searchTerm)
        'put filenames and file info in subfolders into array
        Do While strName <> vbNullString
          i = i + 1
          strArr(i, 1) = SubFolder.Path & "\" & strName
          strArr(i, 2) = FileLen(SubFolder.Path & "\" & strName)
          strArr(i, 3) = FileDateTime(SubFolder.Path & "\" & strName)
          strName = Dir()
        Loop
        Call recurseSubFolders(SubFolder, strArr(), i, searchTerm)
      Next

    exit_Sub:
      On Error Resume Next
      Exit Sub

    err_Sub:
      Debug.Print "Error: " & Err.Number & " - (" & _
        Err.Description & _
        ") - Sub: recurseSubFolders - Module: " & _
        "Mod_Testing  - " & Now()
      GoTo exit_Sub

    End Sub
    '/========================================================/
    '  Function Purpose:  returns file attribute description
    '/========================================================/
    '
    Private Function GetFileAttributeName(fileAttribute As Long) As String
      On Error GoTo err_Function

      Select Case fileAttribute
        Case 0      'vbNormal
          GetFileAttributeName = "Normal"
        Case 1      'vbReadOnly
          GetFileAttributeName = "Read-Only"
        Case 2      'vbHidden
          GetFileAttributeName = "Hidden"
        Case 4      'vbSystem
          GetFileAttributeName = "System"
        Case 8      'vbVolume
          GetFileAttributeName = "Volume"
        Case 16     'vbDirectory
          GetFileAttributeName = "Directory"
        Case 32      'vbNormal
          GetFileAttributeName = "Normal"
        Case 33      'vbReadOnly
          GetFileAttributeName = "Read-Only"
        Case 34      'vbHidden
          GetFileAttributeName = "Hidden"
        Case Else
          GetFileAttributeName = "Unknown"
      End Select

    exit_Function:
      On Error Resume Next
      Exit Function

    err_Function:
      Debug.Print "Error: " & Err.Number & " - (" & _
        Err.Description & _
        ") - Function: GetFileAttributeName - " & Now()
      GoTo exit_Function

    End Function
    '/================================================/

     


    Gary Brown
    Monday, May 24, 2010 5:54 PM
  • As I suggested before

    fileName = Dir(\\targetmachine0123\*.xls)


    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    Thursday, May 27, 2010 6:38 AM
  • I noticed that Dir() doesn't give a correct answer when it is about a network address (and in my case a mapped drive). Solution that worked for me was:

    function file_exist(LinkName As Variant)
        On Error Resume Next
        Dim drive_folosit As String, substituent_drive As String, cale_drive As String
       drive_folosit = Left(LinkName, InStr(LinkName, ":"))
        substituent_drive = GETNETWORKPATH(drive_folosit)
        calea_drive = Left(substituent_drive, InStrRev(substituent_drive, "\") - 1)
        linkNameComplet = calea_drive & "\" & Mid(LinkName, Len(drive_folosit) + 2, Len(LinkName))
        linkNameComplet = calea_drive & "\" & Mid(LinkName, Len(drive_folosit) + 2, Len(LinkName))
        'file_exist1 = Dir(LinkName)   ' didn't work
        file_exist = Dir(linkNameComplet)

    End Function


    Function GETNETWORKPATH(ByVal DriveName As String) As String
        
        Dim objNtWork  As Object
        Dim objDrives  As Object
        Dim lngLoop    As Long
        
        
        Set objNtWork = CreateObject("WScript.Network")
        Set objDrives = objNtWork.enumnetworkdrives
        
        For lngLoop = 0 To objDrives.Count - 1 Step 2
            If UCase(objDrives.Item(lngLoop)) = UCase(DriveName) Then
                GETNETWORKPATH = objDrives.Item(lngLoop + 1)
                Exit For
            End If
        Next
    End Function

    Saturday, August 17, 2019 5:47 AM