opening multiple files using VB and running a mcaro in excel

[den748]

Hi, I am very new to VB and I am only using very basic code. But I a
currently working on a meteorological research project and am wonderin
if there is a way to create a list of excel workbooks to open, then ru
a macro, then save, then repeat using the next file on the list. Th
macro Created (the macro currently works) is used to create and modif
graphs (i.e. color, fonts, titles...) it also adds columns the
calculates variables. Please let me know if this is at all possible
thank you

Denni

 

[Township of East Hanover]

I m guessing that you have a workbook with your macro and it is acting on
other workbooks. It can be done

1. Create file list
2. In a loop while not at the end of the list
a. create a variable of type workbook
b. using the above created variable run your macro
c. Destroy the variable and move to next workbook
d. start over.
3. Destroy file list


1.
Function CreateFileList(FileFilter As String, IncludeSubFolder As Boolean)
As Variant
' Returns the full filename for files matching the filter criteria
' in the current folder

Dim FileList() As String, FileCount As Long

CreateFileList = ""
Erase FileList
If FileFilter = "" Then FileFilter = "*.*" ' all files
With Application.FileSearch
.NewSearch
.LookIn = CurDir
.FileName = FileFilter
.SearchSubFolders = IncludeSubFolder
.FileType = msoFileTypeAllFiles
If .Execute(SortBy:=msoSortByFileName,
SortOrder:=msoSortOrderAscending) = 0 Then Exit Function
ReDim FileList(.FoundFiles.Count)
For FileCount = 1 To .FoundFiles.Count
FileList(FileCount) = .FoundFiles(FileCount)
Next FileCount
.FileType = msoFileTypeExcelWorkbooks ' reset filetypes
End With
CreateFileList = FileList
Erase FileList
End Function

FileNamesList = CreateFileList("*.xls", False)

2.
Dim TempWB as Workbook

For I = 1 To UBound(FileNamesList)
Set TempWB = FileNamesList(I)
WorkBooks.Open TempWB
run you macro here
TempWB =
Next I

This is not all of the code I just thought that I would point you in the
right direction. There should be enough here to get you started.

 

[Jim Rech]

An example that may help you:

Sub LoopThruFiles()
Dim FilesArray() As String, FileCounter As Integer
Dim FName As String, LoopCounter As Integer
FName = Dir("c:\my documents\test\*.xls")
Do While FName <> ""
FileCounter = FileCounter + 1
ReDim Preserve FilesArray(1 To FileCounter)
FilesArray(FileCounter) = FName
FName = Dir()
Loop
If FileCounter > 0 Then
Application.ScreenUpdating = False
For LoopCounter = 1 To FileCounter
Workbooks.Open "c:\my documents\test\" &
FilesArray(LoopCounter), False
Range("A1").Value = "abc"
ActiveWorkbook.Save
ActiveWorkbook.Close
Next
End If
End Sub

--
Jim Rech
Excel MVP
| Hi, I am very new to VB and I am only using very basic code. But I am
| currently working on a meteorological research project and am wondering
| if there is a way to create a list of excel workbooks to open, then run
| a macro, then save, then repeat using the next file on the list. The
| macro Created (the macro currently works) is used to create and modify
| graphs (i.e. color, fonts, titles...) it also adds columns then
| calculates variables. Please let me know if this is at all possible,
| thank you
|
| Dennis
|
|
| ---
|
|

 

[den748]

Thank you very much I will let you know how it goes, thanks agai