Open Save & Close All Files in Dir

[Guest]

I am looking to write a macro that will open, save & close each file in a
specific directory when run (code below I used from some previous help I
received). Probably more complicate than I need. I just want to open each
file, wait for it to update, and then save & close and go to the next file
til all 100 or so are done.

Sub UpdateTheData()
Dim basebook As Workbook
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = "\\Lkg0fc976\DISK 1\DianeTest\"
MyPath = "\\Lkg0fc976\DISK 1\DianeTest\"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = True
Set basebook = ThisWorkbook
Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
DoEvents
mybook.Close True
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

Thank you.
Regards,
Diane

 

[Guest]

I actually have code that works great, but now need to put some code in here
that will skip four files in this directory I DO NOT want to open, update &
save. I tried putting in some test code to skip one of the files, but it
doesn't seem to work as I had hoped. Any help would be greatly appreciated.

Diane

Sub AutoUpdateLinks()

Dim FileDir As String
Dim FName As String
FileDir = "\\Lkg0fc976\DISK 1\Groups Correlations\"
FName = Dir(FileDir)
Do Until FName = ""Workbooks.Open FileDir & FName, True, False
DoEvents
ActiveWorkbook.Save
ActiveWorkbook.Close
FName = Dir()Loop

End Sub

 

[Tom Ogilvy]

the string returned by Dir is just the filename, it does not include the
path.


Dim FileDir As String
Dim FName As String
FileDir = "\\Lkg0fc976\DISK 1\Groups Correlations\"
FName = Dir(FileDir)
Do Until FName = ""
If lcase(FName) <> "file1.xls" and _
lcase(FName) <> "file2.xls" and _
lcase(FName) <> "file3.xls" and _
lcase(FName) <> "file4.xls" Then
Workbooks.Open FileDir & FName, True, False
DoEvents
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
FName = Dir() ' <== moved this statement
Loop

 

[Guest]

I have written the following, but when running the macro, it still opens
those file names. I am not sure if it matters at all, but the lkg0fc976\disk
1 is not local to my pc, but rather an external maxtor drive on my mini home
network. perhaps you might have some insight as to what I may be doing
wrong. Thank you

Dim FileDir As String
Dim FName As String
FileDir = "\\Lkg0fc976\DISK 1\Groups Correlations\"
FName = Dir(FileDir)
Do Until FName = ""
If LCase(FName) <> "AERO_DEF_EQPT_.xls" And _
LCase(FName) <> "AUTO_MFG.xls" Then

Workbooks.Open FileDir & FName, True, False
DoEvents
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
FName = Dir()
Loop

 

[Tom Ogilvy]

I guess I should have explained better.
To avoid problems with CASE, I added the lcase function that converts
whatever FName contains to all lowercase. So naturally it will never match
anything that contains mostly uppercase. Assuming Dir works with a network
path (and I believe it does), try

UCase(FName) <> "AERO_DEF_EQPT_.XLS"
UCase(FName) <> "AUTO_MFG.XLS"

it was easier to switch from lcase (lower case) to ucase (upper case) and
capitalize the XLS.

 

[Guest]

Yep - that was it - I should have realized what those statements were doing!
Thank you!

Regards,
Diane