CODEWITHSUNDEEP
excelvba
alex_silver
alex_silver

Reputation: 1

How to use replace function in a macro to replace a character with something else

I am a noob at vba and i need some help with on old file at my workplace. Long story short i have a template that generates an excel file used for printing stickers. One of the column is name Picture and it points to the location of a picture. Because they change something in the database the name generated by the database contains a slash "/" which of course cannot be used in the filename. I need to adjust the code so that it replaces the "/" with @S@ (we have this on our website pictures but our IT guy left so we don`t have nobody capable at the moment) I would appreciate if you guys could help me out.

Following is a part of the code containing this picture column :

ls_Path = "C:\Pictures for Barcode\"
Rs.Open ls_Sql, conn
Do While Not Rs.EOF
' Copy and insert new row
s_Copyrow ls_Sheet, ll_Row

' Put the data
ws.Cells(ll_Row, PROD_COL).Value = Rs("prod_complete")
ws.Cells(ll_Row, DESC_COL).Value = f_OnlyAsciiCharacter(Rs("prod_desc"))
ws.Cells(ll_Row, BARCODE_COL).Value = Rs("prod_barcode")
ws.Cells(ll_Row, COLOR_COL).Value = Rs("full_color")
ws.Cells(ll_Row, MAT1_COL).Value = f_OnlyAsciiCharacter(Rs("mat1"))
ws.Cells(ll_Row, MAT2_COL).Value = f_OnlyAsciiCharacter(Rs("mat2"))
ws.Cells(ll_Row, MAT3_COL).Value = f_OnlyAsciiCharacter(Rs("mat3"))
ws.Cells(ll_Row, MAT4_COL).Value = f_OnlyAsciiCharacter(Rs("mat4"))
ws.Cells(ll_Row, MAT5_COL).Value = f_OnlyAsciiCharacter(Rs("mat5"))
ws.Cells(ll_Row, PIC_COL).Value = ls_Path & Rs("prod_picture")
ws.Cells(ll_Row, PRINT_COL).Value = 1

' Go to next item
ll_Row = ll_Row + 1

From what i understand i need to use the replace function with this line ws.Cells(ll_Row, PIC_COL).Value = ls_Path & Rs("prod_picture") so that it replaces "/" with what i want which is "@S@"

Thank you in advance.

Upvotes: 0

Views: 121

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

ws.Cells(ll_Row, PIC_COL).Value = Replace(ls_Path & Rs("prod_picture").Value, "\", "@S@")

Upvotes: 1

Related Questions