Posts by John_w

    Re: Inserting variable number of rows


    Re: Export Outlook email body to excel


    I see that the body text is tab-separated, so the above line which replaces the tabs with "" doesn't help at all.


    See if you can incorporate the following code into your code. It includes test data to prove that the parsing works, but you only need the code from the point indicated by the comment and change one line (also indicated) to parse the actual email body text instead of the test string.


    Re: Macro button that inserts row above, and copies formats from the above row


    Ok, your button is forms toolbar button, not an ActiveX command button, n'est-ce pas?


    If you change the code to use a row number instead of a range, it is a matter of changing the macro recorder's fixed row and range reference strings to strings constructed from the row number, to insert and auto fill the correct rows and cells, like this:

    Re: Search and Extract specific values from columns


    Try this code, which you should put in a standard module:

    PS - I think you've made a mistake in repeating CIBC in the Sheet2 output data, otherwise my results don't exactly match yours.

    Re: Insert new row and keep formula (relative to position of command button)


    Third time lucky maybe?! I thought you were filling down the whole row (or cells A-F in the row), but try the following code which is a direct generic translation of your macro:

    Code
    Private Sub CommandButton1_Click()
        Dim row As Long
        row = Me.CommandButton1.TopLeftCell.Offset(-1, 0).row
        Rows(row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("A" & row - 1).AutoFill Destination:=Range("A" & row - 1 & ":A" & row), Type:=xlFillDefault
        Range("F" & row - 1).AutoFill Destination:=Range("F" & row - 1 & ":F" & row), Type:=xlFillDefault
    End Sub

    Re: Insert new row and keep formula (relative to position of command button)


    Try this instead. It inserts 1 row then selects 2 rows above (the previous code selected 1 row above) and auto fills down 1 row.

    Code
    Private Sub CommandButton1_Click()
        Dim newRow As Range
        Set newRow = Me.CommandButton1.TopLeftCell.Offset(-1, 0)
        With newRow.EntireRow
            .Insert shift:=xlDown
            .Offset(-3).Resize(2).AutoFill Destination:=Range(.Offset(-3), .Offset(-1)), Type:=xlFillDefault
        End With
    End Sub

    If still no good, record a macro of you manually inserting a row (on row 21 to continue the established example) and doing the drag fill, and post the code generated.

    Re: Insert new row and keep formula (relative to position of command button)


    Try changing the Type:=xlFillDefault to Type:=xlFillCopy.


    If that doesn't give the required outcome tell us which columns (cells) contain the 2 formulas, using the example you gave in your first post. For example, cell A20 contains the =SUM formula, and cell F20 contains the other formula.

    Re: Automatically Determining Column Widths in a text file before import


    Quote from Evolvd;637189


    I have attached a sample text file. I want to import the data in the columns after the heading "Summary of Tip Deflections For All Load Cases" and stop at the heading "Loads At Insulator Attachments For All Load Cases". At least these headings do not change from file to file. Within this range the columns are fixed, but from file to file they change as mentioned.

    Your sample file doesn't include the former heading. The first heading above the latter heading is "Summary of Joint Support Reactions For All Load Cases", so the following analysis is based on the column headings and data values between "Summary of Joint Support Reactions For All Load Cases" and "Loads At Insulator Attachments For All Load Cases".


    Am I correct in saying that the column headings and data values are always right-aligned? For example, the heading "Str." (with "No." immediately below) has the first value "YL-88" and all following values exactly right-aligned below it; the heading "Structure" (with "Name" immediately below) has the first value "term with cto 15m_24kn.#89.pol" and all following values exactly right-aligned below. If this is the case for all files it should be fairly easy to write VBA code to read in the file (using Open, Input #), find the positions of all the column headings (using Instr and Mid functions) and extract the values which lie exactly below them in the same column width and position.

    Re: Insert new row and keep formula (relative to position of command button)


    Try this:

    Code
    Private Sub CommandButton1_Click()
        Dim newRow As Range
        Set newRow = Me.CommandButton1.TopLeftCell.Offset(-1, 0)
        With newRow.EntireRow
            .Insert Shift:=xlDown
            .Offset(-2, 0).AutoFill Destination:=Range(.Offset(-2, 0), .Offset(-1, 0)), Type:=xlFillDefault
        End With
    End Sub

    Re: Email hyperlink


    How are the email addresses being put into the worksheet? Are you typing them in? If so, typing in an email address should automatically turn it into a hyperlink (with a mailto: link). Otherwise I don't know a way of setting up a column to automatically create an email link for each row in the column, therefore try the following code which converts each selected cell (select the cells before running the macro) into a mailto hyperlink:

    Code
    Sub Create_MailTo_Links()
        Dim cell As Range
        
        For Each cell In Selection
            If Not IsEmpty(cell.Value) Then
                cell.Parent.Hyperlinks.Add anchor:=cell, Address:="mailto:" & cell.Value, TextToDisplay:=cell.Value
            End If
        Next
        
    End Sub

    Re: Email body from VBA over writing Outlook template


    Quote from teekay;637213


    I cannot seem to find a way to get the text of the body into the white space of the template.

    Code
    .HTMLBody = "blah blah blah"

    It's not clear what you want, but you seem to be asking how to insert text into the email body text, whilst preserving the existing text from the template. As you've found, the above line replaces the whole email body text from the template.


    One way is to type in 'placeholder' text in the email template body when you create the template. For example, include the text "**PLACEHOLDER**" (without the quotes) in the template body, and then use the following code instead of the .HTMLBody line to replace that text: