Replace values in Notepad file from CSV/Excel

[GreenBox]

Hi All,

I need your help to develop a Macro which will help me to eradicate a continuous task...

I have output in CSV as below

CSV FILE

[TABLE="width: 608"]
<tbody>[TR]
[TD]Business Name[/TD]
[TD]Address 1[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[TD]Phone[/TD]
[/TR]
[TR]
[TD]Tips & Toes Nail Salon[/TD]
[TD]9430 W 191st[/TD]
[TD]Mokena[/TD]
[TD]Illinois[/TD]
[TD]60448[/TD]
[TD] (708) 478-7420[/TD]
[/TR]
[TR]
[TD]L A Nails[/TD]
[TD]11322 Lincoln[/TD]
[TD]Mokena[/TD]
[TD]Illinois[/TD]
[TD]60423[/TD]
[TD] (815) 464-9445[/TD]
[/TR]
[TR]
[TD]Asia Nails[/TD]
[TD]19610 La Grange[/TD]
[TD]Mokena[/TD]
[TD]Illinois[/TD]
[TD]60448[/TD]
[TD] (708) 478-8590[/TD]
[/TR]
[TR]
[TD]Nails Palace & Spa[/TD]
[TD]19834 La Grange[/TD]
[TD]Mokena[/TD]
[TD]Illinois[/TD]
[TD]60448[/TD]
[TD] (708) 995-5529[/TD]
[/TR]
[TR]
[TD]Kal Nails Spa[/TD]
[TD]10148 W 191st t[/TD]
[TD]Mokena[/TD]
[TD]Illinois[/TD]
[TD]60448[/TD]
[TD] (708) 478-6285[/TD]
[/TR]
[TR]
[TD]Perfect Nails[/TD]
[TD]9500 W Lincoln[/TD]
[TD]Frankfor[/TD]
[TD]Illinois[/TD]
[TD]60423[/TD]
[TD] (815) 806-8789[/TD]
[/TR]
[TR]
[TD]Jen Nails[/TD]
[TD]2063 E Laraway[/TD]
[TD]Lenox[/TD]
[TD]Illinois[/TD]
[TD]60451[/TD]
[TD] (815) 462-1059[/TD]
[/TR]
[TR]
[TD]Classy Nails[/TD]
[TD]15784 S LaGrange[/TD]
[TD]Orland[/TD]
[TD]Illinois[/TD]
[TD]60462[/TD]
[TD] (708) 226-1212[/TD]
[/TR]
[TR]
[TD]Best One Massage[/TD]
[TD]16902 Oak Park[/TD]
[TD]Tinley[/TD]
[TD]Illinois[/TD]
[TD]60477[/TD]
[TD] (708) 532-3103[/TD]
[/TR]
[TR]
[TD]Magnificent Nails[/TD]
[TD]19133 Wolf Rd[/TD]
[TD]Mokena[/TD]
[TD]Illinois[/TD]
[TD]60448[/TD]
[TD] (708) 478-7210[/TD]
[/TR]
</tbody>[/TABLE]

I need the above data to be replaced (TEXT File) in the below BLUE highlighted areas

TEXT FILE

Business Name Address 1 City, State Zip
< website details will be placed>
Business Name
Address 1
City, State Zip
Phone
< website details will be placed>
< website details will be placed>
Business Name
< website details will be placed>
City
< website details will be placed>

The final output should be creation of 10 TEXT (.txt) files as per the record count in CSV file in the above Text Format.

Please reply me the possibilities...

Thanks for all your help.

 

[Momentman]

<website details="" will="" be="" blessed=""><Website details will be placed>, does that mean leaving a line before the next item, 'cos am trying to understand the format of the text file. Maybe you could use record 1 for example</website>

 

[drWatson]

Hello,

Something along those lines?

! Careful: you must reference the "Microsoft Scripting Runtime" library in the VBA editor
! Careful: save your workbook as one accepting macros (*.xlsm)

> this code will output a text file for each CSV line in the folder where the workbook is stored.

*************************************************
Public Sub ParseContacts()
Dim objFSO As FileSystemObject
Dim objNewFile As TextStream
Dim i As Integer
Dim objSheet As Worksheet
i = 2 'don't parse header row
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objSheet = ThisWorkbook.Worksheets(1)
While Trim$(objSheet.Cells(i, 1)) <> ""
Set objNewFile = objFSO.CreateTextFile(ActiveWorkbook.Path & "\" & Trim$(objSheet.Cells(i, 1)) & ".txt", True)
With objNewFile
.WriteLine Trim$(objSheet.Cells(i, 1)) & " " & Trim$(objSheet.Cells(i, 2)) & " " & Trim$(objSheet.Cells(i, 3)) & ", " & Trim$(objSheet.Cells(i, 4)) & " " & Trim$(objSheet.Cells(i, 5))
.WriteBlankLines 1
.WriteLine Trim$(objSheet.Cells(i, 1))
.WriteLine Trim$(objSheet.Cells(i, 2))
.WriteLine Trim$(objSheet.Cells(i, 3)) & ", " & Trim$(objSheet.Cells(i, 4)) & " " & Trim$(objSheet.Cells(i, 5))
.WriteLine Trim$(objSheet.Cells(i, 6))
.WriteBlankLines 2
.WriteLine Trim$(objSheet.Cells(i, 1))
.WriteBlankLines 1
.WriteLine Trim$(objSheet.Cells(i, 3))
.Close
End With
i = i + 1
Wend
End Sub
**********************************

Let me know if this works for you.

Cheers,
drW

 

[GreenBox]

Hi Momentman,

Thank you for your interest in my query.

It is not about leaving a line. there may be more line in between the BLUE highlighted Text.

Regards,
GreenBox

<website details="" will="" be="" blessed=""><website details="" will="" be="" placed="">, does that mean leaving a line before the next item, 'cos am trying to understand the format of the text file. Maybe you could use record 1 for example</website>

</website>

 

[GreenBox]

Hi drWatson

Thank you for your interest in my query.

The output was to replace the data in provided TEXT Template based on the keywords which are highlighted in BLUE which will like

Business Name Address 1 City, State Zip
< website details will be placed>
Business Name
Address 1
City, State Zip
Phone
< website details will be placed>
< website details will be placed>
Business Name
< website details will be placed>
City
< website details will be placed>

Please review and reply

Regards,
GreenBox

Hello,

Something along those lines?

! Careful: you must reference the "Microsoft Scripting Runtime" library in the VBA editor
! Careful: save your workbook as one accepting macros (*.xlsm)

> this code will output a text file for each CSV line in the folder where the workbook is stored.

*************************************************
Public Sub ParseContacts()
Dim objFSO As FileSystemObject
Dim objNewFile As TextStream
Dim i As Integer
Dim objSheet As Worksheet
i = 2 'don't parse header row
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objSheet = ThisWorkbook.Worksheets(1)
While Trim$(objSheet.Cells(i, 1)) <> ""
Set objNewFile = objFSO.CreateTextFile(ActiveWorkbook.Path & "\" & Trim$(objSheet.Cells(i, 1)) & ".txt", True)
With objNewFile
.WriteLine Trim$(objSheet.Cells(i, 1)) & " " & Trim$(objSheet.Cells(i, 2)) & " " & Trim$(objSheet.Cells(i, 3)) & ", " & Trim$(objSheet.Cells(i, 4)) & " " & Trim$(objSheet.Cells(i, 5))
.WriteBlankLines 1
.WriteLine Trim$(objSheet.Cells(i, 1))
.WriteLine Trim$(objSheet.Cells(i, 2))
.WriteLine Trim$(objSheet.Cells(i, 3)) & ", " & Trim$(objSheet.Cells(i, 4)) & " " & Trim$(objSheet.Cells(i, 5))
.WriteLine Trim$(objSheet.Cells(i, 6))
.WriteBlankLines 2
.WriteLine Trim$(objSheet.Cells(i, 1))
.WriteBlankLines 1
.WriteLine Trim$(objSheet.Cells(i, 3))
.Close
End With
i = i + 1
Wend
End Sub
**********************************

Let me know if this works for you.

Cheers,
drW

 

[drWatson]

Hello,

Then simply replace the lines that said .writeblanklines with "< website details will be placed>", as follows:
Option Explicit

*******************************************
Public Sub ParseContacts()
Dim objFSO As FileSystemObject
Dim objNewFile As TextStream
Dim i As Integer
Dim objSheet As Worksheet
i = 2 'don't parse header row
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objSheet = ThisWorkbook.Worksheets(1)
While Trim$(objSheet.Cells(i, 1)) <> ""
Set objNewFile = objFSO.CreateTextFile(ActiveWorkbook.Path & "\" & Trim$(objSheet.Cells(i, 1)) & ".txt", True)
With objNewFile
.WriteLine Trim$(objSheet.Cells(i, 1)) & " " & Trim$(objSheet.Cells(i, 2)) & " " & Trim$(objSheet.Cells(i, 3)) & ", " & Trim$(objSheet.Cells(i, 4)) & " " & Trim$(objSheet.Cells(i, 5))
.WriteLine "< website details will be placed>"
.WriteLine Trim$(objSheet.Cells(i, 1))
.WriteLine Trim$(objSheet.Cells(i, 2))
.WriteLine Trim$(objSheet.Cells(i, 3)) & ", " & Trim$(objSheet.Cells(i, 4)) & " " & Trim$(objSheet.Cells(i, 5))
.WriteLine Trim$(objSheet.Cells(i, 6))
.WriteLine "< website details will be placed>"
.WriteLine "< website details will be placed>"
.WriteLine Trim$(objSheet.Cells(i, 1))
.WriteLine "< website details will be placed>"
.WriteLine Trim$(objSheet.Cells(i, 3))
.WriteLine "< website details will be placed>"
.Close
End With
i = i + 1
Wend

set objfso = nothing
End Sub
**************

Cheers,
drW

 

[GreenBox]

Hi drWatson,

Thanks for the code.

When I replace the "< website details will be placed>" with
<a href="http://website.com"><img class="size-full wp-image-123 alignleft" alt="Reviews" src="http://website.com/Reviews.png" width="200" height="168" /></a>

I am getting "End of Statement" error. How to overcome it.

Regards,
GreenBox

Hello,

Then simply replace the lines that said .writeblanklines with "< website details will be placed>", as follows:
Option Explicit

*******************************************
Public Sub ParseContacts()
Dim objFSO As FileSystemObject
Dim objNewFile As TextStream
Dim i As Integer
Dim objSheet As Worksheet
i = 2 'don't parse header row
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objSheet = ThisWorkbook.Worksheets(1)
While Trim$(objSheet.Cells(i, 1)) <> ""
Set objNewFile = objFSO.CreateTextFile(ActiveWorkbook.Path & "\" & Trim$(objSheet.Cells(i, 1)) & ".txt", True)
With objNewFile
.WriteLine Trim$(objSheet.Cells(i, 1)) & " " & Trim$(objSheet.Cells(i, 2)) & " " & Trim$(objSheet.Cells(i, 3)) & ", " & Trim$(objSheet.Cells(i, 4)) & " " & Trim$(objSheet.Cells(i, 5))
.WriteLine "< website details will be placed>"
.WriteLine Trim$(objSheet.Cells(i, 1))
.WriteLine Trim$(objSheet.Cells(i, 2))
.WriteLine Trim$(objSheet.Cells(i, 3)) & ", " & Trim$(objSheet.Cells(i, 4)) & " " & Trim$(objSheet.Cells(i, 5))
.WriteLine Trim$(objSheet.Cells(i, 6))
.WriteLine "< website details will be placed>"
.WriteLine "< website details will be placed>"
.WriteLine Trim$(objSheet.Cells(i, 1))
.WriteLine "< website details will be placed>"
.WriteLine Trim$(objSheet.Cells(i, 3))
.WriteLine "< website details will be placed>"
.Close
End With
i = i + 1
Wend

set objfso = nothing
End Sub
**************

Cheers,
drW

 

[GreenBox]

Hi drWatson,


Thanks for the code.


When I replace the "< website details will be placed>" with





I am getting "End of Statement" error. How to overcome it.


Regards,
GreenBox

Hi drWatson,

Thanks for the code.

When I replace the "< website details will be placed>" with


I am getting "End of Statement" error. How to overcome it.

Regards,
GreenBox

 

[GreenBox]

Hi drWatson,

The website link is not showing here.

So I sent you private message.

Please check and reply.

Regards,
GreenBox

Hi drWatson,


Thanks for the code.


When I replace the "< website details will be placed>" with





I am getting "End of Statement" error. How to overcome it.


Regards,
GreenBox

 

[GreenBox]

Dear drWatson,

Thanks for the help.

Regards,
GreenBox