+ Reply to Thread
Results 1 to 11 of 11

Wait for Notepad to open file

Hybrid View

  1. #1
    Registered User
    Join Date
    01-14-2004
    Location
    Va Beach, VA
    Posts
    71

    Wait for Notepad to open file

    Hi, Looking to change up my code it doesn't stop and wait for the file to open so the send keys doesn't work.
    Sub FileLastMod()
    
    Dim oFS As Object
    Dim strFilename As String
    Dim n As Integer
    Dim firstdate As Date
    Dim seconddate As Date
        'Put filename here
    strFilename = "g:\Jim.txt"
        Set oFS = CreateObject("Scripting.FileSystemObject")
        firstdate = Date
        seconddate = oFS.GetFile(strFilename).Datelastmodified
        n = DateDiff("d", firstdate, seconddate)
    'Begin first leg of code here
    On Error Resume Next
    If n = 0 Then
        Shell "notepad.exe g:\Jim.txt", vbNormalFocus
        SendKeys "^s", (wait)
        DoEvents
        SendKeys "%fx"
    If n = 0 Then Exit Sub
    End If
    
    'Begin 2nd leg of code here - checks to see if it's Sunday or Monday
    If Weekday(Date) = 1 Or Weekday(Date) = 2 Then
        Shell "notepad.exe g:\Jim.txt", vbNormalFocus
        SendKeys "^s", (wait)
        DoEvents
        SendKeys "%fx"
    If n = 0 Then Exit Sub
    End If
        
    'Begin 3rd leg of code here - waits for files to be updated
    Do
        strFilename = "g:\Jim.txt"
        Set oFS = CreateObject("Scripting.FileSystemObject")
        firstdate = Date
        seconddate = oFS.GetFile(strFilename).Datelastmodified
        n = DateDiff("d", firstdate, seconddate)
        Loop Until n = 0
    If n = 0 Then
        Shell "notepad.exe g:\Jim.txt", vbNormalFocus
        SendKeys "^s", (wait)
        DoEvents
        SendKeys "%fx"
    If n = 0 Then Exit Sub
    End If
    End Sub

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Wait for Notepad to open file

    Hello jimrosser,

    It is seldom necessary to have select or activate objects in VBA before using them.

    If you can explain in words what you are doing then we can find a solution for you.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    01-14-2004
    Location
    Va Beach, VA
    Posts
    71

    Re: Wait for Notepad to open file

    All the macro is trying to do is primarily open and save the file depending on the date. It will run a total of 10 times. It has no problem with smaller files but one it reaches some of size it begins to open the additional files and skips the sendkeys function.

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Wait for Notepad to open file

    Hello jimrosser,

    All the macro is trying to do is primarily open and save the file depending on the date.
    That's the part that doesn't make sense to me. Are you wanting to change the last modified date of the most recent file?

  5. #5
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Wait for Notepad to open file

    If your attempting to Open/Edit/Close text files then you should look at the commands that involve opening files for input

    example colde here shows "Open .. for Input" method to open and edit text files.

    F1 = FreeFile
    Open SourceFile For Input As F1

    Sub ReplaceTextInFile(SourceFile As String, _
        sText As String, rText As String)
    Dim TargetFile As String, tLine As String, tString As String
    Dim p As Integer, i As Long, F1 As Integer, F2 As Integer
        TargetFile = "RESULT.TMP"
        If Dir(SourceFile) = "" Then Exit Sub
        If Dir(TargetFile) <> "" Then
            On Error Resume Next
            Kill TargetFile
            On Error GoTo 0
            If Dir(TargetFile) <> "" Then
                MsgBox TargetFile & _
                    " already open, close and delete / rename the file and try again.", _
                    vbCritical
                Exit Sub
            End If
        End If
        F1 = FreeFile
        Open SourceFile For Input As F1
        F2 = FreeFile
        Open TargetFile For Output As F2
        i = 1 ' line counter
        Application.StatusBar = "Reading data from " & _
            TargetFile & " ..."
        While Not EOF(F1)
            If i Mod 100 = 0 Then Application.StatusBar = _
                "Reading line #" & i & " in " & _
                TargetFile & " ..."
            Line Input #F1, tLine
            If sText <> "" Then
                ReplaceTextInString tLine, sText, rText
            End If
            Print #F2, tLine
            i = i + 1
        Wend
        Application.StatusBar = "Closing files ..."
        Close F1
        Close F2
        Kill SourceFile ' delete original file
        Name TargetFile As SourceFile ' rename temporary file
        Application.StatusBar = False
    End Sub
    
    Private Sub ReplaceTextInString(SourceString As String, _
        SearchString As String, ReplaceString As String)
    Dim p As Integer, NewString As String
        Do
            p = InStr(p + 1, UCase(SourceString), UCase(SearchString))
            If p > 0 Then ' replace SearchString with ReplaceString
                NewString = ""
                If p > 1 Then NewString = Mid(SourceString, 1, p - 1)
                NewString = NewString + ReplaceString
                NewString = NewString + Mid(SourceString, _
                    p + Len(SearchString), Len(SourceString))
                p = p + Len(ReplaceString) - 1
                SourceString = NewString
            End If
            If p >= Len(NewString) Then p = 0
        Loop Until p = 0
    End Sub
    
    Sub TestReplaceTextInFile()
        ReplaceTextInFile ThisWorkbook.Path & _
            "\ReplaceInTextFile.txt", "|", ";"
        ' replaces all pipe-characters (|) with semicolons (;) 
    End Sub

  6. #6
    Registered User
    Join Date
    01-14-2004
    Location
    Va Beach, VA
    Posts
    71

    Re: Wait for Notepad to open file

    The files are being sent down from a server and some of the data is garbled. So the only way to fix the files is to manually open and save the files. However I'd like to be able to do this without having to manually sit and wait each morning.

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Wait for Notepad to open file

    Hello jimrosser,

    This will open and the close the file without using Notepad or having to wait.
    Sub ReadWriteFile()
    
        Dim Data() As Byte
        Dim Filespec As String
        
        Filespec = "G:\Jim.txt"
        
        Open Filespec For Binary Access Read As #1
            ReDim Data(LOF(1))
            Get #1, , Data
                Open Filespec For Binary Access Write As #2
                    Put #2, , Data
                Close #2
        Close #1
             
    End Sub

  8. #8
    Registered User
    Join Date
    01-14-2004
    Location
    Va Beach, VA
    Posts
    71

    Re: Wait for Notepad to open file

    Thanks Leith! It's like a rocketship compared to what I was attempting to run.

  9. #9
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Wait for Notepad to open file

    Hello jimrosser,

    You're welcome.

  10. #10
    Registered User
    Join Date
    01-14-2004
    Location
    Va Beach, VA
    Posts
    71

    Re: Wait for Notepad to open file

    Still having issues as the way the files are written they must be opened in notepad to remove the hard returns and other garbage that's being passed down.

  11. #11
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Wait for Notepad to open file

    Hello jimrosser,

    To solve this problem, I need a copy of the raw data for testing. Without a before and after files for comparison, I can do nothing for you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. if file is open - try again, wait, try again
    By papasmurfuo9 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-06-2014, 10:39 AM
  2. Open with Notepad Issue after saving CSV
    By aaron.mendes in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-06-2014, 09:45 AM
  3. Wait for user to open new file
    By bishope in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-24-2013, 03:47 PM
  4. [SOLVED] How to know if a file (notepad) is open?
    By keymuu in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-03-2012, 02:36 AM
  5. Wait if application open
    By astrikor in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-16-2010, 01:27 PM

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