+ Reply to Thread
Results 1 to 5 of 5

macro code to open most recent file.

  1. #1
    Guest

    macro code to open most recent file.

    I am currently using the following code. I am trying to find some
    additional code that will go to a designated folder and open the most recent
    excel file and run the remaining syntax. So this new code would go to the
    macros folder and find the most recent quote sheet1.xls and open it and run
    the remainder of the macro it would do the same for the remaining quote
    sheet files. If I had to save the quote sheet test1, test2, test3 into
    separate folders I can do that as well.

    Any help would be greatly appreciated.

    Sub aaa()
    Workbooks.Open Filename:="C:\Documents and Settings\Default\My
    Documents\macros\quote sheet test1.xls"

    Workbooks("quote sheet test1.xls").Activate

    'nominate the output file
    Set OutFile = Workbooks("results of running macro.xls").Sheets("sheet2")
    OutPutRow = 1 'nominate the starting row
    Workbooks("quote sheet test1.xls").Activate 'go to the first sample file
    Range("a1:" &
    Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Address).Copy
    Destination:=OutFile.Cells(OutPutRow, 1) 'copy and output the data from the
    first file
    OutPutRow = OutFile.Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Row +
    1 'Range("a65536").End(xlUp).Row + 1 'update the output row based on the
    data in the first file

    Workbooks.Open Filename:="C:\Documents and Settings\Default\My
    Documents\macros\quote sheet test2.xls"

    Workbooks("quote sheet test2.xls").Activate

    Workbooks("quote sheet test2.xls").Activate
    Range("a1:" &
    Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Address).Copy
    Destination:=OutFile.Cells(OutPutRow, 1) 'copy and output the data from the
    second file
    OutPutRow = OutFile.Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Row +
    1 'update the output row based on the data in the second file

    Workbooks.Open Filename:="C:\Documents and Settings\Default\My
    Documents\macros\quote sheet test3.xls"

    Workbooks("quote sheet test3.xls").Activate

    Workbooks("quote sheet test3.xls").Activate
    Range("a1:" &
    Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Address).Copy
    Destination:=OutFile.Cells(OutPutRow, 1) 'copy and output the data from the
    second file

    End Sub


    Regards,


    Judd



  2. #2
    Jim Cone
    Guest

    Re: macro code to open most recent file.

    judd,

    You can't have more than one file with the same name
    in the same folder. So any file in a folder is the most recent.
    The following code opens and closes the files, but you
    have to add the code to massage the files while they are open.

    Regards,
    Jim Cone
    San Francisco, USA

    '---------------------------------
    Sub ChangeDataInFiles()
    'Jim Cone - San Francisco, USA - Jun 09, 2005
    '*Requires a project reference to "Microsoft Scripting Runtime" (scrrun.dll)*
    'Opens/closes files in the specified folder that have a numeric suffix
    'that falls within a specified range.

    Dim objFSO As Scripting.FileSystemObject
    Dim objFolder As Scripting.Folder
    Dim objFile As Scripting.File
    Dim lngCount As Long
    Dim lngNum As Long
    Dim strPath As String
    'Specify the base file name.
    Const strName As String = "quote sheet test"

    'Specify the folder...
    strPath = "C:\Documents and Settings\Default\MyDocuments\macros\"
    'Establish object references
    Set objFSO = New Scripting.FileSystemObject
    Set objFolder = objFSO.GetFolder(strPath)
    'first numeric suffix to look for
    lngCount = 1

    For Each objFile In objFolder.Files
    lngNum = lngCount

    Do
    If objFile.Name = strName & lngNum & ".xls" Then
    Workbooks.Open strPath & objFile.Name

    '*Insert code to do stuff to workbook*

    Workbooks(objFile.Name).Close savechanges:=True 'or False
    lngCount = lngCount + 1
    Exit Do
    Else
    lngNum = lngNum = 1
    End If
    Loop While lngNum < 4 'One more than the number of files

    If lngCount > 3 Then Exit For 'The number of files to open

    Next 'objFile

    Set objFile = Nothing
    Set objFSO = Nothing
    Set objFolder = Nothing
    End Sub
    '----------------------------------


    <[email protected]> wrote in message
    news:[email protected]...
    I am currently using the following code. I am trying to find some
    additional code that will go to a designated folder and open the most recent
    excel file and run the remaining syntax. So this new code would go to the
    macros folder and find the most recent quote sheet1.xls and open it and run
    the remainder of the macro it would do the same for the remaining quote
    sheet files. If I had to save the quote sheet test1, test2, test3 into
    separate folders I can do that as well.

    Any help would be greatly appreciated.

    Sub aaa()
    Workbooks.Open Filename:="C:\Documents and Settings\Default\My
    Documents\macros\quote sheet test1.xls"

    Workbooks("quote sheet test1.xls").Activate
    'nominate the output file
    Set OutFile = Workbooks("results of running macro.xls").Sheets("sheet2")
    OutPutRow = 1 'nominate the starting row
    Workbooks("quote sheet test1.xls").Activate 'go to the first sample file
    Range("a1:" &
    Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Address).Copy
    Destination:=OutFile.Cells(OutPutRow, 1) 'copy and output the data from the
    first file
    OutPutRow = OutFile.Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Row +
    1 'Range("a65536").End(xlUp).Row + 1 'update the output row based on the
    data in the first file
    Workbooks.Open Filename:="C:\Documents and Settings\Default\My
    Documents\macros\quote sheet test2.xls"
    Workbooks("quote sheet test2.xls").Activate
    Workbooks("quote sheet test2.xls").Activate
    Range("a1:" &
    Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Address).Copy
    Destination:=OutFile.Cells(OutPutRow, 1) 'copy and output the data from the
    second file
    OutPutRow = OutFile.Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Row +
    1 'update the output row based on the data in the second file
    Workbooks.Open Filename:="C:\Documents and Settings\Default\My
    Documents\macros\quote sheet test3.xls"
    Workbooks("quote sheet test3.xls").Activate
    Workbooks("quote sheet test3.xls").Activate
    Range("a1:" &
    Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Address).Copy
    Destination:=OutFile.Cells(OutPutRow, 1) 'copy and output the data from the
    second file
    End Sub
    Regards,
    Judd



  3. #3
    Jim Cone
    Guest

    Re: macro code to open most recent file.

    Correction...
    lngNum = lngNum = 1

    should be
    lngNum = lngNum + 1

    (my test code and the posted code differed)

    Jim Cone


    "Jim Cone" <[email protected]> wrote in message news:[email protected]...
    judd,
    You can't have more than one file with the same name
    in the same folder. So any file in a folder is the most recent.
    The following code opens and closes the files, but you
    have to add the code to massage the files while they are open.
    Regards,
    Jim Cone
    San Francisco, USA


    '---------------------------------
    Sub ChangeDataInFiles()
    'Jim Cone - San Francisco, USA - Jun 09, 2005
    '*Requires a project reference to "Microsoft Scripting Runtime" (scrrun.dll)*
    'Opens/closes files in the specified folder that have a numeric suffix
    'that falls within a specified range.

    Dim objFSO As Scripting.FileSystemObject
    Dim objFolder As Scripting.Folder
    Dim objFile As Scripting.File
    Dim lngCount As Long
    Dim lngNum As Long
    Dim strPath As String
    'Specify the base file name.
    Const strName As String = "quote sheet test"

    'Specify the folder...
    strPath = "C:\Documents and Settings\Default\MyDocuments\macros\"
    'Establish object references
    Set objFSO = New Scripting.FileSystemObject
    Set objFolder = objFSO.GetFolder(strPath)
    'first numeric suffix to look for
    lngCount = 1

    For Each objFile In objFolder.Files
    lngNum = lngCount

    Do
    If objFile.Name = strName & lngNum & ".xls" Then
    Workbooks.Open strPath & objFile.Name

    '*Insert code to do stuff to workbook*

    Workbooks(objFile.Name).Close savechanges:=True 'or False
    lngCount = lngCount + 1
    Exit Do
    Else
    lngNum = lngNum = 1'<<<<change =1 to + 1
    End If
    Loop While lngNum < 4 'One more than the number of files

    If lngCount > 3 Then Exit For 'The number of files to open

    Next 'objFile

    Set objFile = Nothing
    Set objFSO = Nothing
    Set objFolder = Nothing
    End Sub
    '----------------------------------


  4. #4
    Guest

    Re: macro code to open most recent file.

    Jim,
    Thank you very much for your patience as I am a new to VBA. I have
    a couple of questions.

    Where should I put this code that you wrote? Prior to each file opening?

    "but you have to add the code to massage the files while they are open." I
    am not quite sure what you mean here. Can you please elaborate.


    Thank You,


    Judd



    "Jim Cone" <[email protected]> wrote in message
    news:[email protected]...
    > judd,
    >
    > You can't have more than one file with the same name
    > in the same folder. So any file in a folder is the most recent.
    > The following code opens and closes the files, but you
    > have to add the code to massage the files while they are open.
    >
    > Regards,
    > Jim Cone
    > San Francisco, USA
    >
    > '---------------------------------
    > Sub ChangeDataInFiles()
    > 'Jim Cone - San Francisco, USA - Jun 09, 2005
    > '*Requires a project reference to "Microsoft Scripting Runtime"
    > (scrrun.dll)*
    > 'Opens/closes files in the specified folder that have a numeric suffix
    > 'that falls within a specified range.
    >
    > Dim objFSO As Scripting.FileSystemObject
    > Dim objFolder As Scripting.Folder
    > Dim objFile As Scripting.File
    > Dim lngCount As Long
    > Dim lngNum As Long
    > Dim strPath As String
    > 'Specify the base file name.
    > Const strName As String = "quote sheet test"
    >
    > 'Specify the folder...
    > strPath = "C:\Documents and Settings\Default\MyDocuments\macros\"
    > 'Establish object references
    > Set objFSO = New Scripting.FileSystemObject
    > Set objFolder = objFSO.GetFolder(strPath)
    > 'first numeric suffix to look for
    > lngCount = 1
    >
    > For Each objFile In objFolder.Files
    > lngNum = lngCount
    >
    > Do
    > If objFile.Name = strName & lngNum & ".xls" Then
    > Workbooks.Open strPath & objFile.Name
    >
    > '*Insert code to do stuff to workbook*
    >
    > Workbooks(objFile.Name).Close savechanges:=True 'or False
    > lngCount = lngCount + 1
    > Exit Do
    > Else
    > lngNum = lngNum = 1
    > End If
    > Loop While lngNum < 4 'One more than the number of files
    >
    > If lngCount > 3 Then Exit For 'The number of files to open
    >
    > Next 'objFile
    >
    > Set objFile = Nothing
    > Set objFSO = Nothing
    > Set objFolder = Nothing
    > End Sub
    > '----------------------------------
    >
    >
    > <[email protected]> wrote in message
    > news:[email protected]...
    > I am currently using the following code. I am trying to find some
    > additional code that will go to a designated folder and open the most
    > recent
    > excel file and run the remaining syntax. So this new code would go to the
    > macros folder and find the most recent quote sheet1.xls and open it and
    > run
    > the remainder of the macro it would do the same for the remaining quote
    > sheet files. If I had to save the quote sheet test1, test2, test3 into
    > separate folders I can do that as well.
    >
    > Any help would be greatly appreciated.
    >
    > Sub aaa()
    > Workbooks.Open Filename:="C:\Documents and Settings\Default\My
    > Documents\macros\quote sheet test1.xls"
    >
    > Workbooks("quote sheet test1.xls").Activate
    > 'nominate the output file
    > Set OutFile = Workbooks("results of running macro.xls").Sheets("sheet2")
    > OutPutRow = 1 'nominate the starting row
    > Workbooks("quote sheet test1.xls").Activate 'go to the first sample file
    > Range("a1:" &
    > Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Address).Copy
    > Destination:=OutFile.Cells(OutPutRow, 1) 'copy and output the data from
    > the
    > first file
    > OutPutRow = OutFile.Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Row
    > +
    > 1 'Range("a65536").End(xlUp).Row + 1 'update the output row based on the
    > data in the first file
    > Workbooks.Open Filename:="C:\Documents and Settings\Default\My
    > Documents\macros\quote sheet test2.xls"
    > Workbooks("quote sheet test2.xls").Activate
    > Workbooks("quote sheet test2.xls").Activate
    > Range("a1:" &
    > Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Address).Copy
    > Destination:=OutFile.Cells(OutPutRow, 1) 'copy and output the data from
    > the
    > second file
    > OutPutRow = OutFile.Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Row
    > +
    > 1 'update the output row based on the data in the second file
    > Workbooks.Open Filename:="C:\Documents and Settings\Default\My
    > Documents\macros\quote sheet test3.xls"
    > Workbooks("quote sheet test3.xls").Activate
    > Workbooks("quote sheet test3.xls").Activate
    > Range("a1:" &
    > Range("a1").Cells.SpecialCells(xlCellTypeLastCell).Address).Copy
    > Destination:=OutFile.Cells(OutPutRow, 1) 'copy and output the data from
    > the
    > second file
    > End Sub
    > Regards,
    > Judd
    >
    >




  5. #5
    Jim Cone
    Guest

    Re: macro code to open most recent file.

    Judd,

    'I have included the code to "massage" the files.
    'It should be complete? Just run it instead of your code.
    'Try it on a copy of your workbooks first!

    'To answer your other question, the code I posted earlier,
    'found, opened the files and closed them. It did not do
    'anything with the data in the files.

    'Please note the project reference required!

    '--------------------------
    Sub ChangeDataInFiles()
    'Jim Cone - San Francisco, USA - Jun 09, 2005
    '*Requires a project reference to "Microsoft Scripting Runtime" (scrrun.dll)*
    'Opens/closes files in the specified folder that have a numeric suffix
    'that falls within a specified range.
    'Copies the used range on the first sheet in each file and
    'pastes it below all other data on a new sheet added to the 'other' file.

    Dim objFSO As Scripting.FileSystemObject
    Dim objFolder As Scripting.Folder
    Dim objFile As Scripting.File
    Dim OutPutRow As Long
    Dim lngCount As Long
    Dim lngNum As Long
    Dim strPath As String
    Dim OutSheet As Excel.Worksheet

    'Specifies the base file name.
    Const strName As String = "quote sheet test"

    'Adds a new sheet to receive the data and names it.
    With Workbooks("results of running macro.xls")
    .Worksheets.Add(Count:=1, after:=.Worksheets(.Worksheets.Count)).Name = _
    "New Output " & Format$(Date, "mmddyy")
    Set OutSheet = .Worksheets(.Worksheets.Count)
    End With
    OutPutRow = 1

    'Specify the folder...
    strPath = "C:\Documents and Settings\Default\MyDocuments\macros\"

    'Establish object references
    Set objFSO = New Scripting.FileSystemObject
    Set objFolder = objFSO.GetFolder(strPath)

    'first numeric suffix to look for
    lngCount = 1

    For Each objFile In objFolder.Files
    lngNum = lngCount

    Do
    'Find the file and open it.
    If objFile.Name = strName & lngNum & ".xls" Then
    Workbooks.Open strPath & objFile.Name

    '*Code to do stuff has been added here*
    'Copies the used range and pastes it on the added sheet,
    'below any existing data.
    Workbooks(objFile.Name).Worksheets(1).UsedRange.Copy _
    Destination:=OutSheet.Cells(OutPutRow, 1)
    OutPutRow = OutSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1

    'Close the file without saving it.
    Workbooks(objFile.Name).Close savechanges:=False
    lngCount = lngCount + 1
    Exit Do
    Else
    lngNum = lngNum + 1
    End If
    Loop While lngNum < 4 'One more than the number of files

    If lngCount > 3 Then Exit For 'The number of files to open

    Next 'objFile

    Set OutSheet = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
    Set objFolder = Nothing
    End Sub
    '------------------------------


    <[email protected]> wrote in message
    news:%23%[email protected]...
    Jim,
    Thank you very much for your patience as I am a new to VBA.
    I have a couple of questions.

    Where should I put this code that you wrote? Prior to each file opening?
    "but you have to add the code to massage the files while they are open." I
    am not quite sure what you mean here. Can you please elaborate.
    Thank You,
    Judd



+ 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