need to export row as csv file with column e plus .bom as file name

R

rod

need to export part of row as csv file with column e plus .bom extension
as file name.





a b c d e f
g h i j k l m n o p q r
s t u


done1 11/28/05 312944\ 862423 599-020d p a g
32 10/25/06
g:\sigma\sndata55\parts55\ done2 action cell



cells would be s,c,e,h,i,f,d,,,j,,,,t

notice cell j in csv file will have to be date as shown 2006/10/06
instead of the 10/25/06 that excel file shows in row,, because this is how
the date is inserted into list it is pasted as text from a as400 dat file ..
g:\sigma\sndata55\parts55\312944\599-020d,g,32,p,862423,,,2006/10/25,,,,done2file would be saved as c:\tmp\599-020d.bomexample cells in column u could have the action code in each cell pasteddown sheet ..like row 1 click on cell in u1 and csv file would be created of row 1like row 2 click on cell in u2 and csv file would be created of row 2rod
 
R

rod

Jim

You are correct on that one for sure.
Text looked in order before I sent it out..
I hope it wont happen again.
I hope this is not too confusing the way I have explaind it here.

What I am trying to do is take a active row and export that row out as a
delimited file format .
Really just using columns A thru T that have any info in them per row.
Some of the colums in the row I am not using .

The cells in order needed in the output delimeted file would be
S,C,E,H,I,F,D,,,J,,,,T
The empty spaces in the delimited file could just be a cell that holds the
extra comma or however.
This means I need these empty spaces in the delimited file, not other cells
in the excel file that hold no value.

In the column J of the excel file there is a date, this is displayed as
10/25/06.
This is how the date info is placed into the excel file from an as400 dat
file that someone else does for us.
But need the this Date output in a different format.
Example
Excel Exported columns - s ,c
,e ,h ,i ,f ,d ,,,j ,,,,t
Would look like this-
g:\sigma\sndata55\parts55\312944\599-020d,g,32,p,862423,,,2006/10/25,,,,donefile2
That means I have joined colums S,C,E,H,I,F,D (PLUS 2 EMPTY SPACES) then J
(PLUS 3 EMPTY SPACES) then T of the target row .

The delimited file would need be saved as c:\tmp\599-020d.bom
That means delimited file would use value of cell in column E in that active
row for file name and have an extension of .BOM for file name.


The cells in column U could have the action code or trigger in each cell to
create the delimeted file for that active row , pasteddown sheet of column U
..
Like active row is on A3 , click on cell in U3 and delimted file be created
for that row A3 .

I can explain more orget you a small excel file example, and a Bom file if
it will help any further ..

I use this below , then copy the cell value into notepad and then save the
file as 599-020d.bom
=A3&C3&E3&","&H3&","&I3&","&F3&","&D3&","&","&","&J3&","&","&","&","&","&T3

G:\SIGMA\SNDATA55\PARTS55\312944\599-020D,G,32,P,1122445,,,39015,,,,,DONEFILE2

As I mentioned before the date does not come out correct in what the above
function does so I wolud have to edit in notepad as 2006/10/25
and then save file again ..
I can explain more orget you a small excel file example, and a Bom file if
it will help any further ..

Thanks
Rod
 
J

Jim Cone

Rod,
This ought to get you almost there.
It uses the row that has the active cell in it.
It picks up the text using a variant array.
It then reads the data from the array into a string variable.
A new text file is opened using the Microsoft Scripting Runtime
FileSystemObject and the text is added to the file and the file is closed.

Sub AsRequested()
Dim oFSO As Object
Dim oFile As Object
Dim strRow As String
Dim strName As String
Dim strPath As String
Dim varRow As Variant
Dim lngR As Long
Dim N As Long

lngR = ActiveCell.Row
strName = Cells(lngR, 5).Value
strPath = "c:\tmp\" & strName & ".Bom"

