ok so im trying to create a macro that will take my set cells and insert them into html code...
what i have is cells with my html code and cells with my values that i need... first it ref. the first html J2 and places it in B13 next it ref. my value that i need and places it in C13 and so on... it gets to a file name that is based off of a date so lets say for date is 6/20/2012 it would take that and format the next cell to pull that date but format the text to go to 20120620.mp3... when the ref. code takes that cell it takes the format... but when i run another script to join all the cells together to one it changes the date to the original format of 6/20/2012... i thought maybe it pulling the original formatting but i changed the original format of the first date cell and it still keeps it in 6/20/2012 format.
<div id="messageDate">6/20/2012</div><audio id="audio" preload="none" controls="controls"><source src="sermons_mp3/mp3/6/20/2012" type="audio/mpeg"></audio>
it should look like this
<div id="messageDate">6/20/2012</div><audio id="audio" preload="none" controls="controls"><source src="sermons_mp3/mp3/20120620.mp3" type="audio/mpeg"></audio>
im using this code to combine the cells
Sub JoinText()
myCol = Selection.Columns.Count
For i = 1 To myCol
ActiveCell = ActiveCell.Offset(0, 0) & ActiveCell.Offset(0, i)
ActiveCell.Offset(0, i) = ""
Next i
End Sub
and ive tried this one too
Function ConcatinateAllCellValuesInRange(sourceRange As Excel.Range) As String
Dim finalValue As String
Dim cell As Excel.Range
For Each cell In sourceRange.Cells
finalValue = finalValue + CStr(cell.Value)
Next cell
ConcatinateAllCellValuesInRange = finalValue
End Function
Sub MyMacro()
Range("b14").Select
ActiveCe
ll.FormulaR1C1 = ConcatinateAllCellValuesInRange([b13:r13])
End Sub
and all with the same results... i hope this helps and someone can give me some help on this... i am totally new at VBA and have no idea what im doing... what i have done has been me just playing and getting lucky... lol
thanks
if you concatenate the date from a cell containing date only (e.g. 6/20/2012
as opposed to sermons_mp3/mp3/6/20/2012
), you can use following formula to get the src argument:
="sermons_mp3/mp3/" & text(date_value, "yyyymmdd") & ".mp3"