using notepad with excel

[Dennis]

Hey

Im new to using excel and visual basic and I want to create a macro, which will collect information from a sheet in excel paste it to notepad and save it in notepad. I would also like it not to replace the previous time it saved notepad.

Thanks if anyone can help

[Colo]

Hi Dennis,
Pls click the link following.

http://www.mrexcel.com/board/viewtop...c=6411&forum=2

[Dennis]

Hi colo thanks for the help it works but it means I will have to delete the previous document that has been saved. Is there a way to call the notepad document a different name each time the macro is run like 1, 2, 3...or something like that. Thanks for the help

[Colo]

Hi Dennis. May I ask you a question?
The cells where you want to save are always same?
If so, I would like to recommend to use "open statement" to you.

But the cells what you want to save are different for each times,
I would like to recommend to use another way (see following code).

Sub Test()

    Dim arrBuf(), i As Long, c As Integer, shAct As Worksheet

    Dim strFName As String    'aFile Name :Full path

    With Application

        .ScreenUpdating = False

        .DisplayAlerts = False

    'Copy to clipbord

        Set shAct = ActiveSheet

        strFName = Application.GetOpenFilename("Textfile(*.txt),*.txt")

        If strFName = "False" Then Exit Sub

        c = 256

        ReDim arrBuf(1 To c)

        For i = 1 To c    'Loop

    'A reading format is limited to a character sequence.

            arrBuf(i) = Array(i, 2)

        Next i

        delimt = ","    'Or Chr(9) which mean Tab

        On Error GoTo READY_OPENED

        Workbooks.OpenText FileName:=strFName, _

                           StartRow:=1, _

                           DataType:=xlDelimited, _

                           Other:=True, _

                           OtherChar:=delimt, _

                           FieldInfo:=arrBuf

                           

        shAct.Range("A1").CurrentRegion.Copy Range("A65536").End(xlUp).Offset(1)

        With ActiveWorkbook

            .SaveAs FileName:=strFName, FileFormat:=xlText

            .Close False

        End With

        .DisplayAlerts = True

        .ScreenUpdating = True

    End With

    Exit Sub

READY_OPENED:

    MsgBox "The file is already opened."

End Sub

_________________
With regards,
Colo



[ This Message was edited by: Colo on 2002-04-29 18:17 ]

[ This Message was edited by: Colo on 2002-04-29 20:54 ]

[ This Message was edited by: Colo on 2002-04-30 19:42 ]