varRow = Array(Cells(lngR, 1).Value, Cells(lngR, 3).Value, _
Cells(lngR, 5).Value, ",", Cells(lngR, 8).Value, ",", _
Cells(lngR, 9).Value, ",", Cells(lngR, 6).Value, ",", _
Cells(lngR, 4).Value, ",", ",", ",", ",", Cells(lngR, 10).Value, _
",", ",", ",", ",", Cells(lngR, 20).Value)
For N = 0 To UBound(varRow)
strRow = strRow & varRow(N)
Next 'N

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.CreateTextFile(strPath, True)
oFile.WriteLine (strRow)
oFile.Close

Set oFile = Nothing
Set oFSO = Nothing
End Sub
-----------
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"rod"
<[email protected]>
wrote in message
Jim
You are correct on that one for sure.
Text looked in order before I sent it out..
I hope it wont happen again.
I hope this is not too confusing the way I have explaind it here.

What I am trying to do is take a active row and export that row out as a
delimited file format .
Really just using columns A thru T that have any info in them per row.
Some of the colums in the row I am not using .
The cells in order needed in the output delimeted file would be
S,C,E,H,I,F,D,,,J,,,,T
The empty spaces in the delimited file could just be a cell that holds the
extra comma or however.
This means I need these empty spaces in the delimited file, not other cells
in the excel file that hold no value.

In the column J of the excel file there is a date, this is displayed as
10/25/06.
This is how the date info is placed into the excel file from an as400 dat
file that someone else does for us.
But need the this Date output in a different format.
Example
Excel Exported columns - s ,c
,e ,h ,i ,f ,d ,,,j ,,,,t
Would look like this-
g:\sigma\sndata55\parts55\312944\599-020d,g,32,p,862423,,,2006/10/25,,,,donefile2
That means I have joined colums S,C,E,H,I,F,D (PLUS 2 EMPTY SPACES) then J
(PLUS 3 EMPTY SPACES) then T of the target row .

The delimited file would need be saved as c:\tmp\599-020d.bom
That means delimited file would use value of cell in column E in that active
row for file name and have an extension of .BOM for file name.
The cells in column U could have the action code or trigger in each cell to
create the delimeted file for that active row , pasteddown sheet of column U .
Like active row is on A3 , click on cell in U3 and delimted file be created
for that row A3 .
I can explain more orget you a small excel file example, and a Bom file if
it will help any further ..
I use this below , then copy the cell value into notepad and then save the
file as 599-020d.bom
=A3&C3&E3&","&H3&","&I3&","&F3&","&D3&","&","&","&J3&","&","&","&","&","&T3

G:\SIGMA\SNDATA55\PARTS55\312944\599-020D,G,32,P,1122445,,,39015,,,,,DONEFILE2
As I mentioned before the date does not come out correct in what the above
function does so I wolud have to edit in notepad as 2006/10/25
and then save file again ..
I can explain more orget you a small excel file example, and a Bom file if
it will help any further ..
Thanks
Rod
 
J

James Silverton

Hello, Jim!
You wrote on Sun, 29 Oct 2006 17:52:32 -0800:

JC> Sub AsRequested()
JC> Dim oFSO As Object
JC> Dim oFile As Object
JC> Dim strRow As String
JC> Dim strName As String
JC> Dim strPath As String
JC> Dim varRow As Variant
JC> Dim lngR As Long
JC> Dim N As Long

JC> lngR = ActiveCell.Row
JC> strName = Cells(lngR, 5).Value
JC> strPath = "c:\tmp\" & strName & ".Bom"

JC> varRow = Array(Cells(lngR, 1).Value, Cells(lngR,
JC> 3).Value, _
JC> Cells(lngR, 5).Value, ",", Cells(lngR, 8).Value,
JC> ",", _
JC> Cells(lngR, 9).Value, ",", Cells(lngR, 6).Value,
JC> ",", _
JC> Cells(lngR, 4).Value, ",", ",", ",", ",",
JC> Cells(lngR, 10).Value, _
JC> ",", ",", ",", ",", Cells(lngR, 20).Value)
JC> For N = 0 To UBound(varRow)
JC> strRow = strRow & varRow(N)
JC> Next 'N

