Act on files in directory

[jmenche]

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

[Bob Phillips]

REfresh what?

[jmenche]

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]

You should test this with just a few files first. Of course you need to change FileFolder and FileSpec values to suit your needs.

[VBA]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
[/VBA]

[jmenche]

Thanks! I can work with this.