• XL Range to NotePad (XL97; SR2)

    Author
    Topic
    #389996

    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

    Viewing 2 reply threads
    Author
    Replies
    • #691041

      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)?

      • #691129

        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

        • #691172

          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

    • #691167

      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

      • #691301

        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

    • #691353

      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:

      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
      
      
      • #691501

        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

        • #691503

          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..

          • #691505

            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

            • #691515

              Try instead:

                  AppActivate rtn

              Steve

            • #691521

              Steve,

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

              Thanks,
              John

    Viewing 2 reply threads
    Reply To: XL Range to NotePad (XL97; SR2)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: