opening multiple files using VB and running a mcaro in excel
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 -- Message posted from http://www.ExcelForum.com |
opening multiple files using VB and running a mcaro in excel
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. "den748 " wrote in message ... 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 --- Message posted from http://www.ExcelForum.com/ |
opening multiple files using VB and running a mcaro in excel
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 "den748 " wrote in message ... | 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 | | | --- | Message posted from http://www.ExcelForum.com/ | |
opening multiple files using VB and running a mcaro in excel
Thank you very much I will let you know how it goes, thanks agai
-- Message posted from http://www.ExcelForum.com |
All times are GMT +1. The time now is 11:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com