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

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

"Derek Bliss" <(E-Mail Removed)> wrote in message
news:EE703120-D2D5-45A7-951A-(E-Mail Removed)...
> 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

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.

"Bob Phillips" wrote:

> 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
>
> "Derek Bliss" <(E-Mail Removed)> wrote in message
> news:EE703120-D2D5-45A7-951A-(E-Mail Removed)...
> > 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
>
>
>