Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: using notepad with excel

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    Hi Dennis,
    Pls click the link following.

    http://www.mrexcel.com/board/viewtop...c=6411&forum=2
    Hope this helps + pen pineapple apple pen!

    Masaru Kaji aka Colo - cellmasters.net

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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