Re: Exporting excel to text file

> 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
>
>

filenumber is just a new local variable which is going to hold the system
fiile number than FreeFile returns. You could just as legitimately named it
myFilenumber, or even myWheelbarrow if you felt so inclined.

One of the things you should check is your references. Open visual basic
for your workbook, then click Tools-->References.
You should have something like the following ticked (ticked references will
be at the top of the list), although the numbers may be slightly different:
-Visual Basic For Applications
-Microsoft Excel 10.0 object library (possibly a diff number)
-OLE Automation
-Microsoft Office 10.0 Object library ("" "")
If not, find them in the list and tick them and try and run it. Not all of
the above are actually required, but as you may well extend the
functionality of your program at a later date it's a good base set to start
with (ie you could get away with less but you dont want to be dealing with
not having the proper references if you extend your code at a later date so
the above is a good set to have).

I have sent you an email with my test book in it so you can see a version
which works. If it doesnt work then this may indicate an issue with your
system. I have made some slight tweaks to it so that it explicitly lists
the object/variable types. You will see what I mean when you compare the
new code to the original code posted.

Regards
A

"Exceluser" <Exceluser.1rgkto@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:Exceluser.1rgkto@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>

>
>
> 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

  • Re: Exporting excel to text file
    ... >> Thank you so much for your help, the While statement and empty is ... The selection may very large as compared to what is ... >> Open Filename For Output As #filenumber ... >> 'Walk down each row and include it in the string if the cell is not ...
    (microsoft.public.excel.misc)
  • Re: Exporting excel to text file
    ... The selection may very large as compared to what is ... >> * An empty cell indicates no selection, ... >> Open Filename For Output As #filenumber ... >> 'Walk down each row and include it in the string if the cell is not ...
    (microsoft.public.excel.misc)
  • Re: BrowseForFolder
    ... WinXp and a for root file selection in Win2k. ... present on all installations and is exposed through the comdlg32.ocx file. ... UserOpenDlg.initialDir= string ...
    (microsoft.public.scripting.vbscript)
  • Re: Query using 3 listboxes
    ... Dim MyDB As DAO.Database ... Dim strWhere As String ... Dim varItem As Variant ... MsgBox "Please make a selection from each list",, "Selection ...
    (microsoft.public.access.formscoding)
  • Boolean value stored in a combobox!
    ... I use the stored values in tblStorage to set some kind of default value next ... cboMemberCard instead of an empty string. ... selection. ... Dim db As Database, rst As Recordset ...
    (microsoft.public.access.formscoding)