Open files in different folders

I have 2 folders. Folder 1 contains 2003 data and folder 2 contains 2004
data. Match will be on left 6 characters of files. How do I open both
files, do my analysis and then continue looping through all files in the
folders?

Dim sName as String
dim sPath2004 as String
dim sPath2003 as String
Dim bk1 as Workbook
Dim bk2 as Workbook
sPath2004 = "C:\Files2004\"
sPath2003 = "C:\Files2003\"
dim v() as String
Redim v(1 to 1000)
Dim i as Long
i = 1
sName = dir(sPath2004 & "*.xls")
if sName <> "" then
do
v(i) = sName
sName = dir()
i = i + 1
Loop while sName <> ""
redim v(1 to i -1)
for i = 1 to ubound(v)
sName = Left(v(i),6) & "2003.xls"
set bk1 = workbooks.Open(sPath2004 & v(i))
set bk2 = workbooks.Open(sPath2003 & sName)
. . .
bk1.close Savechanges:=False
bk2.close SaveChanges:=False
Next

--
Regards,
Tom Ogilvy


"TEB2" <TEB2@discussions.microsoft.com> wrote in message
news:4E46BF22-4595-4304-AB24-2922D1CBFFDB@microsoft.com...
> I have 2 folders. Folder 1 contains 2003 data and folder 2 contains 2004
> data. Match will be on left 6 characters of files. How do I open both
> files, do my analysis and then continue looping through all files in the
> folders?
>
>

Where do I put the "End If"?

"Tom Ogilvy" wrote:

> Dim sName as String
> dim sPath2004 as String
> dim sPath2003 as String
> Dim bk1 as Workbook
> Dim bk2 as Workbook
> sPath2004 = "C:\Files2004\"
> sPath2003 = "C:\Files2003\"
> dim v() as String
> Redim v(1 to 1000)
> Dim i as Long
> i = 1
> sName = dir(sPath2004 & "*.xls")
> if sName <> "" then
> do
> v(i) = sName
> sName = dir()
> i = i + 1
> Loop while sName <> ""
> redim v(1 to i -1)
> for i = 1 to ubound(v)
> sName = Left(v(i),6) & "2003.xls"
> set bk1 = workbooks.Open(sPath2004 & v(i))
> set bk2 = workbooks.Open(sPath2003 & sName)
> . . .
> bk1.close Savechanges:=False
> bk2.close SaveChanges:=False
> Next
>
> --
> Regards,
> Tom Ogilvy
>
>
> "TEB2" <TEB2@discussions.microsoft.com> wrote in message
> news:4E46BF22-4595-4304-AB24-2922D1CBFFDB@microsoft.com...
> > I have 2 folders. Folder 1 contains 2003 data and folder 2 contains 2004
> > data. Match will be on left 6 characters of files. How do I open both
> > files, do my analysis and then continue looping through all files in the
> > folders?
> >
> >
>
>
>

maybe just jump out instead.

Sub ARA()
Dim sName As String
Dim sPath2004 As String
Dim sPath2003 As String
Dim bk1 As Workbook
Dim bk2 As Workbook
sPath2004 = "C:\Files2004\"
sPath2003 = "C:\Files2003\"
Dim v() As String
ReDim v(1 To 1000)
Dim i As Long
i = 1
sName = Dir(sPath2004 & "*.xls")
If sName = "" Then Exit Sub
Do
v(i) = sName
sName = Dir()
i = i + 1
Loop While sName <> ""
ReDim v(1 To i - 1)
For i = 1 To UBound(v)
sName = Left(v(i), 6) & "2003.xls"
Set bk1 = Workbooks.Open(sPath2004 & v(i))
Set bk2 = Workbooks.Open(sPath2003 & sName)
' . . .
bk1.Close SaveChanges:=False
bk2.Close SaveChanges:=False
Next
End Sub


--
Regards,
Tom Ogilvy


"TEB2" <TEB2@discussions.microsoft.com> wrote in message
news:A9D4024A-AF3C-4D65-A7DD-93BD3C7E5478@microsoft.com...
> Where do I put the "End If"?
>
> "Tom Ogilvy" wrote:
>
> > Dim sName as String
> > dim sPath2004 as String
> > dim sPath2003 as String
> > Dim bk1 as Workbook
> > Dim bk2 as Workbook
> > sPath2004 = "C:\Files2004\"
> > sPath2003 = "C:\Files2003\"
> > dim v() as String
> > Redim v(1 to 1000)
> > Dim i as Long
> > i = 1
> > sName = dir(sPath2004 & "*.xls")
> > if sName <> "" then
> > do
> > v(i) = sName
> > sName = dir()
> > i = i + 1
> > Loop while sName <> ""
> > redim v(1 to i -1)
> > for i = 1 to ubound(v)
> > sName = Left(v(i),6) & "2003.xls"
> > set bk1 = workbooks.Open(sPath2004 & v(i))
> > set bk2 = workbooks.Open(sPath2003 & sName)
> > . . .
> > bk1.close Savechanges:=False
> > bk2.close SaveChanges:=False
> > Next
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "TEB2" <TEB2@discussions.microsoft.com> wrote in message
> > news:4E46BF22-4595-4304-AB24-2922D1CBFFDB@microsoft.com...
> > > I have 2 folders. Folder 1 contains 2003 data and folder 2 contains
2004
> > > data. Match will be on left 6 characters of files. How do I open
both
> > > files, do my analysis and then continue looping through all files in
the
> > > folders?
> > >
> > >
> >
> >
> >