File Structure Issue with macro - Frustrated!

[leggylongshanks]

Hi All,

Don't get scared off by the bunch of writing below. I'm convinced that my problem can be easily solved by someone with slightly more knowledge than myself (and that's not saying much).

I finally found some code I was able to modify to suit my needs. I macro starts at a certain path, finds all the subfolders, finds excel worksheets with certain named cells, and then puts those cells into a single log sheet. It worked without any problems when I was testing it, but now when I try to use it to compile sheets that are shared with the rest of the office using a program called 'sugarsync' I am running into some problems. It must stem from the way this code finds folders/subfolder/and file names as I have had other code run without this problem. When I say the files are shared, they are still in my C drive, the program just has access and updates the files accordingly to other workers computers, it isn't having to grab them off a server or anything.

The first problem I have is an error message that will come up saying "file not found" and highlight the " If (GetAttr(strFolder & "\" & strFileName) And vbDirectory) = vbDirectory Then" line in the Process Files Sub. When I see what value is in the strFileName variable, it is indeed one that does not seem to exist. This issue does not happen every time and seems to go away depending on where I place my test folders.

The second problem I am having is the actual detection of some of the files. When I place them in a folder on my desktop and run the program to that folder (I have some random files and folders and some with the info I need to see if it can grab the right stuff) it works great. However, when I take that same folder and put it in a sugarsync shared folder it know it finds the name of each folder but it sometimes won't enter the information because it isn't detecting the right cell names in the file. I have used some msgboxes in parts of my if statements to confirm that it is finding the files, but not grabbing the right info.

So issue seems to be with the way the files and folder names are detected and stored by each function. If anyone can see a way to simplify the ProcessFiles Sub or make it more robust, we might have a solution.

Please let me know if you have any ideas. Thanks again!

Option Explicit

Dim wbList() As String
Dim wbCount As Long


Sub ReadDataFromAllWorkbooksInFolder()
    Dim FolderName As String
    Dim cValue As String, bValue As String, aValue As String
    Dim dValue As String, eValue As String, fValue As String
    Dim gValue As String, hValue As String
    Dim i As Long, r As Long


    'FolderName = Sheets("Sheet1").Cells(3, 10).Value
   FolderName = "C:\User"
  
    
    ProcessFiles FolderName, "*.xls"


    If wbCount = 0 Then Exit Sub


    r = 17
    
    For i = 1 To UBound(wbList)
  
        '~~> wbList(i) will give you something like
        '   C:\Receiving Temp\aaa.xls
        '   C:\Receiving Temp\FOLDER1\aaa.xls
        Debug.Print wbList(i)
       ''MsgBox (wbList(i))
        r = r + 1
      If Not (GetInfoFromClosedFile(wbList(i), "po form", "po") = "0000-000") Then
      If Not (GetInfoFromClosedFile(wbList(i), "po form", "po") = "") Then
            
            cValue = GetInfoFromClosedFile(wbList(i), "po form", "po")
            bValue = GetInfoFromClosedFile(wbList(i), "po form", "supplier")
            aValue = GetInfoFromClosedFile(wbList(i), "po form", "description")
            dValue = GetInfoFromClosedFile(wbList(i), "po form", "costcode")
            eValue = GetInfoFromClosedFile(wbList(i), "po form", "date")
            fValue = GetInfoFromClosedFile(wbList(i), "po form", "subtotal")
            gValue = GetInfoFromClosedFile(wbList(i), "po form", "freight")
            hValue = GetInfoFromClosedFile(wbList(i), "po form", "total")
    
            Sheets("Sheet1").Cells(r, 1).Value = cValue
            Sheets("Sheet1").Cells(r, 1).Hyperlinks.Add Sheets("Sheet1").Cells(r, 1), wbList(i)
            Sheets("Sheet1").Cells(r, 2).Value = bValue
            Sheets("Sheet1").Cells(r, 3).Value = aValue
            Sheets("Sheet1").Cells(r, 4).Value = dValue
            Sheets("Sheet1").Cells(r, 5).Value = eValue
            Sheets("Sheet1").Cells(r, 9).Value = fValue
            Sheets("Sheet1").Cells(r, 10).Value = gValue
            Sheets("Sheet1").Cells(r, 11).Value = hValue
  
      Else
      ''MsgBox ("inner IF")
      r = r - 1
      End If
      Else
      ''MsgBox ("outer IF")
      r = r - 1
      End If


     Next i
     
 wbCount = 0
End Sub
Sub ResetAll()


End Sub


'~~> This function was taken from
'~~> http://www.vbaexpress.com/kb/getarticle.php?kb_id=245
Sub ProcessFiles(strFolder As String, strFilePattern As String)
    Dim strFileName As String, strFolders() As String
    Dim i As Long, iFolderCount As Long


    '~~> Collect child folders
    strFileName = Dir$(strFolder & "\", vbDirectory)
    Do Until strFileName = ""
        If (GetAttr(strFolder & "\" & strFileName) And vbDirectory) = vbDirectory Then
            If Left$(strFileName, 1) <> "." Then
                ReDim Preserve strFolders(iFolderCount)
                strFolders(iFolderCount) = strFolder & "\" & strFileName
                iFolderCount = iFolderCount + 1
            End If
        End If
        strFileName = Dir$()
    Loop


    '~~> process files in current folder
    strFileName = Dir$(strFolder & "\" & strFilePattern)
    Do Until strFileName = ""
        wbCount = wbCount + 1
        ReDim Preserve wbList(1 To wbCount)
        wbList(wbCount) = strFolder & "\" & strFileName
        strFileName = Dir$()
    Loop


    '~~> Look through child folders
    For i = 0 To iFolderCount - 1
        ProcessFiles strFolders(i), strFilePattern
    Next i
End Sub


Private Function GetInfoFromClosedFile(ByVal wbFile As String, _
wsName As String, cellRef As String) As String
    Dim arg As String, wbPath As String, wbName As String


    GetInfoFromClosedFile = ""


    wbName = FunctionGetFileName(wbFile)
    wbPath = Replace(wbFile, "\" & wbName, "")


    arg = "'" & wbPath & "\[" & wbName & "]" & _
          wsName & "'!" & cellRef


    On Error Resume Next
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function


'~~> Function to get file name from the full path
'~~> Taken from http://www.ozgrid.com/VBA/GetExcelFileNameFromPath.htm
Function FunctionGetFileName(FullPath As String)
    Dim StrFind As String
    Dim i As Long


    Do Until Left(StrFind, 1) = "\"
        i = i + 1
        StrFind = Right(FullPath, i)
        If i = Len(FullPath) Then Exit Do
    Loop
    FunctionGetFileName = Right(StrFind, Len(StrFind) - 1)
End Function

*note this code has only been modified by me, the bulk of the work was done by other, smarter, individuals.