JC> Set oFSO = CreateObject("Scripting.FileSystemObject")
JC> Set oFile = oFSO.CreateTextFile(strPath, True)
JC> oFile.WriteLine (strRow)
JC> oFile.Close

JC> Set oFile = Nothing
JC> Set oFSO = Nothing
JC> End Sub
JC> -----------
JC> Jim Cone
JC> San Francisco, USA
JC> http://www.officeletter.com/blink/specialsort.html

JC> "rod"
JC> <[email protected]>
JC> wrote in message
JC> Jim
JC> You are correct on that one for sure.
JC> Text looked in order before I sent it out..
JC> I hope it wont happen again.
JC> I hope this is not too confusing the way I have explaind it
JC> here.

JC> What I am trying to do is take a active row and export
JC> that row out as a delimited file format .
JC> Really just using columns A thru T that have any info in
JC> them per row. Some of the colums in the row I am not
JC> using .The cells in order needed in the output delimeted
JC> file would be S,C,E,H,I,F,D,,,J,,,,T
JC> The empty spaces in the delimited file could just be a cell
JC> that holds the extra comma or however.
JC> This means I need these empty spaces in the delimited file,
JC> not other cells in the excel file that hold no value.

JC> In the column J of the excel file there is a date, this is
JC> displayed as 10/25/06.
JC> This is how the date info is placed into the excel file
JC> from an as400 dat file that someone else does for us.
JC> But need the this Date output in a different format.
JC> Example
JC> Excel Exported columns - s
JC> ,c ,e ,h ,i ,f ,d ,,,j
JC> ,,,,t Would look like this-
JC> g:\sigma\sndata55\parts55\312944\599-020d,g,32,p,862423,,,2
JC> 006/10/25,,,,donefile2 That means I have joined colums
JC> S,C,E,H,I,F,D (PLUS 2 EMPTY SPACES) then J (PLUS 3 EMPTY
JC> SPACES) then T of the target row .

JC> The delimited file would need be saved as
JC> c:\tmp\599-020d.bom That means delimited file would use
JC> value of cell in column E in that active row for file name
JC> and have an extension of .BOM for file name.The cells in
JC> column U could have the action code or trigger in each cell
JC> to create the delimeted file for that active row ,
JC> pasteddown sheet of column U . Like active row is on A3 ,
JC> click on cell in U3 and delimted file be created for that
JC> row A3 . I can explain more orget you a small excel file
JC> example, and a Bom file if it will help any further ..
JC> I use this below , then copy the cell value into notepad
JC> and then save the file as 599-020d.bom
JC> =A3&C3&E3&","&H3&","&I3&","&F3&","&D3&","&","&","&J3&","&",
JC> "&","&","&","&T3

JC> G:\SIGMA\SNDATA55\PARTS55\312944\599-020D,G,32,P,1122445,,,
JC> 39015,,,,,DONEFILE2 As I mentioned before the date does not
JC> come out correct in what the above function does so I wolud
JC> have to edit in notepad as 2006/10/25 and then save file
JC> again .. I can explain more orget you a small excel file
JC> example, and a Bom file if it will help any further ..
JC> Thanks
JC> Rod

JC> ??>> Your message is pretty much jumbled up.
??>> Suggest you try again with a revised example.
??>> Using normal English grammar rules and punctuation will
??>> also help. -- Jim Cone San Francisco,
??>> USA http://www.realezsites.com/bus/primitivesoftware

You know, I don't suppose it will help if you want to export
several times but the normal processes of copying, removal of
formatting with PureText and copying again will actually produce
CSV delimited data. If it is absolutely necessary, Word's
replace command would turn the tabs into commas.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not
 
