Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    XL Range to NotePad (XL97; SR2)

    I'm trying to PRINT a range in Excel to NotePad using the following code:

    Sub Macro1()
    oData = Range("A1:H6")
    ErrMsg = ErrMsg & oData

    Open "Error.Log" For Output As #1
    Print #1, ErrMsg
    Close #1
    rtn = Shell("notepad.exe Error.Log", vbNormalFocus)
    End Sub

    It seems it would be as simple as copy/paste to NotePad. I intend to to cycle through the code a number of times thus having various ranges appear in the NotePad represented by the "Error.Log"

    Any assistance would be appreciated.

    Thanks,
    John

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

    Re: XL Range to NotePad (XL97; SR2)

    In the first place, I strongly recommend you to select Tools | Options... in the Visual Basic Editor, and check the box labeled Require Variable Declaration. This will add a line Option Explicit at the top of new modules you create.
    I also recommend to insert this line manually into existing modules (always at the top of the module.)

    This option will force you to declare all your variables explicitly, in this form:
    Dim oData As Range
    Dim ErrMsg As String
    Dim rtn As Long

    You will then find that your code contains errors. For example, a range is an object, so you must assign it using Set, in this case Set oData = Range("H1:H6"). The assigment ErrMsg = ErrMsg & oData is invalid because you can't concatenate a string and a multi-cell range.

    Can you explain in more detail how you want the data to end up in the text file? Cells in one range on one line of the text file (if so, separated by what?), or each cells on its own line (if so, proceed across then down, or down then across)?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL Range to NotePad (XL97; SR2)

    Hans,

    Thank you for your suggestion about Option Explicit. I will certainly do that.

    Responding to your question regarding how I want the data to end up in the text file-

    First range = Sheets("Sheet1).Range("A1:H6") Second range = Sheets("Sheet2).Range("A1:H12") and etc..

    Line1 of textfile = Sheets("Sheet1).Range("A1:H1")
    Line2 of textfile = Sheets("Sheet1).Range("A2:H2")
    Line3 of textfile = Sheets("Sheet1).Range("A3:H3")
    Line4 of textfile = Sheets("Sheet1).Range("A4:H4")
    Line5 of textfile = Sheets("Sheet1).Range("A5:H5")
    Line6 of textfile = Sheets("Sheet1).Range("A6:H6")
    Line7 of textfile = Sheets("Sheet2).Range("A1:H1") Note the sheet change
    Line8 of textfile = Sheets("Sheet2).Range("A2:H2")
    and so forth

    **if so, separated by what?** Would be the width of the column the data came from. I'm not interested in separation by comma. Think of this as an error log viewed by the NotePad and the data easily identified in columns.

    Thanks,
    John

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL Range to NotePad (XL97; SR2)

    You could do it like this:

    Sub Macro1()
    Dim odata As Range
    Dim lRows As Long
    Dim lColumns As Long
    Dim Errmsg As String
    Dim lCount As Long
    Dim lCount1 As Long
    Dim rtn As Variant
    Set odata = Range("A1:H6")
    lRows = odata.Rows.Count
    lColumns = odata.Columns.Count
    For lCount = 1 To lRows
    Errmsg = ""
    For lCount1 = 1 To lColumns
    Errmsg = Errmsg & Format(odata(lCount, lCount1), "@@@@@@@@@@@@@@@@@@@@") 'Add @'s if you need wider columns
    Next
    Open "Error.Log" For Append As #1
    Print #1, Errmsg
    Close #1
    Next
    rtn = Shell("notepad.exe Error.Log", vbNormalFocus)
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: XL Range to NotePad (XL97; SR2)

    If you want the text file to "look" like columns, how wide should each column be (since you will have to "pad" them with spaces to get the right length.).

    Do you want to check the length of ALL the cells in ALL the columns and make them all the max length?
    Do you want to check the length of ALL the cells in EACH of the columns and make them each its OWN max length?
    Do you want to truncate any cell that is greater than some defined col width (padding the rest)?
    Do you want to truncate any cell (in a column) that is greater than some defined col width (padding the rest), each column with its own defined max?

    Steve

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL Range to NotePad (XL97; SR2)

    Jan,

    This is exactly what I'm after. One thing though, there are some blank cells in the range which I need to address. The final next step is to change the font in NotePad to Courier so everything falls neatly in columns.

    John

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL Range to NotePad (XL97; SR2)

    John:

    Doing a Save As with format 'Formatted Text (space delimited) (*.prn)' gives more or less what you want.

    Ian.

    (Though something like this is more fun:
    <pre>Sub MakeTextList(rng As Range, strOutFile As String)
    Dim nRows As Integer, nCols As Integer
    Dim dProc
    Dim iFieldWidth() As Integer
    Dim iWidth As Integer
    Dim i As Integer, j As Integer
    Dim strOutput As String

    Open strOutFile For Output As #1
    nRows = rng.Rows.Count
    nCols = rng.Columns.Count

    ReDim iFieldWidth(1 To nCols)
    For j = 1 To nCols
    iFieldWidth(j) = 0
    For i = 1 To nRows
    iWidth = Len(rng(i, j))
    If iFieldWidth(j) < iWidth Then iFieldWidth(j) = iWidth
    Next i
    Next j

    For i = 1 To nRows
    strOutput = ""
    For j = 1 To nCols
    strOutput = strOutput & (strPadded(rng(i, j), iFieldWidth(j) + 1))
    Next j
    'Probably test here for blank lines
    Print #1, strOutput
    Next i
    Close #1

    dProc = Shell("notepad.exe " & strOutFile, vbNormalFocus)
    AppActivate dProc
    End Sub

    Function strPadded(str As String, iLength As Integer) As String
    Dim i As Integer

    strPadded = str
    For i = Len(str) + 1 To iLength
    strPadded = strPadded & " "
    Next i
    End Function

    </pre>


  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL Range to NotePad (XL97; SR2)

    Well I'm to the last step of changing the font in NotePad to Courier. Unfortunately I can not do it with "Application.SendKeys". In tinkering with the code, I found it only to work within XL. Is it possible to do something like: Application.Notepad.SendKeys to change the font?

    Thanks,
    John

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL Range to NotePad (XL97; SR2)

    You can use sendkeys to notepad. Make sure notepad is the foreground window by e.g. using AppActivate:

    AppActivate "Notepad.exe"
    'Sendkeys here

    The string argument after AppActivate must match the titlebar caption of the Notepad session you want to send the keys to..
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL Range to NotePad (XL97; SR2)

    Jan,

    I actually tried "AppActivate "Notepad.exe" prior to posting but received the following error message: Invalid proceedure call or argument

    Sub TestNotePad()
    rtn = Shell("notepad.exe Error.Log", vbNormalFocus)
    AppActivate "Notepad.exe"
    Application.SendKeys ("%f") Test for format
    End Sub

    John

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: XL Range to NotePad (XL97; SR2)

    Try instead:
    <pre> AppActivate rtn</pre>


    Steve

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL Range to NotePad (XL97; SR2)

    Steve,

    That was too easy. I should have seen the writing on the walls.

    Thanks,
    John

Posting Permissions

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