PDA

View Full Version : Act on files in directory



jmenche
03-03-2011, 01:54 PM
Howdy,

I do not have much experience above the file level. I have a bunch of files in one directory (different versions of the same file) and just need to run a "refresh all" in them once a month.

Can someone help me out?

Thanks

:beerchug:

Bob Phillips
03-04-2011, 01:40 AM
REfresh what?

jmenche
03-04-2011, 05:58 AM
i have some data queries and a pivottable. I can figure that out. I just need the right loop to go through the files.

Thanks

Kenneth Hobs
03-04-2011, 06:57 AM
You should test this with just a few files first. Of course you need to change FileFolder and FileSpec values to suit your needs.

Sub DirFiles()
Dim FileName As String, FileSpec As String, FileFolder As String
Dim wb As Workbook

FileFolder = ThisWorkbook.Path & "\"
FileSpec = FileFolder & "*.xl*"

FileName = Dir(FileSpec)
If FileName = "" Then Exit Sub

' Loop until no more matching files are found
Do While FileName <> ""
If IsWorkbookOpen(FileName) = False Then
Set wb = Workbooks.Add(FileFolder & FileName)
DoEvents
wb.Close True
End If
FileName = Dir()
Loop

End Sub


Function IsWorkbookOpen(stName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next ' In Case it isn't Open
Set Wkb = Workbooks(stName)
If Not Wkb Is Nothing Then IsWorkbookOpen = True
'Boolean Function assumed To be False unless Set To True
End Function

jmenche
03-04-2011, 07:57 AM
Thanks! I can work with this.