Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Aug 2005
    Location
    Madison, Wisconsin, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBS Code - Create Txt file (Excel 2002)

    I have found some code and am trying to make it work for my process. I have one excel workbook with 3 sheets. I need the information on sheet 2 to save as a text file and close.... Is there also a way to insert the sheet name so it will copy the correct data?


    Sub ExportToNotepad()
    Last_Row = ActiveSheet.UsedRange.Rows.Count
    WriteRangeToTextFile Range("B1:B" & Last_Row), "C:tempalstest1.scr", vbTab

    Shell "notepad.exe C:tempalstest1.scr", vbMaximizedFocus

    End Sub


    Sub WriteRangeToTextFile(Source As Range, Path As String, Delimiter As String)
    Dim oFSO As Object
    Dim oFSTS As Object
    Dim lngRow As Long, lngCol As Long


    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFSTS = oFSO.CreateTextFile(Path, True)

    For lngRow = 1 To Source.Rows.Count

    For lngCol = 1 To Source.Columns.Count

    If lngCol = Source.Columns.Count Then
    oFSTS.Write Source.Cells(lngRow, lngCol).Text & vbCrLf
    Else
    oFSTS.Write Source.Cells(lngRow, lngCol).Text & Delimiter
    End If

    Next lngCol

    Next lngRow

    oFSTS.Close

    Set oFSTS = Nothing
    Set oFSO = Nothing

    End Sub

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

    Re: VBS Code - Create Txt file (Excel 2002)

    Try

    WriteRangeToTextFile Sheets(2).Range("B1:B" & Last_Row), "C:tempalstest1.scr", vbTab

  3. #3
    New Lounger
    Join Date
    Aug 2005
    Location
    Madison, Wisconsin, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBS Code - Create Txt file (Excel 2002)

    Thanks Hans but it doesn't seem to work. This is what I get...

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

    Re: VBS Code - Create Txt file (Excel 2002)

    In your first post in this thread you had a procedure WriteRangeToTextFile. It is not visible in the screenshot. Where is it?

  5. #5
    New Lounger
    Join Date
    Aug 2005
    Location
    Madison, Wisconsin, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBS Code - Create Txt file (Excel 2002)

    Sorry I think I misunderstood your response. I think I have it right now...So it must not read the sheet name but the sheet order? How do I get the txt window to close?

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

    Re: VBS Code - Create Txt file (Excel 2002)

    If you want the second sheet, regardless of name, use Sheets(2).
    If you want a sheet named MySheet, regardless of its position in the workbook, use Sheets("MySheet")

    Why use Shell "notepad ..." at all if you want to close the window immediately?

Posting Permissions

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