Concatenate /w Chr(10) error

[Derek Bliss]

What I working with:
I use a program which connects to all my systems on my network and collects
data about each system. This program can only collect and export the data
into a .csv file format. I have another program that does our Disaster
Recovery planning. It is using a SQL database.

What I'm trying to do:
I take the report that has all the Hardware information and clean it up. One
of the fields, called "Description" needs to have 8 cells of data in it. I'm
using the "Concatenate" command to do this. Once my column is populated with
the concatenated data, I then have to copy the column into a new column with
only the data value so that it will import into the SQL Database. By the way,
I have to save the file as an xls file format so that it keeps the Line
Feeds. I've tested this by manually entering in the data and using the
"Alt+Enter" to create the line feeds.

Here is my code which does the concatenate fine but without a Chr(10)
between each description and it does not give an error.

ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""Asset Tag: "",RC[1],"" / S/N: "",RC[2],"" /
Description: "",RC[18],"" / IP Address: "",RC[19],"" / BU Cost Center:
"",RC[15],"" / BU CC Description: "",RC[16],"" / Organization Name:
"",RC[17],"" / MAC Address: "",RC[20])"

====================================================
When I put the "& Chr(10) &" in the places where I want the line feed, I get
the following error:

Run-time error '1004': Application-defined or object-defined error

Here is the code

ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""Asset Tag: "",RC[1]," & Chr(10) & "S/N: "",RC[2]," &
Chr(10) & "Discription: "",RC[18]," & Chr(10) & "IP Address: "",RC[19]," &
Chr(10) & "BU Cost Center: "",RC[15]," & Chr(10) & "BU CC Description:
"",RC[16]," & Chr(10) & "Organization Name: "",RC[17]," & Chr(10) & "MAC
Address: "",RC[20])"


Can anyone figure out why I'm getting this error? Do I not have something
turned on like a reference?

Thanks for any help.

Derek

 

[Bob Phillips]

This works for me, including corrected spelling <g>

ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""Asset Tag: "",RC[1]," & Chr(10) & _
"""S/N: "",RC[2]," & Chr(10) & _
"""Description: "",RC[18]," & Chr(10) & _
"""IP Address: "",RC[19]," & Chr(10) & _
"""BU Cost Center: "",RC[15]," & Chr(10) & _
"""BU CC Description: "",RC[16]," & Chr(10) & _
"""Organization Name: "",RC[17]," & Chr(10) & _
"""MAC Address: "",RC[20])"

 

[Derek Bliss]

WOW, just missing "" after the Chr(10) & made all the difference. Thanks and
I wish I could use Spell checker in VB, LOL

Thanks again Bob.

This works for me, including corrected spelling <g>

ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""Asset Tag: "",RC[1]," & Chr(10) & _
"""S/N: "",RC[2]," & Chr(10) & _
"""Description: "",RC[18]," & Chr(10) & _
"""IP Address: "",RC[19]," & Chr(10) & _
"""BU Cost Center: "",RC[15]," & Chr(10) & _
"""BU CC Description: "",RC[16]," & Chr(10) & _
"""Organization Name: "",RC[17]," & Chr(10) & _
"""MAC Address: "",RC[20])"



--
__________________________________
HTH

Bob

What I working with:
I use a program which connects to all my systems on my network and
collects
data about each system. This program can only collect and export the data
into a .csv file format. I have another program that does our Disaster
Recovery planning. It is using a SQL database.

What I'm trying to do:
I take the report that has all the Hardware information and clean it up.
One
of the fields, called "Description" needs to have 8 cells of data in it.
I'm
using the "Concatenate" command to do this. Once my column is populated
with
the concatenated data, I then have to copy the column into a new column
with
only the data value so that it will import into the SQL Database. By the
way,
I have to save the file as an xls file format so that it keeps the Line
Feeds. I've tested this by manually entering in the data and using the
"Alt+Enter" to create the line feeds.

Here is my code which does the concatenate fine but without a Chr(10)
between each description and it does not give an error.

ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""Asset Tag: "",RC[1],"" / S/N: "",RC[2],"" /
Description: "",RC[18],"" / IP Address: "",RC[19],"" / BU Cost Center:
"",RC[15],"" / BU CC Description: "",RC[16],"" / Organization Name:
"",RC[17],"" / MAC Address: "",RC[20])"

====================================================
When I put the "& Chr(10) &" in the places where I want the line feed, I
get
the following error:

Run-time error '1004': Application-defined or object-defined error

Here is the code

ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""Asset Tag: "",RC[1]," & Chr(10) & "S/N: "",RC[2],"
&
Chr(10) & "Discription: "",RC[18]," & Chr(10) & "IP Address: "",RC[19]," &
Chr(10) & "BU Cost Center: "",RC[15]," & Chr(10) & "BU CC Description:
"",RC[16]," & Chr(10) & "Organization Name: "",RC[17]," & Chr(10) & "MAC
Address: "",RC[20])"


Can anyone figure out why I'm getting this error? Do I not have something
turned on like a reference?

Thanks for any help.

Derek