Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open all files in folder (Excel 2000)

    Hello all,

    I'm trying to open all the files in a folder. I need to unhide each (this is the problem) and copy some data. Code below.

    Thanks

    Public Sub BatchReplaceAll()

    Dim PathToUse As String
    Dim myDoc As Workbook
    Dim i As Long

    PathToUse = "Cocuments and SettingsSarnelliDesktopComputer BackupsExchange Feb 11Excel"

    With Application.FileSearch
    .NewSearch
    .LookIn = PathToUse
    .SearchSubFolders = True
    .Filename = "D***.xls"
    .MatchTextExactly = True
    .FileType = msoFileTypeExcelWorkbooks

    If .Execute() Then

    For i = 1 To .FoundFiles.Count


    Set myDoc = Workbooks.Open(.FoundFiles(i))

    Windows(myDoc).Visible = True 'Problem here


    myDoc.Close

    Next i

    End If

    End With

    End Sub

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Open all files in folder (Excel 2000)

    I find it odd that the workbook is not visible by default when you open it. (I'm a Word person, not an Excel person.) Is the problem that the window is not visible or is not "in front" of the other windows? Perhaps if you try something like this:

    myDoc.Windows(1).Visible = True

    or

    myDoc.Activate

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open all files in folder (Excel 2000)

    The files are hidden

    myDoc.Windows(1).Visible = True

    This worked but why

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Open all files in folder (Excel 2000)

    I think the index you are giving to the Windows collection in Windows(myDoc) is not a valid index. myDoc is a Workbook object, not a string, so myDoc without a property identifier after it is going to pass the "default property" (whatever that is, I didn't look it up). Let's assume it's the file name. I think the Windows collection requires the full path. You could experiment and see whether that hunch is correct.

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open all files in folder (Excel 2000)

    The reason you were having problems is that mydoc carries all of the information related to the file you opened. You should use the following code:

    Windows(myDoc.Name).Visible = True


    The name presented in this manner satisfies the requirement of not including the path, which is also true when you use Windows(1). But, you should beware of using the Windows(1) approach as there might be more than one hidden file, or one open, etc.
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •