Solved

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

Posted on 2010-11-29
5
556 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
ID: 34234229
Is the workbook placed in the same folder as the other files?
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 34234334
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
ID: 34234533
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
ID: 34234574
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
ID: 34234643
Thanks Dave, and I thought I was special. lol
I can definitely make my solution from this in depth solution set.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

895 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