+ Reply to Thread
Results 1 to 12 of 12

Repost - Shell Wait for Notepad to open file and Save

Hybrid View

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

    Repost - Shell Wait for Notepad to open file and Save

    So I asked this the other night and the Binary function supplied is not the same as opening the file in notepad. I have code that checks the modified date of the file and opens in wordpad.exe Then it saves the file. There are approximately 10 files and 10macros that I'm calling and some of the files take a couple of minutes to open. So all of the files are opening in Notepad at once and my sendkeys command is not always working.
    The file must be opened and saved in notepad to remove some erroneous data that exists.

    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 Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Repost - Shell Wait for Notepad to open file and Save

    Couldn't you deal with the files using VBA's file input/output methods?
    If posting code please use code tags, see here.

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

    Re: Repost - Shell Wait for Notepad to open file and Save

    Norie - I've tried that and it doesn't work. Each macro just needs to complete before the next one begins.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Repost - Shell Wait for Notepad to open file and Save

    What exactly did you try?

    Also, what erroneous data are you trying to get rid of?

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

    Re: Repost - Shell Wait for Notepad to open file and Save

    The files are being downloaded from a server and contain various hard returns and data out of place.
    Here's the code that I have tried that doesn't work.
    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

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Repost - Shell Wait for Notepad to open file and Save

    How does that code not work?

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

    Re: Repost - Shell Wait for Notepad to open file and Save

    The code works but just doesn't modify the files as notepad does. The reason for opening in notepad is there are hard returns and formatting issues with the data ie special formatting characters that are removed when the file is opened and saved in notepad.
    Is there any way to remove these characters that exist with the binary process? For example the non-prining symbols that you would see in word if you hit the formatting tool.

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Repost - Shell Wait for Notepad to open file and Save

    What happens if you use Line Input/Print instead of Get/Put?

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

    Re: Repost - Shell Wait for Notepad to open file and Save

    i get an error on this line
    Input #1, , Data

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Repost - Shell Wait for Notepad to open file and Save

    You wouldn't just substitute Input for Get etc, the code would be different.

    Can you upload a sample file?

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

    Re: Repost - Shell Wait for Notepad to open file and Save

    wish i could but it's proprietary

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

    Re: Repost - Shell Wait for Notepad to open file and Save

    After viewing the file in Notepad it's showing a string of 3 nulls, if there were any way to remove those it would make me very happy.

+ 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. [SOLVED] Wait for Notepad to open file
    By jimrosser in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-30-2014, 12:56 PM
  2. 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
  3. shell to open external bat file
    By zhuleijia in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-07-2013, 12:28 AM
  4. VBA Shell command Wait function
    By jcomfort in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-11-2013, 04:24 PM
  5. Shell command and Notepad
    By SA3214 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-15-2005, 07:06 AM

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