Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
open all files in a folder and ...
My macro works fine in reading the content of one file into a summary file.
But i don't want to start the macro for every single file in a specific folder (selected by the user). Is there a way to tell Excel: open first file in folder, run macro, close file, open next one, run macro, close file.... until all files are done? The actual opening routine looks like that: Dim myFileName As Variant Dim myWkbk As Workbook myFileName = Application.GetOpenFilename("Excel files, *.xls") If myFileName = False Then Exit Sub End If Application.ScreenUpdating = False Set myWkbk = Workbooks.Open(Filename:=myFileName) TIA Walt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
open all files in a folder and ...
see code below,
that should do it Sub WorkWithFiles() 'Dim As Long Dim wkbk As Workbook With Application.FileSearch .NewSearch .LookIn = "c:\my folder" .SearchSubFolders = False .FileName = ".xls" ' .FileType = msoFileTypeAllFiles .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then For i = 1 To .FoundFiles.Count Set wkbk = Workbooks.Open(.FoundFiles(i)) ' work with the wkbk reference ' paste macro here wkbk.Close SaveChanges:=False 'change to true to save changes Next i Else MsgBox "There were no files found." End If End With End Sub -----Original Message----- My macro works fine in reading the content of one file into a summary file. But i don't want to start the macro for every single file in a specific folder (selected by the user). Is there a way to tell Excel: open first file in folder, run macro, close file, open next one, run macro, close file.... until all files are done? The actual opening routine looks like that: Dim myFileName As Variant Dim myWkbk As Workbook myFileName = Application.GetOpenFilename("Excel files, *.xls") If myFileName = False Then Exit Sub End If Application.ScreenUpdating = False Set myWkbk = Workbooks.Open(Filename:=myFileName) TIA Walt . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
open all files in a folder and ...
Thank you, it works fine! Only one last thing to do now :o)
This .LookIn folder should be a variable location... Any ideas? Well, i have the whole day for trying ;o) Walt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
open all files in a folder and ...
xl2002? Then look at:
Application.FileDialog(msoFileDialogFolderPicker) in the help. If before, then Jim Rech has a BrowseForFolder routine at: http://www.BMSLtd.co.uk/MVP/Default.htm (look for BrowseForFolder) (You did mean that you wanted the user to select the folder???) walt wrote: Thank you, it works fine! Only one last thing to do now :o) This .LookIn folder should be a variable location... Any ideas? Well, i have the whole day for trying ;o) Walt -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
open all files in a folder and ...
*** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
open all files in a folder and ...
what do you mean rewarded? how? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you set Excel 2007 to open files in a specific folder? | Excel Discussion (Misc queries) | |||
can't open excel files from common folder | Excel Discussion (Misc queries) | |||
Can't Open Certain Excel Files in Folder | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) | |||
create a list of worksheet names (from a single folder, or open files) | Excel Discussion (Misc queries) |