Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop through workbooks
"Rhonda" wrote in message ... I need a macro to loop through a directory (C:\MyDocuments) and identify files with the .bak extension. Then open them one at a time because I need to copy columns to a master. I found this macro, can it be changed to my needs? Not really, that routine loops through the worksheets in an open workbook You probably need to use the dir function to return a list of the files in your directory and then use the workbook.Open method Something like Dim myfilelist(1000) As String, myfilecount As Long Dim myfile as string, xlbook as Workbook myfile = Dir("C:MyDocuments\*.bak", vbNormal) Do While myfile < "" myfilelist(myfilecount) = mydirlist(i) & "\" & myfile myfilecount = myfilecount + 1 myfile = Dir Loop For i = 0 To myfilecount If myfilelist(i) < "" Then Set xlbook = xWorkbooks.Open(myfilelist(i)) ' Do Your Stuff xlbook.Close Set xlbook = Nothing End If Next i Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop through workbooks
"Rhonda" wrote in message ... What should dirlist be? Sorry I cut this from a more complex example which looped through directories too and mydirlist was the folder path, I thought I cut that out but missed that one Here's the fixed version Dim myfilelist(1000) As String, myfilecount As Long Dim myfile as string, xlbook as Workbook myfile = Dir("C:MyDocuments\*.bak", vbNormal) Do While myfile < "" myfilelist(myfilecount) = myfile myfilecount = myfilecount + 1 myfile = Dir Loop For i = 0 To myfilecount If myfilelist(i) < "" Then Set xlbook = xWorkbooks.Open(myfilelist(i)) ' Do Your Stuff xlbook.Close Set xlbook = Nothing End If Next i Keith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop through workbooks
Thanks Keith, appreciate the help!!
-----Original Message----- "Rhonda" wrote in message ... What should dirlist be? Sorry I cut this from a more complex example which looped through directories too and mydirlist was the folder path, I thought I cut that out but missed that one Here's the fixed version Dim myfilelist(1000) As String, myfilecount As Long Dim myfile as string, xlbook as Workbook myfile = Dir("C:MyDocuments\*.bak", vbNormal) Do While myfile < "" myfilelist(myfilecount) = myfile myfilecount = myfilecount + 1 myfile = Dir Loop For i = 0 To myfilecount If myfilelist(i) < "" Then Set xlbook = xWorkbooks.Open(myfilelist (i)) ' Do Your Stuff xlbook.Close Set xlbook = Nothing End If Next i Keith . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
Copy/ move selected data from workbooks to seperate worksheets or workbooks | Excel Worksheet Functions | |||
Loop through folder of workbooks and add rows | Excel Worksheet Functions | |||
loop through workbooks | Excel Programming | |||
Can I have a loop to open a set of workbooks get some data, close it one a time. | Excel Programming |