Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro for all files in the directory
Hi,
I am trying to figure out how to run a macro for all the files in the directory without specifying each individual file. I want the macro to open 1st file, run macro, close the first file; then open the second file and do the same thing for all other files. Please help with the code... Thank you Igor |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro for all files in the directory
Sub WorkWithFiles()
Dim as long Dim wkbk as Workbook With Application.FileSearch .NewSearch .LookIn = "C:\MyFolder" .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 ' macro1 wkbk.Close SaveChanges:=False Else MsgBox "There were no files found." End If End With End Sub Regards, Tom Ogilvy "igor" wrote in message ... Hi, I am trying to figure out how to run a macro for all the files in the directory without specifying each individual file. I want the macro to open 1st file, run macro, close the first file; then open the second file and do the same thing for all other files. Please help with the code... Thank you Igor |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro for all files in the directory
thank you veeeeeeeeery much
-----Original Message----- Sub WorkWithFiles() Dim as long Dim wkbk as Workbook With Application.FileSearch .NewSearch .LookIn = "C:\MyFolder" .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 ' macro1 wkbk.Close SaveChanges:=False Else MsgBox "There were no files found." End If End With End Sub Regards, Tom Ogilvy "igor" wrote in message ... Hi, I am trying to figure out how to run a macro for all the files in the directory without specifying each individual file. I want the macro to open 1st file, run macro, close the first file; then open the second file and do the same thing for all other files. Please help with the code... Thank you Igor . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro for all files in the directory
Option Explicit
Public Sub ReadExcelFiles(FolderName As String) Dim FileName As String ' Add trailing \ character if necessary ' If Right(FolderName, 1) < "\" Then FolderName = FolderName & "\" FileName = Dir(FolderName & "*.xls") Do While FileName < "" Workbooks.Open (FolderName & FileName) ' Do whatever workbook manipulation here Workbooks(FileName).Close FileName = Dir() Loop End Sub Public Sub test() ReadExcelFiles ("c:\temp\test") End Sub http://www.billlunney.com/Excel/FAQ/...ExcelFAQID=204 -- Regards, Bill Lunney www.billlunney.com "igor" wrote in message ... Hi, I am trying to figure out how to run a macro for all the files in the directory without specifying each individual file. I want the macro to open 1st file, run macro, close the first file; then open the second file and do the same thing for all other files. Please help with the code... Thank you Igor |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
open all files in directory | Excel Discussion (Misc queries) | |||
Macro - Open all word files in a directory | Excel Worksheet Functions | |||
List of Files in A Directory | Excel Discussion (Misc queries) | |||
Files in a directory? | Excel Discussion (Misc queries) | |||
Load all files in a directory | Excel Discussion (Misc queries) |