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?

Open files in different 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" wrote in message
...
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?

Open files in different 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" wrote in message
...
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?

Open files in different 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" wrote in message
...
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" wrote in message
...
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?