• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 155
  • Last Modified:

I dont know, So here is all I've got

I have an Excel document that contains several fields, like this:

Brand Name Here | 1234567 | Computer Memory URL: /searchsku.asp?find=1234567 | 204 | 53 | $19.99 | $13.99

I need to take this information and write it to a document like this:

    <td>Brand Name Here</td>
    <td><a href=/search.asp?find=1234567>Computer Desktop Memory</a></td>
    <td align=center>204-53</td>
    <td align=right>$19.99</td>
    <td align=right>$13.99</td>

Now in my opinion this is extremely difficult...so I am putting all of the points I have on this question...please keep in mind I have NO KNOWLEDGE of VB, VBA, VBSCRIPT or any of that stuff, I was just told that this could be done so I was wondering if anybody out there could help me...thanks in advance
1 Solution
Do you want to create this table via ASP/vbscript or in VB proper in a document?
JdogAuthor Commented:
VB would be great!!
Richie_SimonettiIT OperationsCommented:
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Richie_SimonettiIT OperationsCommented:

Private Sub CreateHTML()
     Dim i as long
     Dim CurLine as string
     Dim StrTmp as string
     Open HTMLFile For Output as #1
     ' Write html header and other stuff
     ' Write table header
     for i = 1 to 100
          CurLine = Range("A" & i).Value
          Print #1, "<tr>"
          Print #1, "   <td>" & split(CurLine, "|")(0) & "</td>"
          Print #1, "   <td>" & split(CurLine, "|")(1) & "</td>"
          StrTmp = split(CurLine, "|")(2)
          Print #1, "   <td><a href=" & Mid(StrTmp, InStr(StrTmp, ":")+1) & ">" & Left(StrTmp, InStr(StrTmp, ":")) & "</a></td>"
          Print #1, "   <td align=center>" & split(CurLine, "|")(3) & "-" & split(CurLine, "|")(4) & "</td>"
          Print #1, "   <td align=right>" & split(CurLine, "|")(5) & "</td>"
          Print #1, "   <td align=right>" & split(CurLine, "|")(6) & "</td>"
          Print #1, "</tr>"
     Next i

     ' Write table end
     ' Write html end tages
     Close #1
End Sub
I think Richie Simonetti's code is excellent.

To respond to your particuliar needs about the URL field, you just have to add an If statement inside the For Next:
For ic = 1 To iCols
'Add this IF statement
          If InStr(UCase(rng.Cells(ir, ic).Value), "URL:") > 0 Then
            Dim a As String
            Dim b As Long
            a = rng.Cells(ir, ic).Value
            b = InStr(UCase(rng.Cells(ir, ic).Value), "URL:")
            Print #ff, "<td><a href=" & Chr(34) & Trim(Mid(a, b + Len("URL:"))) & Chr(34) & ">" & Mid(a, 1, b - 1) & "</a></td>"
            ElseIf IsNumeric(rng.Cells(ir, ic).Value) Then
Richie_SimonettiIT OperationsCommented:
DocM, thanks for your kindly thoughts.

The code posted at that site is merely for information purposes and to show a possible way to do the job.
It could be modified to add graphics, links and everything what you need.

Richie_SimonettiIT OperationsCommented:
Also, there is a tool for Office97(i think and provided by Micro$oft, free!!, do you believe it?) that converts excel to HTML automatically.
I did never use it so i cannot sponsor it.
Dim strDataLine as string, strFileOutput as string
Dim aryData() as string
Dim iCol as integer
Dim lFile as long

'Put the line of data into a string
strdataline = "Brand Name Here | 1234567 | Computer Memory URL: /searchsku.asp?find=1234567 | 204 | 53 | $19.99 | $13.99"

'split that string into an array
arydata = split(strdataline, "|")

'Clear the File Output variable and set its first value
strFileOutput = "<tr>" & vbcrlf

'Loop through the columns in the array
'adding the columns to the file output string
For iCol = lbound(arydata) to ubound(arydata)
 strFileOutput = strFileOutput & vbtab & "<td>" & arydata(iCol) & "</td>" & vbcrlf
next iCol
'Add on the closing for the table row
strFileOutput = strFileOutput & "</tr>"

'get a free file number
lfile = freefile()
'open the file - clearing its contents if it already exists
open "Output.html" for output as lfile
'print the file output
print #lfile, strFileOutput
'close the file
close #lfile
JdogAuthor Commented:
Thanks for the help I really appreciate it!!

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now