CODEWITHSUNDEEP
htmlexcelvba
Umar Farooq
Umar Farooq

Reputation: 57

Convert excel data to txt file in html format

I am trying to combine details from excel column1 & column2 to get a text file as shown in figure, excel can have upto 100 rows. Here is the picture of excel data which is limited to 3 rows for the time being : enter image description here Here is the txt format on which I want my output: enter image description here I have developed the code but since I am not a regular vba programmer so couldnt be able to hit the target. Here is output I am currently having after suggested edits: enter image description here

Here is my code:

 Dim c As Range, r As Range
    Dim output As String
    Dim txt1 As String
    Dim txt2 As String
    Dim txt3 As String
    Dim txt4 As String
    txt1 = "<div class=" & Chr(34) & "promo_img col-md-4 col-sm-4 col-xs-6" & Chr(34) & ">"
    txt2 = "<!-- PROMOTIONAL IMAGE -->"
    txt3 = "</div>"



    For Each r In Sheet1.Range("A1:C3").Rows
        For Each c In r.Cells
            output = output & "," & c.Value   ' & Chr(10) & Chr(13) & txt2 & Chr(10) & Chr(13) &
        Next c
        output = txt1 & vbNewLine & output & vbNewLine
    Next r
    Open "D:\DOCS\text.txt" For Output As #1
    Print #1, output
    Close
End Sub

Upvotes: 0

Views: 96

Answers (1)

PaichengWu
PaichengWu

Reputation: 2689

Try use code below:

Dim c As Range
Dim output As String
Dim txt1 As String
Dim txt2 As String
Dim txt3 As String
Dim txt4 As String
txt1 = "<div class=" & Chr(34) & "promo_img col-md-4 col-sm-4 col-xs-6" & Chr(34) & ">"
txt2 = "<!-- PROMOTIONAL IMAGE -->"
txt3 = "</div>"

output = ""
For Each c In Sheet1.Range("A1:A3")
    output = output & txt1 & vbnewline & vbnewline & txt2 & vbnewline 
    output = output & "<a href=""" & c.value & """ target=""blank"">"
    output = output & "<img src=""" & c.offset(,1).value & """/></a>"
    output = output & vbnewline & vbnewline & txt3 & vbnewline & vbnewline
Next c

Open "D:\DOCS\text.txt" For Output As #1
Print #1, output
Close #1

vbnewline will change to a newline
"" will return a "

Upvotes: 1

Related Questions