Solved: Open text File into a Excel workbook

[Shazam]

Hi everyone



I would like a code to open the last modified text file from my C:\ Drive into a excel workbook. I did a simple macro record but I can't figure it out how to modify it to open the last modified Text File in my C:\ Drive.


[vba] Workbooks.OpenText FileName:= _
"C:\.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True

.LastModified = msoLastModifiedAnyTime
.FileName = ""
If .Execute(msoSortByFileName, msoSortOrderDescending, True) > 0 Then
Workbooks.OpenText .FoundFiles(1), xlWindows

End If
End Wit
[/vba]

I tried to modified the code below it but no avial?

[VBA]
Workbooks.OpenText FileName:= _
"C:\.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True

.LastModified = msoLastModifiedAnyTime
.FileName = ""
If .Execute(msoSortByFileName, msoSortOrderDescending, True) > 0 Then
Workbooks.OpenText .FoundFiles(1), xlWindows

End If
End With
[/VBA]

[CBrine]

Shazam,
Are you using FSO to get the .LastModified attribute? It looks like it, but you've cut off some of the code.

Are you trying to find the last modified .txt file in a specific folder, or the last modified .txt file used by excel? If it's within a specific folder, then you are going in the right direction, if it's the last excel modified .txt file, then you are not. Let me know.

Cal

[CBrine]

Looks like you want to do something like this. You will have to integrate your options into it.

[VBA]
sub FindText()
Dim FSO As Scripting.FileSystemObject, f As Scripting.File, Path As String
Dim LastDate As Date, lastfile As String
Path = "C:\"
Set FSO = CreateObject("Scripting.FileSystemObject")
For Each f In FSO.GetFolder(path).Files
If f.Type = "Text Document" Then
If LastDate < f.DateLastModified Then
LastDate = f.DateLastModified
lastfile = f.Path
End If
End If
Next
Workbooks.OpenText lastfile
End Sub
[/VBA]

HTH
Cal

[Shazam]

Thank You CBrine its perfect.

At first I was getting a error but then I thought I need to select the vba reference "Microsoft Scripting Runtime". Now it works perfectly.


Thank You!

[CBrine]

Shazam,
Another option is using late binding, which is just a fancy way of saying change all your dll declarations to Object, and don't have a reference to the dll. Like this.

[VBA]
Sub FindText()
Dim FSO As Object, f As Object, Path As String
Dim LastDate As Date, lastfile As String

Path = "C:\"
Set FSO = CreateObject("Scripting.FileSystemObject")
For Each f In FSO.GetFolder(path).Files
If f.Type = "Text Document" Then
If LastDate < f.DateLastModified Then
LastDate = f.DateLastModified
lastfile = f.Path
End If
End If
Next
Workbooks.OpenText lastfile
End Sub
[/VBA]

I find the late binding is better when you are using functionality that has been available with the dll for a while.(Experience will tell you this). Actually referencing the object, lets you access to the intellisense drop downs, which can give you a good idea of what options are available to you.

Glad I could help. Don't forget to mark the thread solved.

Cal

[Shazam]

Shazam,
Another option is using late binding, which is just a fancy way of saying change all your dll declarations to Object, and don't have a reference to the dll. Like this.

[vba]
Sub FindText()
Dim FSO As Object, f As Object, Path As String
Dim LastDate As Date, lastfile As String

Path = "C:\"
Set FSO = CreateObject("Scripting.FileSystemObject")
For Each f In FSO.GetFolder(path).Files
If f.Type = "Text Document" Then
If LastDate < f.DateLastModified Then
LastDate = f.DateLastModified
lastfile = f.Path
End If
End If
Next
Workbooks.OpenText lastfile
End Sub
[/vba]

I find the late binding is better when you are using functionality that has been available with the dll for a while.(Experience will tell you this). Actually referencing the object, lets you access to the intellisense drop downs, which can give you a good idea of what options are available to you.

Glad I could help. Don't forget to mark the thread solved.

Cal

Than You again.