Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

Open latest file

Hi,

I have a file path

G:\Asset Services MI\BSS PACK\ASSET SERVICES\2011\SEPTEMBER\CORPORATE ACTIONS

The spreadsheet i need to open is called Month End September11.xls

I want to open this file using a macrro button, so need code to open the latest file.

The only part of the path that changes is the month (SEPTEMBER), Next month will be OCTOBER

An the Spreadsheet will be called
Month End October11.xls

Has anyone got some code that could open this?

Thanks
Seamus
0
Seamus2626
Asked:
Seamus2626
  • 4
  • 3
1 Solution
 
StephenJRCommented:
Seamus - will this be the same as opening the most recent file in terms of date created/modified?
0
 
Seamus2626Author Commented:
Yes the excel file would be the most recent in terms of date created/modified

The code would need to know to look for the new month in the file path though

Thanks
Seamus
0
 
StephenJRCommented:
Seamus - try this:
Sub x()
   
Dim sPath As String, sNew As String
Dim oFSO As Object, oFile As Object, oFldr As Object
Dim dt As Date

Set oFSO = CreateObject("Scripting.FileSystemObject")
sPath = "G:\Asset Services MI\BSS PACK\ASSET SERVICES\2011\SEPTEMBER\CORPORATE ACTIONS"
Set oFldr = oFSO.GetFolder(sPath)

For Each oFile In oFldr.Files
    If oFile.DateCreated > dt Then
        sNew = oFile.Path
        dt = oFile.DateCreated
    End If
Next oFile

Workbooks.Open sNew
   
End Sub

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Seamus2626Author Commented:
Hi Stephen, thats perfect wrt to opening the file in the September file, what is key for me however is to have code that recognises that we are now in October, so search the file path

G:\Asset Services MI\BSS PACK\ASSET SERVICES\2011\OCTOBER\CORPORATE ACTIONS

Otherwise i will be constantly opening SEPTEMBERS file

Thanks
Seamus
0
 
StephenJRCommented:
Sorry Seamus, missed that somehow. So will you be looking for the most recent file in the most recent folder or is it just finding the most recent folder and there will only be one file in it?
0
 
StephenJRCommented:
Does this work?

sPath = "G:\Asset Services MI\BSS PACK\ASSET SERVICES\2011\" & Format(Date, "mmmm") & "\CORPORATE ACTIONS\"
0
 
Seamus2626Author Commented:
Smashing! Thanks Stephen!

Cheers,
Seamus
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now