How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask George Sibiya Your Own Question
George Sibiya
George Sibiya, Computer Hardware Engineer
Category: Programming
Satisfied Customers: 220
Experience:  PhD in Computer Science
94470076
Type Your Programming Question Here...
George Sibiya is online now
A new question is answered every 9 seconds

How to change this macro script below Workbooks.OpenText

This answer was rated:

How to change this macro script below
Workbooks.OpenText Filename:="Word.doc"so instead of Word.doc it opens the latest doc file by time created in the folder?

Hi,

Welcome to JustAnswer and thank you for utilising our services.

I am reviewing your question and will post back

Regards,

G

Please test this:

=====================================

Dim fsobject As Scripting.FileSystemObject

Dim folder As Scripting.Folder

Dim file As Scripting.File

Dim maxDate As Date

Dim fname As String

Set fsobject = CreateObject("Scripting.FileSystemObject")

Set folder = fsobject.GetFolder("" & ActiveWorkbook.Path)

MaxDate = ""

fname = ""

For Each fil In folder.Files

Debug.Print fil.DateLastModified

If fname = "" Then

MaxDate = fil.DateLastModified

fname = fil.Name

Else If

If fil.DateLastModified > MaxDate Then

MaxDate = fil.DateLastModified

fname = fil.Name

End If

End If

Next fil

Workbooks.OpenText Filename:=fname

Set fsobject = Nothing

Set folder = Nothing

Customer: replied 2 months ago.
all this script goes instead one line I had?
Customer: replied 2 months ago.
where do I type path to folder that im using?

Yes because it loops through all the files and check last modified date to compare.

I'll post one that prompts for a directory

Dim fsobject As Scripting.FileSystemObject

Dim folder As Scripting.Folder

Dim file As Scripting.File

Dim maxDate As Date

Dim fname As String

Dim selectedPath As String

Set fsobject = CreateObject("Scripting.FileSystemObject")

'Retrieve Target Folder Path From User

Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker

.Title = "Select A Target Folder"

.AllowMultiSelect = False

If .Show <> -1 Then GoTo NextCode

selectedPath = .SelectedItems(1) & "\"

End With

'Set folder = fsobject.GetFolder("" & selectedPath)

MaxDate = ""

fname = ""

For Each fil In folder.Files

Debug.Print fil.DateLastModified

If fname = "" Then

MaxDate = fil.DateLastModified

fname = fil.Name

Else If

If fil.DateLastModified > MaxDate Then

MaxDate = fil.DateLastModified

fname = fil.Name

End If

End If

Next fil

Workbooks.OpenText Filename:=fname

Set fsobject = Nothing

Set folder = Nothing

Hi, I am just checking if you have managed to test the above

George Sibiya and other Programming Specialists are ready to help you