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

Posted on 2003-03-18
Medium Priority
Last Modified: 2010-05-01
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
Question by:Jdog
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8161681
Do you want to create this table via ASP/vbscript or in VB proper in a document?

Author Comment

ID: 8161686
VB would be great!!
LVL 16

Expert Comment

ID: 8161728
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!

LVL 16

Expert Comment

ID: 8161763
LVL 11

Accepted Solution

supunr earned 615 total points
ID: 8162900

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

Expert Comment

ID: 8163149
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
LVL 16

Expert Comment

ID: 8166609
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.

LVL 16

Expert Comment

ID: 8166621
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.

Expert Comment

ID: 8167345
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

Author Comment

ID: 8169225
Thanks for the help I really appreciate it!!

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month7 days, 20 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question