#copy from excel and paste to notepad using VBA

Question

I am able to print the values from excel to notepad, but the format is bit different,

Dim txtFile As String, rng As Range, cellValue As Variant, r As Integer, c As Integer
    txtFile = slocation & "\" & "Cont_name_" & Filename & ".txt"
    lrow = Range("I" & Rows.Count).End(xlUp).Row
        Range("A2:G" & lrow).Select
        Set rng = Selection
        Open txtFile For Output As #1
        For r = 1 To rng.Rows.Count
            For c = 1 To rng.Columns.Count
                cellValue = rng.Cells(r, c).Value
                If InStr(cellValue, "/") Then
                    cellValue = Format(cellValue, "yyyyMMDD")
                End If
                If c = rng.Columns.Count Then
                    Print #1, cellValue
                Else
                    Print #1, cellValue,
                End If
            Next c
        Next r
                Close #1

Spaces are more than the requirement, please help to achieve the desired output,because the tool is accepting only the desired format

Is there trailing white-space in the cells? Typically to generate fixed width columns with `Print`, you'd do something like `Print #1, Spc(50 - Len(cellValue)), cellValue` where 50 is the column width and after `cellValue` has been `Trim`'d. You can also use a character position if that's easier given you output loop. The documentation is [here](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/printstatement). — Comintern, Feb 15 '19 at 04:49
See if [Fixed Field](http://www.mcgimpsey.com/excel/textfiles.html) is what you are looking for? — Siddharth Rout, Feb 15 '19 at 06:06
Please add a scale below the manual output that you desire, 1xxx5xxxx0xxxx5xxxx0xxxx5xxxx0xxxx5xxxx0xxxx5 ... so that I can tell the desired spacing. I cannot convert jpg to a txt file. — donPablo, Feb 15 '19 at 06:36

Asger · Accepted Answer · 2019-02-15T20:56:30.567

Your first output uses the standard "print zones" in every 14th column (positions 1, 15, 29, ...), which you get by printing with appended comma

.............|.............|.............|.............|.............|.............|
XXX-XX-XXXX 20190111 AA 123 NAME NAME XXXXX

Your desired output starts at the next multiple of 8 characters (1, 9, 17, ...)

.......|.......|.......|.......|.......|.......|.......|.......|.......|
XXX-XX-XXXX.....20190111........AA......123.....NAME....NAME....XXXXX

You can set the next print position in your file by Seek

Private Sub SaveAsText()
    Dim rng As Range
    Dim r As Long, c As Long

    Set rng = ActiveSheet.Range("A1:G1")

    Dim file1 As Integer
    file1 = FreeFile
    Open ThisWorkbook.Path & "\test.txt" For Output As file1

    For r = 1 To rng.Rows.Count
        For c = 1 To rng.Columns.Count
            If c = 1 Then
                Print #file1, CStr(rng.Cells(r, c).Value);
            Else
                Seek #file1, (Seek(file1) \ 8 + 1) * 8 + 1
                Print #file1, CStr(rng.Cells(r, c).Value);
            End If
        Next c
    Next r
    Close #file1

End Sub

Additional Hints:

Use Freefile to get the next free file number (which might be 1).

Use CStr() to prevent the automatically added space characters before and after numeric values.

@koti Did you give my answer a try? If it works fine for you, please consider to mark it as answer as described [here](https://stackoverflow.com/help/accepted-answer). If not, don't hesitate to comment below it. — Asger, Feb 26 '19 at 16:17
yes it works, but still the external tool can't accept the file because of the spacing, still need to look around for perfect solution — koti, Mar 15 '19 at 05:55
I didn’t format the dates, but I hope you did. If you have any working example, can you copy one line as text at the end of your question? — Asger, Mar 15 '19 at 06:19

#copy from excel and paste to notepad using VBA

1 Answers1