Re: Exporting excel to text file
- From: "A C" <no@xxxxx>
- Date: Thu, 23 Jun 2005 10:26:17 +1200
"Exceluser" <Exceluser.1r1rhn@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:Exceluser.1r1rhn@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>
> Hi A,
>
> Thank you so much for your help, the While statement and empty is
> giving me an error.
>
Hi
Whats happening here is an issue here with my code rolling into multiple
lines when I pasted it into the posting.
I have attached a txt document where I copied and pasted the code straight
from the VBA module. This should preserve where new lines are appearing.
Try copying from this and see if it works. If not I can send you the .xls
itself if you supply an email address.
Regards
A
>
>
>
> A C Wrote:
> > "Exceluser" Exceluser.1qzwtn@xxxxxxxxxxxxxxxxxxxxxxxxxx wrote in
> > message
> > news:Exceluser.1qzwtn@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> >
> > selection Choice1 choice2
> > A1 x x
> > A2 x
> > A3 x
> > A4 x
> > A5 x x
> > A6
> > A7 x
> > A8 x
> > A9 x x
> >
> > The table above is done in excel. How can I output the data in a text
> > file as
> > Choice1(A1,A3,A4 etc..)
> > Choice2(A1, A2, A5 etc.)
> > I basically want to list all the selection with X's with the
> > corresponding
> > choices. The selection may very large as compared to what is
> > demonstrated
> > here. is there anyway I can get this done using VSB?
> >
> > I am using Windows XP, Excel 2003.
> >
> >
> > --
> > Exceluser-
> >
> > Below is a macro which will do what you want.
> > Assumptions:
> > * There are NO BLANK ROWS and NO BLANK COLUMNS in your table of data
> > * Every choice column has a header, eg Choice1
> > * An empty cell indicates no selection, anything else in the cell
> > indicates
> > a selection (you used "x" in your example).
> > * You have set up 2 ranges:
> > Filename - this holds the name of the file you want to write to,
> > including
> > the path. (or you could hardcode it into the VB code, i have supplied
> > an
> > example in the code itself)
> > Selection - the "Selection" fieldname cell
> >
> > The code also has no error checking, so for example if the filename is
> > invalid it will crash. You might want to add error checking later.
> > There is some sample code in there if you dont want to output an empty
> > choice, ie there were zero selections in that column of data.
> >
> > Hope this helps
> >
> > Regards
> > A
> >
> > Sub Macro1()
> >
> > 'Open the file for output
> > 'Filename = "c:\Temp\selection.txt"
> > Filename = Range("Filename").Value
> > filenumber = FreeFile
> > Open Filename For Output As #filenumber
> >
> > Range("Selection").Select
> > Range("Selection").Activate
> >
> > 'Loop over all the choices
> > colOffset = 1
> > While (ActiveCell.Offset(0, colOffset).Value "")
> > 'Start the output string
> > outputStr = ActiveCell.Offset(0, colOffset).Value & "(" 'eg Choice1(
> > 'Walk down each row and include it in the string if the cell is not
> > empty
> > rowOffset = 1
> > While (ActiveCell.Offset(rowOffset, 0).Value "")
> > If (ActiveCell.Offset(rowOffset, colOffset).Value "") Then
> > 'This is selected, add it to the string
> > outputStr = outputStr & ActiveCell.Offset(rowOffset, 0).Value &
> > ","
> > End If
> > rowOffset = rowOffset + 1
> > Wend
> > 'Each row checked, close off the string
> > If (Right(outputStr, 1) = ",") Then
> > outputStr = Left(outputStr, Len(outputStr) - 1)
> > End If
> > outputStr = outputStr & ")"
> > 'Add the output string to the file
> > Print #filenumber, outputStr
> > 'OR WRITE USING THIS CODE IF YOU DONT WANT TO PRINT OUT AN EMPTY SET,
> > eg
> > Choice1()
> > 'If (Right(outputStr, 2) "()") Then
> > ' 'Add the output string to the file
> > ' Print #filenumber, outputStr
> > 'End If
> >
> > 'Try the next column
> > colOffset = colOffset + 1
> > Wend
> >
> > 'Close the file
> > Close #filenumber
> >
> >
> > End Sub
>
>
> --
> Exceluser
begin 666 Selection_VBACode.txt
M4W5B($UA8W)O,2@I#0H-"B=/<&5N('1H92!F:6QE(&9O<B!O=71P=70-"B=&
M:6QE;F%M92 ](")C.EQ496UP7'-E;&5C=&EO;BYT>'0B#0I&:6QE;F%M92 ]
M(%)A;F=E*")&:6QE;F%M92(I+E9A;'5E#0IF:6QE;G5M8F5R(#T@1G)E949I
M;&4-"D]P96X@1FEL96YA;64@1F]R($]U='!U="!!<R C9FEL96YU;6)E<@T*
M#0I286YG92@B4V5L96-T:6]N(BDN4V5L96-T#0I286YG92@B4V5L96-T:6]N
M(BDN06-T:79A=&4-"@T*)TQO;W @;W9E<B!A;&P@=&AE(&-H;VEC97,-"F-O
M;$]F9G-E=" ](#$-"E=H:6QE("A!8W1I=F5#96QL+D]F9G-E="@P+"!C;VQ/
M9F9S970I+E9A;'5E(#P^("(B*0T*(" @("=3=&%R="!T:&4@;W5T<'5T('-T
M<FEN9PT*(" @(&]U='!U=%-T<B ]($%C=&EV94-E;&PN3V9F<V5T*# L(&-O
M;$]F9G-E="DN5F%L=64@)B B*"(@)V5G($-H;VEC93$H#0H@(" @)U=A;&L@
M9&]W;B!E86-H(')O=R!A;F0@:6YC;'5D92!I="!I;B!T:&4@<W1R:6YG(&EF
M('1H92!C96QL(&ES(&YO="!E;7!T>0T*(" @(')O=T]F9G-E=" ](#$-"B @
M("!7:&EL92 H06-T:79E0V5L;"Y/9F9S970H<F]W3V9F<V5T+" P*2Y686QU
M92 \/B B(BD-"B @(" @(" @268@*$%C=&EV94-E;&PN3V9F<V5T*')O=T]F
M9G-E="P@8V]L3V9F<V5T*2Y686QU92 \/B B(BD@5&AE;@T*(" @(" @(" @
M("=4:&ES(&ES('-E;&5C=&5D+"!A9&0@:70@=&\@=&AE('-T<FEN9PT*(" @
M(" @(" @(&]U='!U=%-T<B ](&]U='!U=%-T<B F($%C=&EV94-E;&PN3V9F
M<V5T*')O=T]F9G-E="P@,"DN5F%L=64@)B B+"(-"B @(" @(" @16YD($EF
M#0H@(" @(" @(')O=T]F9G-E=" ](')O=T]F9G-E=" K(#$-"B @("!796YD
M#0H@(" @)T5A8V@@<F]W(&-H96-K960L(&-L;W-E(&]F9B!T:&4@<W1R:6YG
M#0H@(" @268@*%)I9VAT*&]U='!U=%-T<BP@,2D@/2 B+"(I(%1H96X-"B @
M(" @(" @;W5T<'5T4W1R(#T@3&5F="AO=71P=713='(L($QE;BAO=71P=713
M='(I("T@,2D-"B @("!%;F0@268-"B @("!O=71P=713='(@/2!O=71P=713
M='(@)B B*2(-"B @(" G061D('1H92!O=71P=70@<W1R:6YG('1O('1H92!F
M:6QE#0H@(" @4')I;G0@(V9I;&5N=6UB97(L(&]U='!U=%-T<@T*(" @("=/
M4B!74DE412!54TE.1R!42$E3($-/1$4@248@64]5($1/3E0@5T%.5"!43R!0
M4DE.5"!/550@04X@14U05%D@4T54+"!E9R!#:&]I8V4Q*"D-"B @(" G268@
M*%)I9VAT*&]U='!U=%-T<BP@,BD@/#X@(B@I(BD@5&AE;@T*(" @("<@(" @
M)T%D9"!T:&4@;W5T<'5T('-T<FEN9R!T;R!T:&4@9FEL90T*(" @("<@(" @
M4')I;G0@(V9I;&5N=6UB97(L(&]U='!U=%-T<@T*(" @("=%;F0@268-"B @
M(" -"B @(" G5')Y('1H92!N97AT(&-O;'5M;@T*(" @(&-O;$]F9G-E=" ]
M(&-O;$]F9G-E=" K(#$-"E=E;F0-"@T*)T-L;W-E('1H92!F:6QE#0I#;&]S
:92 C9FEL96YU;6)E<@T*#0H-"D5N9"!3=6(`
`
end
.
- References:
- Exporting excel to text file
- From: Exceluser
- Exporting excel to text file
- Prev by Date: Automatic Date Update
- Next by Date: RE: Wrap Text from one cell to another cell
- Previous by thread: Exporting excel to text file
- Next by thread: Exporting excel to text file
- Index(es):
Relevant Pages
|
