Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 657
  • Last Modified:

Excel VBA to open workbooks in a folder doesn't find them

I got this code and it works with the sample workbooks provided but it does not find the workbooks in my folder. I used VBA in Access to output many workbooks to a folder. The resulting workbook type in my folder is Excel 5 Win 95.
Is there a differrent file type that I can substitute for  msoFileTypeExcelWorkbooks
I am running this vba in Excel 2003
Option Explicit

Private Sub Workbook_Open()
'   Folder Name:
    Const szFolderName As String = "\Project Books"
    
    Dim wkb As Workbook
    Dim szWkbNames As String
    Dim szOpenWkbNames As String
    Dim i As Long
    
    
'   Obtain max resources available for Excel
    Dim lMaxSize As Long
    lMaxSize = Application.MemoryTotal
    
    
'   Obtain the initial file size
    Dim lSize As Long
    lSize = FileLen(ThisWorkbook.FullName)
    
    
'   This workbook's path
    Dim szThisPath As String
        szThisPath = ThisWorkbook.Path
        
        
'   Build a path to include the project folder
    Dim szProjectPath As String
        szProjectPath = szThisPath & szFolderName
        
        
'   Grab the name of our Master workbook
'   Used to ensure this workbook becomes active after
'   opening all the other files
    Dim szMasterBook As String
        szMasterBook = ThisWorkbook.Name


'   Find all Excel workbooks in the folder
    With Application.FileSearch
        .NewSearch
        .SearchSubFolders = False
        .LookIn = szProjectPath
        .FileType = msoFileTypeExcelWorkbooks
        .Execute
        

'   if we found some files to open:
    If .FoundFiles.Count > 0 Then
    
    
'       Stop screen flicker of workbooks being opened
        Application.ScreenUpdating = False
        
'       =============================================================
'       Simple loop, opening the workbooks
        For i = 1 To .FoundFiles.Count
        
            If IsWbOpen(.FoundFiles(i)) Then
                szOpenWkbNames = szOpenWkbNames & _
                vbNewLine & StripFromPath(.FoundFiles(i))
                GoTo NextFile
            End If
            
            Set wkb = Workbooks.Open(.FoundFiles(i))

'           Store workbooks name in a variable for later use
            szWkbNames = szWkbNames & vbNewLine & wkb.Name
            
            
'           Check that we have not used up all available resources:
            lSize = lSize + FileLen(ActiveWorkbook.FullName)
            
'           If we have, exit the loop because we cannot open up anymore files
            If lSize >= lMaxSize Then GoTo MaxedOut
            
NextFile:
        Next i
'       =============================================================

ErrExit:
'       Enable Screen Update Property
        Application.ScreenUpdating = True
        
    
        
'       Make our Master Wokrbook active
        Workbooks(szMasterBook).Activate
    
    
'       For this example, just deliver a message
'       stating which books were opened, or which books were
'       already opened + the workbooks opened
        If szOpenWkbNames <> CStr(Empty) Then
            MsgBox "These workbooks were already open:" & _
            vbNewLine & szOpenWkbNames & _
            vbNewLine & vbNewLine & _
            "These workbooks were opened:" & vbNewLine & szWkbNames
            
        Else
        
            MsgBox "These workbooks were opened:" & vbNewLine & szWkbNames
            
        End If
        
    Else
    
        MsgBox "No workbooks were found in folder *" & _
        Replace(szFolderName, "\", CStr(Empty)) & "*", 64
    
    End If
    
    End With
    
'   Explicitly clear memory
    Set wkb = Nothing
    Exit Sub
    
MaxedOut:
    MsgBox "The maximum amount of workbooks have been opened", 64
    GoTo ErrExit
End Sub


Private Function IsWbOpen(wbName As String) As Boolean
'   Check if a workbook is open
    Dim i As Long
    For i = Workbooks.Count To 1 Step -1
        If Workbooks(i).FullName = wbName Then Exit For
    Next
    If i <> 0 Then IsWbOpen = True
End Function


Private Function StripFromPath(FullPath As String) As String
'   Cut the file name out of a full path
    Dim szStrip As String
    Dim szFile As String
    Dim i As Long

    If Len(FullPath) > 0 Then
        szStrip = CStr(Empty)
        i = Len(FullPath)
        Do While szStrip <> "\"
            szStrip = Mid$(FullPath, i, 1)
            If szStrip = "\" Then
                szFile = Right$(FullPath, Len(FullPath) - i)
            End If
            i = i - 1
        Loop
        StripFromPath = szFile
    End If
End Function

Open in new window

0
Mswetsky
Asked:
Mswetsky
  • 3
1 Solution
 
SiddharthRoutCommented:
Is the workbook placed in the same folder as the other files?
0
 
MswetskyAuthor Commented:
I tried it in the same and one level up (which was the way the sample worked)
I've attached  the zip sample for reference
Open-All-Books.zip
0
 
MswetskyAuthor Commented:
What I am trying to do is the following, in case there is a better way.
I have approximately 100 workbooks in a folder.
I want to open an excel file (For example Main.xls) that will open each book in the folder, do some formatting and copy the sheet into the main.xls book. I want to save the main book as (whatever.xls) after all books in the folder have been formatted and copied.
0
 
Dave BrettVice President - Business EvaluationCommented:
This Article provides a method to collate all the sheets from all workbooks in a folder to a single master workbook, either as separate sheets or as one mega-sheet

http://www.experts-exchange.com/A_2804.html

You may find this useful for your problem. It can be easily adapted to only copy a certain sheet position or name rather than all sheets, and for your formatting

Cheers

Dave
0
 
MswetskyAuthor Commented:
Thanks Dave, and I thought I was special. lol
I can definitely make my solution from this in depth solution set.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now