Way to Many Spead Sheets
Way to Many Spead Sheets
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Way to Many Spead Sheets

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have currently 325 Spread sheets showing shipping data.. I got asked to find one piece of data from a job that they can not remember where or how etc.. Is there a way to batch search all my spreadsheets to find this one piece, (a text value) with out having to open up every spread sheet and doing a find?
    thanks

  2. #2
    New Member
    Join Date
    Feb 2002
    Location
    Dallas, Texas
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try doing this outside of Excel using Find / Files or Folders on the Start Menu.

    Restrict the search to the folder containing the worksheets using the Browse button on the Name & Location tab.

    Enter the text string you're looking for in the "Containing text:" box and search.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Alternatively, if you do decide you want to open all these spreadsheets, I've found the code below useful in the past.

    It goes thorough every Excel file in a directory you specify, does something to it (you have to put that bit in yourself), then closes it and moves it to a "Processed" directory off the path you started from.

    --
    Sub movestuff()

    'Change the drive to whatever drive you're on
    ChDrive "C"


    'Define Path
    MyPath = "C:temp"

    'Change directory to your directory
    ChDir (MyPath)

    ' See if a subdirectoy called "Processed" exists
    MyCheckDir = Dir(MyPath & "Processed", vbDirectory)

    'If it doesn't exist then create it
    If MyCheckDir = "" Then
    MkDir MyPath & "Processed"
    End If

    ' Now go looking for Excel files to process
    ChDir MyPath & ""
    MyFile = Dir("*.xls", vbNormal)

    Do While MyFile <> ""

    Workbooks.Open Filename:=MyFile

    'Do your stuff here...




    'Switch back to the open Excel file and close it, without saving
    Windows(MyFile).Activate
    ActiveWorkbook.Close SaveChanges:=0

    'Copy the file we've processed to the Processed Directory
    FileCopy MyPath & "" & MyFile, MyPath & "Processed" & MyFile
    'Delete original
    Kill MyFile

    ' Call Dir again without arguments to return the next *.XLS file in
    ' the same directory.
    MyFile = Dir


    Loop
    End Sub

    --

    Hope this helps.
    Rgds
    AJ



    Please note, Where ever you see two backslashes together above, only put one in your code.

    [ This Message was edited by: AJ on 2002-03-06 07:48 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com