R

rod

Jim

Yes this will get me goin.
Using as it is now.It does the job.
When I get farther along with some other ideas dealing with excel I'll ask
away..

Thanks again,
Rod

Jim Cone said:
Rod,
This ought to get you almost there.
It uses the row that has the active cell in it.
It picks up the text using a variant array.
It then reads the data from the array into a string variable.
A new text file is opened using the Microsoft Scripting Runtime
FileSystemObject and the text is added to the file and the file is closed.

Sub AsRequested()
Dim oFSO As Object
Dim oFile As Object
Dim strRow As String
Dim strName As String
Dim strPath As String
Dim varRow As Variant
Dim lngR As Long
Dim N As Long

lngR = ActiveCell.Row
strName = Cells(lngR, 5).Value
strPath = "c:\tmp\" & strName & ".Bom"

varRow = Array(Cells(lngR, 1).Value, Cells(lngR, 3).Value, _
Cells(lngR, 5).Value, ",", Cells(lngR, 8).Value, ",", _
Cells(lngR, 9).Value, ",", Cells(lngR, 6).Value, ",", _
Cells(lngR, 4).Value, ",", ",", ",", ",", Cells(lngR, 10).Value,
_
",", ",", ",", ",", Cells(lngR, 20).Value)
For N = 0 To UBound(varRow)
strRow = strRow & varRow(N)
Next 'N

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.CreateTextFile(strPath, True)
oFile.WriteLine (strRow)
oFile.Close

Set oFile = Nothing
Set oFSO = Nothing
End Sub
-----------
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"rod"
<[email protected]>
wrote in message
Jim
You are correct on that one for sure.
Text looked in order before I sent it out..
I hope it wont happen again.
I hope this is not too confusing the way I have explaind it here.

What I am trying to do is take a active row and export that row out as a
delimited file format .
Really just using columns A thru T that have any info in them per row.
Some of the colums in the row I am not using .
The cells in order needed in the output delimeted file would be
S,C,E,H,I,F,D,,,J,,,,T
The empty spaces in the delimited file could just be a cell that holds the
extra comma or however.
This means I need these empty spaces in the delimited file, not other
cells
in the excel file that hold no value.

In the column J of the excel file there is a date, this is displayed as
10/25/06.
This is how the date info is placed into the excel file from an as400 dat
file that someone else does for us.
But need the this Date output in a different format.
Example
Excel Exported columns - s ,c
,e ,h ,i ,f ,d ,,,j ,,,,t
Would look like this-
g:\sigma\sndata55\parts55\312944\599-020d,g,32,p,862423,,,2006/10/25,,,,donefile2
That means I have joined colums S,C,E,H,I,F,D (PLUS 2 EMPTY SPACES) then
J
(PLUS 3 EMPTY SPACES) then T of the target row .

The delimited file would need be saved as c:\tmp\599-020d.bom
That means delimited file would use value of cell in column E in that
active
row for file name and have an extension of .BOM for file name.
The cells in column U could have the action code or trigger in each cell
to
create the delimeted file for that active row , pasteddown sheet of column
U .
Like active row is on A3 , click on cell in U3 and delimted file be
created
for that row A3 .
I can explain more orget you a small excel file example, and a Bom file if
it will help any further ..
I use this below , then copy the cell value into notepad and then save the
file as 599-020d.bom
=A3&C3&E3&","&H3&","&I3&","&F3&","&D3&","&","&","&J3&","&","&","&","&","&T3

G:\SIGMA\SNDATA55\PARTS55\312944\599-020D,G,32,P,1122445,,,39015,,,,,DONEFILE2
As I mentioned before the date does not come out correct in what the above
function does so I wolud have to edit in notepad as 2006/10/25
and then save file again ..
I can explain more orget you a small excel file example, and a Bom file if
it will help any further ..
Thanks
Rod
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top