Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I use this line to write into txt file:

    Num = RS1.Fields.Count

    Do While RS1.EOF <> True
    Write #FileNum, RS1.Fields(0); RS1.Fields(1); RS1.Fields(2); RS1.Fields(3); RS1.Fields(4); RS1.Fields(5)
    RS1.MoveNext
    Loop

    but i know the number of fileds is possile to loop the fileds and wririte the string?

    In this case i have 36 fileds (see Num = RS1.Fields.Count) and instead to write:
    Write #FileNum, RS1.Fields(0); RS1.Fields(1); RS1.Fields(2); RS1.Fields(3); RS1.Fields(4); RS1.Fields(5)...RS1.Fields(35)

    loop based the number of fileds

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Why not use the DoCmd.TransferText command based upon a query?
    Build a query with the fields you want, in this way its only the one instruction required.


    Or, you could use a loop like:

    Code:
    Num = RS1.Fields.Count
    Dim ii as integer
    Do While RS1.EOF <> True
        For ii = 0 to Num - 1
            Write #FileNum, RS1("Fields" & ii);
        Next ii
        Write #Filenum vbCrLf;    
    RS1.MoveNext
    Loop
    nb. this is all air code

    nb2. as Andrew pointed out your fields start at 0 so I changed the For/Next loop to reflect this.

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Try something like this

    Code:
    Dim intF as Integer
    
    
    num = RS1.Fields.Count
    
    Do Until RS1.EOF
        For intF = 0 To num - 1 
            If intF = (num - 1) Then
                Write #FileNum, RS1.Fields(intF)
            Else
                Write #FileNum, RS1.Fields(intF);
            End If
     	Next
         RS1.MoveNext
    Loop
    Field Numbers start at 0 to Num fields -1 if you are to use a FOR ... NEXT Loop

    You also need to make sure you just use a WRITE with no end ; on the last field to make sure you get one line per field.
    Andrew

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by patt View Post
    Why not use the DoCmd.TransferText command based upon a query?
    Build a query with the fields you want, in this way its only the one instruction required.


    Or, you could use a loop like:

    Code:
    Num = RS1.Fields.Count
    Dim ii as integer
    Do While RS1.EOF <> True
        For ii = 0 to Num - 1
            Write #FileNum, RS1("Fields" & ii);
        Next ii
        Write #Filenum vbCrLf;    
    RS1.MoveNext
    Loop
    nb. this is all air code

    nb2. as Andrew pointed out your fields start at 0 so I changed the For/Next loop to reflect this.
    i know this way (DoCmd.TransferText )... but i think not possible to use in VBA for excel, or not?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can start Access from Excel using code (this is called Automation), and use objAccess.DoCmd.TransferText where objAccess is the Access.Application object that you created.

    Or you can use CopyFromRecordset in Excel VBA to get data from a recordset into a worksheet, then save that worksheet as a text file.

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    You can start Access from Excel using code (this is called Automation), and use objAccess.DoCmd.TransferText where objAccess is the Access.Application object that you created.

    Or you can use CopyFromRecordset in Excel VBA to get data from a recordset into a worksheet, then save that worksheet as a text file.
    I use CopyFromRecordset but my dubt is:

    The sql sever data base contain a table with 36 fileds and all records are filled with data, wath about time to copy from sql server to sheet?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by sal21 View Post
    The sql sever data base contain a table with 36 fileds and all records are filled with data, wath about time to copy from sql server to sheet?
    The only way to know that is to try it...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •