Need some help with a somewhat complex replacement script

[mikekolba]

Been banging my head against the wall for like 4 hours now and keep getting syntax errors.... grrr.

basically this is what i need to do


I have a column of about 60k rows with this web url (with product ids and URl's different for each row). Trying to write a vba macro to do a replace.

http://prf.icecat.biz/index.cgi?product_id=10;mi=start;smi=product;

For the above URL, I need to parse the prodcut ID number out of the URL for each row of data in the column, and then replace the data with all of this HTML Text & below, substituting the product ID that was parsed from the original data in the cell into the new URL that is replacing the data. I need to keep all of the html code and markup in the replaced data.



<div class="qsc-html-content">
<p>******** src="http://prf.icecat.biz/index.cgi?product_id=4610908;mi=start;smi=product;shopname=stsllc01;lang=en" width="640" height="480">*********></p>
</div>



Tried doing this as an excel replace as string function but I keep getting hung up on the replaced portion of data and the syntax because the html code text I think is complicating it.

Help please

 

[mikekolba]

GRRR- the board timed out and the data i posted got formatted html because there is actual html tags in the data that needs to be used in the replacement....

here's an image of the data that needs to be in the replacement...



Uploaded with ImageShack.us

 

[mikekolba]

Any thoughts on this all you excel pro's out there? I'm still banging away at it myself.

This is what I have so far but not sure it's going to work yet.... getting type mismatches on the replace function right now... The double quoting on the html tags makes this messy to do...

Sub Convert_ICECat_URL_to_GoDaddy_HTML_URL()
Dim cell As Range
Dim celldata As String
Dim Data1 As String
Dim Data2 As String
Dim Data3 As String
Dim Data4 As String
Dim Data5 As String
Let Data1 = """<""div class"""
Let Data2 = """=""qsc-html-content"">"""
Let Data3 = """<""p"">""""<""iframe src""=""""http://prf.icecat.biz/index.cgi?product_id=4610908;mi=start;smi=product;shopname=stsllc01;lang=en"""
Let Data4 = """"" ""width """""" = """""" ""640"" """
Let Data5 = """"" ""height"""""" = """""" ""480"" "">""""<""""/""iframe"">""""<""""/""p"">""""<""""/""div"">"""""
'MsgBox Data1
'MsgBox Data2
'MsgBox Data3
'MsgBox Data4
'MsgBox Data5

' Parse the ICECat Product URL for Product ID, and Replace the ICECat Product URL with the Product URL with
' HTML formatting and the parsed Product ID
For Each cell In Selection

If cell.Value <> Empty Then
cell.Value = Replace(cell.Value, 1, -1, Data1 & Data2 & Data3 & Data4 & Data5)
End If

Next cell
End Sub

 

[mikekolba]

ok. got it to run at this point but the problem is it inserts extra "'s in the text that's replaced which messes up the html tags...

The ones in red need to be removed

this is the output...

" < "div class""="qsc-html-content">""<"p">""<"iframe src"=""http://prf.icecat.biz/index.cgi?pro...i=start;smi=product;shopname=stsllc01;lang=en""" "width """ = """ "640" """ "height""" = """ "480" ">""<""/"iframe">""<""/"p">""<""/"div">""

Any ideas? When i remove some of the quotes i then start getting vba errors?

 

[mikekolba]

Ugh- Just had an Idea about it and some feedback would be welcome... Replacing the html tags with chr() codes for the data....

opinion? would that help with this?

 

[mikekolba]

Ok I think I got it now... Kinda Hideous using chr() codes to represent but they do work

This works to replace the data. I was thinking I'd do a second macro then to run and replace the USEPRODUCTIDHERE item I inserted in this first macro so it would be easier to replace the proper product ID number

Sub Convert_ICECat_URL_to_GoDaddy_HTML_URL()
Dim cell As Range
Dim Data1 As String
Dim Data2 As String
Dim Data3 As String
Dim Data4 As String
Dim Data5 As String
Let Data1 = Chr(60) & "div class"
Let Data2 = Chr(61) & """qsc-html-content""" & Chr(62)
Let Data3 = Chr(60) & "p" & Chr(62) & Chr(60) & "iframe src" & Chr(61) & """http://prf.icecat.biz/index.cgi?pro...i=start;smi=product;shopname=stsllc01;lang=en"""
Let Data4 = " width" & Chr(61) & """640"""
Let Data5 = " height" & Chr(61) & """480""" & Chr(62) & Chr(60) & Chr(47) & "iframe" & Chr(62) & Chr(60) & Chr(47) & "p" & Chr(62) & Chr(60) & Chr(47) & "div" & Chr(62)
MsgBox Data1
MsgBox Data2
MsgBox Data3
MsgBox Data4
MsgBox Data5

' Parse the ICECat Product URL for Product ID, and Replace the ICECat Product URL with the Product URL with
' HTML formatting and the parsed Product ID
For Each cell In Selection

If Not IsEmpty(cell.Value) Then
cell.Value = WorksheetFunction.Substitute(cell, cell.Value, Data1 & Data2 & Data3 & Data4 & Data5)
End If

Next cell
End Sub

 

[mikekolba]

ok- what's a good way to get the adjacent value in column A to the cell where this data is getting replaced and then take that value and replace the USEPRODUCTIDHERE text in the URL with it...

Ideas?

 

[mikekolba]

In case anyone is interested this is what I came up with...

Sub Convert_ICECat_URL_to_GoDaddy_HTML_URL()
Dim cell As Range
Dim Data1 As String
Dim Data2 As String
Dim Data3 As String
Dim Data4 As String
Dim Data5 As String
Dim Offset As Range
Let Data1 = Chr(60) & "div class"
Let Data2 = Chr(61) & """qsc-html-content""" & Chr(62) & Chr(10)
Let Data3 = Chr(60) & "p" & Chr(62) & Chr(60) & "iframe src" & Chr(61) & """http://prf.icecat.biz/index.cgi?pro...t;smi=product;shopname=openICECat-url;lang=en"""
Let Data4 = " width" & Chr(61) & """640"""
Let Data5 = " height" & Chr(61) & """440""" & Chr(62) & Chr(60) & Chr(47) & "iframe" & Chr(62) & Chr(60) & Chr(47) & "p" & Chr(62) & Chr(10) & Chr(60) & Chr(47) & "div" & Chr(62)
'MsgBox Data1
'MsgBox Data2
'MsgBox Data3
'MsgBox Data4
'MsgBox Data5
' Parse the ICECat Product URL for Product ID, and Replace the ICECat Product URL with the Product URL with
' HTML formatting and the parsed Product ID
For Each cell In Selection

If Not IsEmpty(cell.Value) Then

cell.Value = WorksheetFunction.Substitute(cell, cell.Value, Data1 & Data2 & Data3 & Data4 & Data5)
cell.Value = WorksheetFunction.Substitute(cell, "USEPRODUCTIDHERE", Cell.Offset(0, -3))
End If

Next cell
End Sub

for one Macro and


Sub Convert_ICECat_Thumbnail_Model_to_GoDaddy_HTML_URL()
Dim cell As Range
Dim Data1 As String
Dim Data2 As String
Dim Data3 As String
Dim Data4 As String
Dim Data5 As String
Dim Offset As Range
Let Data1 = Chr(60) & "div class" & Chr(61) & """qsc-html-content""" & Chr(62) & Chr(10)
Let Data2 = Chr(60) & "table style" & Chr(61) & Chr(34) & "align: 'left';" & Chr(34) & Chr(62) & Chr(10)
Let Data3 = Chr(60) & "tbody" & Chr(62) & Chr(10)
Let Data4 = Chr(60) & "tr" & Chr(62) & Chr(10)
Let Data5 = Chr(60) & "td" & Chr(62) & " "
Let Data6 = Chr(60) & "img alt" & Chr(34) & Chr(34) & " src" & Chr(61) & Chr(34)
Let Data7 = Chr(34) & Chr(47) & Chr(62) & Chr(60) & Chr(47) & "td" & Chr(62) & Chr(10)
Let Data8 = Chr(60) & "td" & Chr(62)
Let Data9 = Chr(60) & Chr(47) & "td" & Chr(62) & Chr(10)
Let Data10 = Chr(60) & Chr(47) & "tr" & Chr(62) & Chr(10)
Let Data11 = Chr(60) & Chr(47) & "tbody" & Chr(62) & Chr(10)
Let Data12 = Chr(60) & Chr(47) & "table" & Chr(62) & Chr(10)
Let Data13 = Chr(60) & Chr(47) & "div" & Chr(62)
'MsgBox Data1
'MsgBox Data2
'MsgBox Data3
'MsgBox Data4
'msgbox Data5
' Parse the ICECat Model Name and Thumbnail URL, and Replace with
' HTML formatting ,the Thumbnail URL and Model Name for GoDaddy
For Each cell In Selection

If Not IsEmpty(cell.Value) Then

cell.Value = WorksheetFunction.Substitute(cell, cell.Value, Data1 & Data2 & Data3 & Data4 & Data5 & Data6 & cell.Offset(0, -5) & Data7 & Data8 & cell.Value & Data9 & Data10 & Data11 & Data12 & Data13)
End If

Next cell
End


for another.

Was a bit of a bear. Now I have to write some more macro's to look up values in columns in other spreadsheets and copy different values in those sheets to columns in another blank sheet

This I could use some help with.