Exporting excel to text file

Tech-Archive recommends: Fix windows errors by optimizing your registry


Hi,
Thanks so much for your help. I am just learning VSB. I guess that's
why I am so "slow". What is "filenumber"? It's not a built in, so what
do I change it to? The error it's giving is "Runtime error "1004"
Application defined or objective-defined error"

I am using Microsoft Excel 2003, with Visual basic 6.3
Email: usaqueen@xxxxxxxxx

thanks




A C Wrote:
> "Exceluser" Exceluser.1rb0tm@xxxxxxxxxxxxxxxxxxxxxxxxxx wrote in
> message
> news:Exceluser.1rb0tm@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>
> Thank you so much!It is not working as yet, I hope I get it to work
> eventually. I keep giving me a 1004 error.-
>
> Hi
>
> Can you be more specific about the error you receive, 1004 errors can
> be
> caused by multiple things. Post the error text/message you receive in
> the
> error dialog as well as the number.
>
> I tried to send you the test excel workbook I created which works for
> the
> very small dummy dataset I input, but your email is invalid. Sent to:
> Exceluser.1rb0tm@xxxxxxxxxxxxxxxxxxxxxxxxxx
> If you want this file post your email address (or post a corrupted
> version
> and clues on how to uncorrupt it) and I will send it again.
>
> Regards
> A
> -
>
>
> A C Wrote:-
> "Exceluser" Exceluser.1r1rhn@xxxxxxxxxxxxxxxxxxxxxxxxxx wrote in
> message
> news:Exceluser.1r1rhn@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>
> 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--
>
>
> --
> Exceluser-


--
Exceluser
.

Relevant Pages

  • Exporting excel to text file
    ... > I basically want to list all the selection with X's with the ... > * Every choice column has a header, eg Choice1 ... > Open Filename For Output As #filenumber ... > 'Add the output string to the file ...
    (microsoft.public.excel.misc)
  • Re: Exporting excel to text file
    ... > I basically want to list all the selection with X's with the ... * Every choice column has a header, eg Choice1 ... Open Filename For Output As #filenumber ... 'Add the output string to the file ...
    (microsoft.public.excel.misc)
  • Exporting excel to text file
    ... Thank you so much for your help, the While statement and empty is ... > I basically want to list all the selection with X's with the ... > * Every choice column has a header, eg Choice1 ... > 'Add the output string to the file ...
    (microsoft.public.excel.misc)