Open other xls files in folder invisible 
Author Message
 Open other xls files in folder invisible

Dear All,

This code opens all the excel files in the folder of the ActiveWorkbook and
counts the sheets.
The data is stored on sheet1 of the ActiveWorkbook, starting row 11.
Now you can see each file on screen.
It would be better if the active ActiveWorkbook stays on screen
and the other workbooks stays invisible.

Sorry, but i can't find it HOW.

Filip

'Set folderpath
    MyPath = ActiveWorkbook.Path
    ThisWorkbookName = ActiveWorkbook.Name
    If Right$(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    MyName = Dir(MyPath & "*.xls", vbNormal) ' Retrieve the first xls
filename
'Loop folder
    Do While MyName <> ""
        If MyName = ThisWorkbookName Then
        Else
            Workbooks.Open FileName:=MyPath & MyName
            iSheetCount = ActiveWorkbook.Sheets.Count
            ActiveWorkbook.Close False
            ActiveSheet.Cells(iFileCount + 11, 1).Value = MyName
            ActiveSheet.Cells(iFileCount + 11, 2).Value = 1
            ActiveSheet.Cells(iFileCount + 11, 3).Value = iSheetCount
            iFileCount = iFileCount + 1
        End If
    MyName = Dir
    Loop



Fri, 25 Nov 2005 08:02:03 GMT  
 Open other xls files in folder invisible
Filips,

Try putting Application.ScreenUpdating = False in at the start of the code.
At the end reset it, but activate your first workbook just before that if
you don't want to see the last opened.

--
    HTH

    -------

    Bob Phillips
    ... looking out across Poole Harbour to the Purbecks


Quote:
> Dear All,

> This code opens all the excel files in the folder of the ActiveWorkbook
and
> counts the sheets.
> The data is stored on sheet1 of the ActiveWorkbook, starting row 11.
> Now you can see each file on screen.
> It would be better if the active ActiveWorkbook stays on screen
> and the other workbooks stays invisible.

> Sorry, but i can't find it HOW.

> Filip

> 'Set folderpath
>     MyPath = ActiveWorkbook.Path
>     ThisWorkbookName = ActiveWorkbook.Name
>     If Right$(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
>     MyName = Dir(MyPath & "*.xls", vbNormal) ' Retrieve the first xls
> filename
> 'Loop folder
>     Do While MyName <> ""
>         If MyName = ThisWorkbookName Then
>         Else
>             Workbooks.Open FileName:=MyPath & MyName
>             iSheetCount = ActiveWorkbook.Sheets.Count
>             ActiveWorkbook.Close False
>             ActiveSheet.Cells(iFileCount + 11, 1).Value = MyName
>             ActiveSheet.Cells(iFileCount + 11, 2).Value = 1
>             ActiveSheet.Cells(iFileCount + 11, 3).Value = iSheetCount
>             iFileCount = iFileCount + 1
>         End If
>     MyName = Dir
>     Loop



Fri, 25 Nov 2005 08:19:23 GMT  
 Open other xls files in folder invisible
It works perfect,

Tanks,

Filip


Quote:
> Filips,

> Try putting Application.ScreenUpdating = False in at the start of the
code.
> At the end reset it, but activate your first workbook just before that if
> you don't want to see the last opened.

> --
>     HTH

>     -------

>     Bob Phillips
>     ... looking out across Poole Harbour to the Purbecks



> > Dear All,

> > This code opens all the excel files in the folder of the ActiveWorkbook
> and
> > counts the sheets.
> > The data is stored on sheet1 of the ActiveWorkbook, starting row 11.
> > Now you can see each file on screen.
> > It would be better if the active ActiveWorkbook stays on screen
> > and the other workbooks stays invisible.

> > Sorry, but i can't find it HOW.

> > Filip

> > 'Set folderpath
> >     MyPath = ActiveWorkbook.Path
> >     ThisWorkbookName = ActiveWorkbook.Name
> >     If Right$(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
> >     MyName = Dir(MyPath & "*.xls", vbNormal) ' Retrieve the first xls
> > filename
> > 'Loop folder
> >     Do While MyName <> ""
> >         If MyName = ThisWorkbookName Then
> >         Else
> >             Workbooks.Open FileName:=MyPath & MyName
> >             iSheetCount = ActiveWorkbook.Sheets.Count
> >             ActiveWorkbook.Close False
> >             ActiveSheet.Cells(iFileCount + 11, 1).Value = MyName
> >             ActiveSheet.Cells(iFileCount + 11, 2).Value = 1
> >             ActiveSheet.Cells(iFileCount + 11, 3).Value = iSheetCount
> >             iFileCount = iFileCount + 1
> >         End If
> >     MyName = Dir
> >     Loop



Fri, 25 Nov 2005 13:56:13 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. BEING UNABLE TO OPEN XLS FILES SAVED IN A FOLDER

2. when admin open the file,others not able to open

3. Excel will not open 1 file but opens others

4. Stop opening book1.xls/keep task pane open when starting Excel (personal.xls issue)

5. Opening Others Folders In Outlook 98

6. File:Open:Other Users Folder - How to get shortcut of folder you just opened

7. Open XLS attachment says file already open

8. Excel97 : Automically open a Macro by opening a xls file

9. Opening XLS attachments in OUTLOOK 2000 - Says file is already open

10. Book1.xls opens when I open an excel file on my H.D.


 
Powered by phpBB® Forum Software © phpBB Group