create looping based filelds.count

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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…

Reply | Quote