Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
opening multiple Excel 2007 files in same window (instance) | Excel Discussion (Misc queries) | |||
How do I stop Excel from opening all files w/macros & running them | Excel Discussion (Misc queries) | |||
Opening Multiple Excel Files | Setting up and Configuration of Excel | |||
Opening multiple Excel files that contain varied selected sheets | Excel Worksheet Functions | |||
Running Virus Scan Slowing down opening files | Excel Discussion (Misc queries) |