data not fixed length vba programming on text file

[kkshansid]

i want to print report through vba programming on text file but the problem i find that the data is not of fixed length on report according to data structure length. i want that for detail field of data type text and field size 5 will always occupy space of 5 characters even if it is blank or of 2 character.if i export it in dbf format its possible through foxpro but not in vba.
kindly help to print report through vba
thanx in advance

[twinnyfo]

kkshansid,

Could you give us an example of what you are looking for? For example, do you want leading or trailing characters displayed to fill up extra empty spaces? Please explain and show us what you have tried so far....

[kkshansid]

Sub WriteToATextFile()
'first set a string which contains the path to the file you want to create.
'this example creates one and stores it in the root directory
MyFile = "c:\" & "EXPENDITURE.txt"
'set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum

Dim r1 As ADODB.Recordset
Set r1 = New ADODB.Recordset
r1.ActiveConnection = CurrentProject.Connection
r1.CursorType = adOpenDynamic
r1.LockType = adLockOptimistic
r1.Open ("SELECT * from  FIN_MAY12")
If Not (r1.EOF And r1.BOF) Then
    Do While Not r1.EOF
    
         Print #fnum, r1("chk_Date") & " |" & r1("chk_no") & " |" & r1("particulars") & " |" & r1("debit") & " |" & Space(5) & r1("credit") & " |" & Space(5)
         Print #fnum, String(50, "-")
         Chr (12)
         r1.MoveNext
    Loop
End If
Close #fnum
End Sub

i want to fill up empty spaces with empty spaces so that formatting will not get disturbed.

[zmbd]

Are you tring to do this:

http://office.microsoft.com/en-us/access-help/export-data-to-a-text-file-HA010006905.aspx

-z

[zmbd]

Ahh.. I see you posted just before I hit enter...
try the following:

http://msdn.microsoft.com/en-us/library/office/ff835958.aspx

Use the transfer type as "acExportFi xed"

Ignore the code offered at the bottom... don't know why they'd offer a mail-merge to word automation code... but it is nice to review... could be useful for some other project!

Please let us know if this works...

-z

[EDIT = 2012-08-08-2335CST]
You'll more than likely need the Schema.ini file...
This link has the format example... scroll down a tad

http://support.microsoft.com/kb/241477

You'll need to place it in the SAME folder as you will be exporting the text file to... (ie: if you're exporting to: "%userprofile%\ textout\" then the schema.ini will need to be there and you will need to specify the file-name in the code as indicated.)

-z

[kkshansid]

i want to implement it in similar manner as in foxpro same file when exporting to dbf becomes character data type and and prints very well in foxpro by same method without any disturbance for example cheque no will always occupy 10 characters event if its empty data but i cant do it in vba programming.vba provide space not according to data structure but according to data

[zmbd]

Another solution

Another solution is to read the table field properties and then append spaces.

I have a simple table "tbl_customprop erty"
and a few fields in it

Sub Listtblformat()
Dim zdbs As DAO.Database, ztbldef As DAO.TableDef, zfld As DAO.Field
'
'setup for the database and look at just one table
Set zdbs = CurrentDb
Set ztbldef = zdbs.TableDefs("tbl_customproperty")
'
'get the information for the table fields
'and print it to the debug window
For Each zfld In ztbldef.Fields
    Debug.Print "Field name ::: Field Size ::: Field Type"
    Debug.Print zfld.Name & " ::: " & zfld.Size & " ::: " & zfld.Type
    Debug.Print "field attributes::: " & zfld.Attributes
    Debug.Print "-----"
Next
'
'clean up
Set zdbs = Nothing
Set ztbldef = Nothing
'
End Sub
'
'In the immediate window the following will print:
'
'Field name ::: Field Size ::: Field Type
'CustomPropertyDB ::: 50 ::: 10
'field attributes::: 2
'-----
'Field name ::: Field Size ::: Field Type
'CustomPropertyName ::: 50 ::: 10
'field attributes::: 2
'-----
'Field name ::: Field Size ::: Field Type
'CustomPropertyType ::: 50 ::: 10
'field attributes::: 2
'-----
'Field name ::: Field Size ::: Field Type
'CustomPropertyValue ::: 50 ::: 10
'field attributes::: 2
'-----

they're text fields and they are set for 50 characters long.... you should now be able to read this information... store it (array, enumeration, custom class whatever)... read your data for the fields in question... compare the length and either cut it down or append/prepend the correct number of spaces to make the length...

I'll leave that coding for you to do.

-z

[kkshansid]

if its that much difficult in vba and require so many lines of code then i will prefer to print it through foxpro

[zmbd]

there really isn't that many lines of code there... lines 23 thru 40 were just the output to the debugger window... there's less actual code in #7 (like 6 lines) that do the work then you posted in #3.

FoxPro... which version? Remember the older databases used fixed field files and that may be why you finding this easier in FP...

-z

[kkshansid]

ITS FOXPRO 2.6 . I dont need to export once but to make text file and write on this text file in desired format through vba program with every field length according to data structure.if i do every thing in access by sql query then i just also want to print it also through vba should i always use foxpro for printing purpose?

[twinnyfo]

kkshansid,

I think you also want to make sure that if a field has fewer than the maximum number of characters that it fills the field correctly for alligning the columns, yes?

Wouldn't you need to find the length of each individual field and add spaces as necessary for that? It sounds a bit involved just to be able to export to a text file. However, if you use those text files often, then it is worth the effort (and the extra lines of code that may be required).

Another challenge of communicating between applications...

[zmbd]

Twinny & kkshansid:

- The code I posted in #7 reads the table field widths.
- Also in #7 I suggested the pre/post-append
- Code in #7 can also be used to create the Schema.ini file for used with the method in suggested in #5 by outputting to a text file the information as given in the second link as posted in #5.
- FoxPro can basically ODBC into the Access DB depending on version... which from kkshansid, is able to do.
- At this point kkshansid needs to decide what is the best way for his application to work. Several solutions have been offered. Unfourtunately I don't have the luxury/time to write the application for kkshansid as I do this inbetween my lab tests and other coding responibilities .

-z