Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA in Excel (English/Excel/2000)

    I am using the following macro and then have the renamed Excel sheet open in Notepad.

    ChDir "F:My Documents"
    ActiveWorkbook.SaveAs Filename:="F:My Documentsexcel_sample.txt", _
    FileFormat:=xlTextPrinter, CreateBackup:=False
    MsgBox "Do you want to view ASCII file now?", vbYesNo, "View File?"
    If vbYes Then Call StartNotePad
    End Sub

    Sub StartNotePad()
    On Error Resume Next
    Program = "Notepad.exe"
    TaskID = Shell(Program, 1)
    If Err <> 0 Then
    MsgBox "Cannot start " & Program, vbCritical, "Error"
    End If

    End Sub

    The excel spreadsheet gets renamed and save. The message box opens asking if I want to open the message. I do so, and a blank notepad screen opens. What I want it to do is open up the txt file that has been saved. In this case excel_sample.txt.

    Ther must be a line that I can enter that will open notepad and call the correct txt file. I assume that the TAsk ID is calling the the notepad. How do I changes it to call the necessary file.



    I admit that I am extremely new to vba, and a portion of this code came from finding code on the net and playing around with it.

    Thanks for any suggestions. Big fan of Woody's lounge. Read it every day and have found lots of useful information

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA in Excel (English/Excel/2000)

    Welcome to Woody's Lounge! You can use

    <code>Program = "Notepad.exe ""F:My Documentsexcel_sample.txt"""</code>

    Note the double double quotes around the path/filename. If you are using Windows XP (and perhaps Windows 2000), you can omit these double double quotes.

    TaskID is not calling notepad, that is done by Shell. Shell returns a number that is used by Windows internally to refer to the opened application.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA in Excel (English/Excel/2000)

    Thanks for the quick response. I am using Windows XP and I used the double quotes. Works great.

  4. #4
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA in Excel (English/Excel/2000)

    One more question. The macro works fine, but everytime I go out of Excel, the macro disappears. Have tried saving it in current workbook and as Excel help says "store the macro in Personal Macro Workbook in the Excel startup folder" in order to have the macro availablewhenever i use Excel. I know i have done this before and had access to my macros, but after a long week I must be going brain dead!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA in Excel (English/Excel/2000)

    See Legare Coleman's <!post=Personal.xls Tutorial (All),118382>Personal.xls Tutorial (All)<!/post>.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •