Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Find and repeat row data text from Excel

Status
Not open for further replies.
PhilBreau

PhilBreau

Technical User
Dec 14, 2001
108
CA
Hi all,

I posted something similar in the past. Thank you Andrzejek (Andy) for your help.

This is a little different.

I have a signature file I constantly modify. If my cursor is anywhere on a specific row, I want to find and replace text to the template variables NAME, ASSETTAG, SERIALNUMBER, and HARDWAREDESCRIPTION. However, if I highlight numerous rows, I want to repeat the search create the output as shown below. The trick for me is the repeatability. I can do it for one row, but not multiple rows.

There is a sample of Code I currently use, but I need to be able to repeat for n rows. The rest if the signature is fine. It's just the table rows I need to repeat.

Here is the current code for single row output:


Sub EmailSignature()
Dim strInputData As String
Dim strOutputData As String
Dim i As Integer
Dim path As String


path = (Environ$("USERPROFILE"))


Open path & "\appdata\roaming\microsoft\signatures\Ship.tmp" For Input As #1
strInputData = Input$(LOF(1), 1)
Close #1

i = ActiveCell.Row

' Do While Range("A" & i).Value <> ""
strOutputData = strInputData
strOutputData = Replace(strOutputData, "FIRSTNAME", Range("E" & i).Value)
'strOutputData = Replace(strOutputData, "LASTNAME", Range("B" & i).Value)
strOutputData = Replace(strOutputData, "ADDRESS1", Range("F" & i).Value)
strOutputData = Replace(strOutputData, "ADDRESS2", Range("G" & i).Value)
strOutputData = Replace(strOutputData, "PHONENUMBER", Range("H" & i).Value)
strOutputData = Replace(strOutputData, "REGARDING", Range("I" & i).Value)
strOutputData = Replace(strOutputData, "WORKTRACK", Range("J" & i).Value)
strOutputData = Replace(strOutputData, "TRACKINGNUMBER", Range("Z" & i).Value)
strOutputData = Replace(strOutputData, "REQUESTER", Range("B" & i).Value)
strOutputData = Replace(strOutputData, "ASSETTAG", Range("N" & i).Value)
strOutputData = Replace(strOutputData, "SERIALNUMBER", Range("M" & i).Value)
strOutputData = Replace(strOutputData, "HARDWAREDESCRIPTION", Range("L" & i).Value)



Open path & "\appdata\roaming\microsoft\signatures\Ship.htm" For Output As #1
Print #1, strOutputData
Close #1

' i = i + 1
'Loop

End Sub



Excel Spreadsheet sample:

A B C D
1 Asset tag Serial Number Hardware description Name
2 70000001 ABCDEFG ROUTER George
3 70000002 ZYXWVUT SWITCH George
4 70000003 QWERTY COMPUTER George




Template sample

Signature.tmp

<html>

<head>
<style>
table, th, td {
border: 1px solid black;
}
</style>
</head>
Hi NAME, the hardware is being shipped as requested


<table style="width:100%">
<tr>
<td>Asset tag</td>
<td>Serial Number</td>
<td>Hardware description</td>
</tr>
<tr>
<td>ASSETTAG</td>
<td>SERIALNUMBER</td>
<td>HARDWAREDESCRIPTION</td>
</tr>

</table>
</html>




Output 1 sample:


Signature.htm

<html>

<head>
<style>
table, th, td {
border: 1px solid black;
}
</style>
</head>

Hi George, the hardware is being shipped as requested

<table style="width:100%">
<tr>
<td>Asset tag</td>
<td>Serial Number</td>
<td>Hardware description</td>
</tr>
<tr>
<td>70000001</td>
<td>ABCDEFG</td>
<td>TELEVISION</td>
</tr>

</table>
</html>


Output 1 sample (as viewed in browser)

Hi George, the hardware is being shipped as requested

Asset tag Serial Number Hardware description
70000001 ABCDEFG ROUTER


Output 2 sample

Signature.htm

<html>

<head>
<style>
table, th, td {
border: 1px solid black;
}
</style>
</head>

Hi George, the hardware is being shipped as requested

<table style="width:100%">
<tr>
<td>Asset tag</td>
<td>Serial Number</td>
<td>Hardware description</td>
</tr>
<tr>
<td>70000001</td>
<td>ABCDEFG</td>
<td>ROUTER</td>
</tr>

<tr>
<td>700000002</td>
<td>ZYXWVUT</td>
<td>SWITCH</td>
</tr>


<tr>
<td>700000003</td>
<td>QWERTY</td>
<td>COMPUTER</td>
</tr>

</table>
</html>


Output 2 sample (as viewed in browser or signature)

Hi George,

the hardware is being shipped as requested.

Asset tag Serial Number Hardware description
70000001 ABCDEFG ROUTER
70000002 ZYXWVUT SWITCH
70000003 QWERTY COMPUTER



Thank you

Phil Breau
 
Sort by date Sort by votes
Hi,

Not understanding that that code is for ONE ROW, however the code loops from the row with ActiveCell to the end of data row. Please explain.
 
Upvote 0 Downvote
There are other variables in the source template I'm not mentioning although the code shows it. I'm focusing on the table components.

This is a macroI use for a mail merge document which captures the start and end rows for shipping labels I print. If I can somehow blend the codes and repeat the table rows I may have a winner.

Sub Printship()
Dim WordApp As Object

Dim startRow As String
Dim endRow As String

startRow = ActiveCell.Row
endRow = startRow + Selection.Count - 1

startRow = startRow - 1
endRow = endRow - 1


Set WordApp = CreateObject("Word.Application")

WordApp.Visible = True
WordApp.Run "Printship", startRow, endRow

End Sub


Thank you,
Phil Breau



 
Upvote 0 Downvote

Code: 
Sub ProcessSelection()
    Dim r As Range, c As Range
    
    '[b]your other code here[/b]
    
    '[b]this is the one or more rows selected loop[/b]
    With Intersect(Selection, ActiveSheet.UsedRange)    '[b]this assumes that this table is the only data on sheet[/b]
        For Each r In .Rows
        
            strOutputData = strInputData
            
            For Each c In .Columns
                strOutputData = Replace(strOutputData, Cells(1, c.Column).Value, Cells(r.Row, c.Column).Value)
            Next
            
            Open Path & "\appdata\roaming\microsoft\signatures\Ship.htm" For Output As #1
            Print #1, strOutputData
            Close #1
        
        Next
    End With
End Sub
 
Upvote 0 Downvote
Sorry, this line needs to be modified

Code: 
With Intersect(Selection[highlight #FCE94F].EntireRow[/highlight], ActiveSheet.UsedRange)
 
Upvote 0 Downvote
Thank you for reply, SkipVought

I think I understand the loop, but how do I repeat the block of tags?

Somehow I need Excel to write the HTML tags and repeat for each row.


This is one row of the output:

<tr>
<td>70000001</td>
<td>ABCDEFG</td>
<td>ROUTER</td>
</tr>

The desired result is the output 2 sample of the original post

Thank you,
Phil Breau
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Question
Copy data between SQL and Oracle 1
Replies
8
Views
602
remeng
remeng
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
763
remeng
remeng
linuxguyEastCoast
  • Locked
  • Question
export text from non-public docx, pptx, pdf
Replies
0
Views
335
linuxguyEastCoast
linuxguyEastCoast
Andrzejek
  • Locked
  • Question
Outlook Template replace text 2
Replies
13
Views
2K
Andrzejek
Andrzejek
remeng
  • Locked
  • Question
Run PowerShell Commands from Excel VBA
Replies
14
Views
2K
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top