+ Reply to Thread
Results 1 to 4 of 4

Open files in different folders

  1. #1
    TEB2
    Guest

    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?



  2. #2
    Tom Ogilvy
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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?
    >
    >




  3. #3
    TEB2
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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?
    > >
    > >

    >
    >
    >


  4. #4
    Tom Ogilvy
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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?
    > > >
    > > >

    > >
    > >
    > >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1