Open Save & Close All Files in Dir

Diane Alsing · 01-31-2005, 11:06 AM

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

Diane Alsing · 01-31-2005, 01:06 PM

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 = ""
>> If FName <> "\\Lkg0fc976\DISK 1\Groups Correlations\File1.xls" Then
Workbooks.Open FileDir & FName, True, False
DoEvents
ActiveWorkbook.Save
ActiveWorkbook.Close
FName = Dir()
>> End If
Loop

End Sub

"Diane Alsing" wrote:

> 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

Tom Ogilvy · 01-31-2005, 02:06 PM

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

--
Regards,
Tom Ogilvy

"Diane Alsing" <[email protected]> wrote in message
news:[email protected]...
> 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 = ""
> >> If FName <> "\\Lkg0fc976\DISK 1\Groups Correlations\File1.xls"
Then
> Workbooks.Open FileDir & FName, True, False
> DoEvents
> ActiveWorkbook.Save
> ActiveWorkbook.Close
> FName = Dir()
> >> End If
> Loop
>
> End Sub
>
>
> "Diane Alsing" wrote:
>
> > 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

Diane Alsing · 01-31-2005, 03:06 PM

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" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Diane Alsing" <[email protected]> wrote in message
> news:[email protected]...
> > 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 = ""
> > >> If FName <> "\\Lkg0fc976\DISK 1\Groups Correlations\File1.xls"
> Then
> > Workbooks.Open FileDir & FName, True, False
> > DoEvents
> > ActiveWorkbook.Save
> > ActiveWorkbook.Close
> > FName = Dir()
> > >> End If
> > Loop
> >
> > End Sub
> >
> >
> > "Diane Alsing" wrote:
> >
> > > 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
>
>
>

Tom Ogilvy · 01-31-2005, 04:06 PM

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.

--
Regards,
Tom Ogilvy

"Diane Alsing" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Diane Alsing" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 = ""
> > > >> If FName <> "\\Lkg0fc976\DISK 1\Groups
Correlations\File1.xls"
> > Then
> > > Workbooks.Open FileDir & FName, True, False
> > > DoEvents
> > > ActiveWorkbook.Save
> > > ActiveWorkbook.Close
> > > FName = Dir()
> > > >> End If
> > > Loop
> > >
> > > End Sub
> > >
> > >
> > > "Diane Alsing" wrote:
> > >
> > > > 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
> >
> >
> >

Diane Alsing · 01-31-2005, 04:06 PM

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

Regards,
Diane

"Tom Ogilvy" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
> "Diane Alsing" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> > > 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
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > > "Diane Alsing" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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 = ""
> > > > >> If FName <> "\\Lkg0fc976\DISK 1\Groups
> Correlations\File1.xls"
> > > Then
> > > > Workbooks.Open FileDir & FName, True, False
> > > > DoEvents
> > > > ActiveWorkbook.Save
> > > > ActiveWorkbook.Close
> > > > FName = Dir()
> > > > >> End If
> > > > Loop
> > > >
> > > > End Sub
> > > >
> > > >
> > > > "Diane Alsing" wrote:
> > > >
> > > > > 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
> > >
> > >
> > >
>
>
>