Run-time error '1004'; Can't open most recent file though its displaying the name of file

[prashant089]

Dear Friends

I am trying to open the most recent text file through excel macro but there is this "Run-time error '1004' " message pops up and says that it can not find the file though it is displaying the name of file. I am slightly new to VBA and I have copied this formula from another online help posting.

Can someone review the codes and let me know how to correct it. I am working on Windows 7 and Excel 2010 version.
Thanks in advance.

Sub GetMostRecentFile()

Dim FileSys As FileSystemObject
Dim objFile As File
Dim myFolder
Dim strFilename As String
Dim dteFile As Date

'set path for files - change for your folder
Const myDir As String = "C:\Users\Admin\Documents\CDR Reports\Jan2012\TXT Files"

'set up filesys objects
Set FileSys = New FileSystemObject
Set myFolder = FileSys.GetFolder(myDir)


'loop through each file and get date last modified. If largest date then store Filename
dteFile = DateSerial(1900, 1, 1)
For Each objFile In myFolder.Files
If objFile.DateLastModified > dteFile Then
dteFile = objFile.DateLastModified
strFilename = objFile.Name
End If
Next objFile

Workbooks.Open strFilename

Set FileSys = Nothing
Set myFolder = Nothing
End Sub


Prashant Paul

 

[Norie]

Are you specifying the path as well as the filename when you try to open the file?

 

[prashant089]

No, I have specified the path only, it finds the latest file and displays the name of file in the error message also but message says it can not find that file
i.e. if the latest file name is "CDR22.01.12.txt" then the error message say:

Run-time error '1004'
'CDR22.01.12.txt' could not be found. Check the spelling of the filename, an varify the the file location is correct.

If you are trying to open the file from the list of most recently used file, make sure that the file has not been renamed, moved or deleted.

 

[Norie]

You aren't specifiying the path here, only the filename.

Workbooks.Open strFilename

One of the thing the message says is verify file location, which probably means check the path.

 

[daverunt]

Norrie was dropping a hint.
You need the path and filename to open the file.

so

Workbooks.Open myDir & "\" &  strFilename

Or change the Const for the path to

Const myDir As String = "C:\Users\Admin\Documents\CDR Reports\Jan2012\TXT Files[COLOR=red]\[/COLOR]"
 
and use
 
Workbooks.Open myDir & strFilename

 

[prashant089]

Thanks a lot Dave& Norie

Its working fine after specifying the path.

 

[prashant089]

Hi Dave

I am facing the new problem now, the text file data is opening in a new workbook with text file name. What I want the data to open in current workbook only from where the macro is run. Can you help me with this.

Thanks

Prashant

 

[daverunt]

Hi,

this involves importing the file so my knowledge is extremely limited here.

I did find this code from C.Pearson you can try.
Paste it below your current macro.

Change:

'Workbooks.Open myDir & strFilename <--comment out or remove
ImportTextFile FName:=myDir & strFilename, Sep:="|" <--Add



Note: It puts the code in the ActivePage starting in the activeCell so you might want to Activate A1 and a sheet in your macro workbook so the import starts where you want it to.


Courtesy
http://www.cpearson.com/excel/ImpText.aspx

Public Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
Application.ScreenUpdating = False
'On Error GoTo EndMacro:
SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row
Open FName For Input Access Read As #1
While Not EOF(1)
    Line Input #1, WholeLine
    If Right(WholeLine, 1) <> Sep Then
        WholeLine = WholeLine & Sep
    End If
    ColNdx = SaveColNdx
    Pos = 1
    NextPos = InStr(Pos, WholeLine, Sep)
    While NextPos >= 1
        TempVal = Mid(WholeLine, Pos, NextPos - Pos)
        Cells(RowNdx, ColNdx).Value = TempVal
        Pos = NextPos + 1
        ColNdx = ColNdx + 1
        NextPos = InStr(Pos, WholeLine, Sep)
    Wend
    RowNdx = RowNdx + 1
Wend
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END ImportTextFile
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Sub

 

[Norie]

Record a macro when you goto Data and import a text file.

 

[prashant089]

Hi Dave

I made the changes you suggested but its giving error. May be I start a new thread for importing the data from latest text file. Thanks for your help.

Regards

Prashant