Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate /w Chr(10) error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate /w Chr(10) error
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate /w Chr(10) error
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CONCATENATE error? | Excel Discussion (Misc queries) | |||
Concatenate Error | Excel Discussion (Misc queries) | |||
Concatenate error | Excel Discussion (Misc queries) | |||
concatenate expression error | Excel Worksheet Functions | |||
Concatenate error and Date Format Error | Excel Programming |