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

Posted on 2010-11-29
Last Modified: 2012-05-10
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


        .SearchSubFolders = False

        .LookIn = szProjectPath

        .FileType = msoFileTypeExcelWorkbooks



'   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



        Next i

'       =============================================================


'       Enable Screen Update Property

        Application.ScreenUpdating = True




'       Make our Master Wokrbook active




'       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




            MsgBox "These workbooks were opened:" & vbNewLine & szWkbNames


        End If




        MsgBox "No workbooks were found in folder *" & _

        Replace(szFolderName, "\", CStr(Empty)) & "*", 64


    End If


    End With


'   Explicitly clear memory

    Set wkb = Nothing

    Exit Sub



    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


    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


        StripFromPath = szFile

    End If

End Function

Open in new window

Question by:Mswetsky
  • 3
LVL 30

Expert Comment

Comment Utility
Is the workbook placed in the same folder as the other files?

Author Comment

Comment Utility
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

Author Comment

Comment Utility
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.
LVL 50

Accepted Solution

Dave Brett earned 500 total points
Comment Utility
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

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



Author Closing Comment

Comment Utility
Thanks Dave, and I thought I was special. lol
I can definitely make my solution from this in depth solution set.

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now