Solved

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

Posted on 2010-11-29
5
541 Views
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

        .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
Comment
Question by:Mswetsky
  • 3
5 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Is the workbook placed in the same folder as the other files?
0
 
LVL 1

Author Comment

by:Mswetsky
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
Open-All-Books.zip
0
 
LVL 1

Author Comment

by:Mswetsky
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.
0
 
LVL 50

Accepted Solution

by:
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

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
 
LVL 1

Author Closing Comment

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

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