Open Save & Close All Files in Dir

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" <DianeAlsing@discussions.microsoft.com> wrote in message
news:2B2DB908-842F-4EB2-858D-DA1D3DBE7576@microsoft.com...
> 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

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" <DianeAlsing@discussions.microsoft.com> wrote in message
> news:2B2DB908-842F-4EB2-858D-DA1D3DBE7576@microsoft.com...
> > 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
>
>
>

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" <DianeAlsing@discussions.microsoft.com> wrote in message
news:DCF197CA-773E-4ED8-989D-193725E72204@microsoft.com...
> 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" <DianeAlsing@discussions.microsoft.com> wrote in message
> > news:2B2DB908-842F-4EB2-858D-DA1D3DBE7576@microsoft.com...
> > > 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
> >
> >
> >

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" <DianeAlsing@discussions.microsoft.com> wrote in message
> news:DCF197CA-773E-4ED8-989D-193725E72204@microsoft.com...
> > 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" <DianeAlsing@discussions.microsoft.com> wrote in message
> > > news:2B2DB908-842F-4EB2-858D-DA1D3DBE7576@microsoft.com...
> > > > 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
> > >
> > >
> > >
>
>
>