By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,654 Members | 1,842 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,654 IT Pros & Developers. It's quick & easy.

data not fixed length vba programming on text file

P: 232
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
Aug 8 '12 #1
Share this Question
Share on Google+
11 Replies

Expert Mod 2.5K+
P: 3,282

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....
Aug 8 '12 #2

P: 232
Expand|Select|Wrap|Line Numbers
  1. Sub WriteToATextFile()
  2. 'first set a string which contains the path to the file you want to create.
  3. 'this example creates one and stores it in the root directory
  4. MyFile = "c:\" & "EXPENDITURE.txt"
  5. 'set and open file for output
  6. fnum = FreeFile()
  7. Open MyFile For Output As fnum
  9. Dim r1 As ADODB.Recordset
  10. Set r1 = New ADODB.Recordset
  11. r1.ActiveConnection = CurrentProject.Connection
  12. r1.CursorType = adOpenDynamic
  13. r1.LockType = adLockOptimistic
  14. r1.Open ("SELECT * from  FIN_MAY12")
  15. If Not (r1.EOF And r1.BOF) Then
  16.     Do While Not r1.EOF
  18.          Print #fnum, r1("chk_Date") & " |" & r1("chk_no") & " |" & r1("particulars") & " |" & r1("debit") & " |" & Space(5) & r1("credit") & " |" & Space(5)
  19.          Print #fnum, String(50, "-")
  20.          Chr (12)
  21.          r1.MoveNext
  22.     Loop
  23. End If
  24. Close #fnum
  25. End Sub
i want to fill up empty spaces with empty spaces so that formatting will not get disturbed.
Aug 9 '12 #3

Expert Mod 5K+
P: 5,397
Are you tring to do this:
Aug 9 '12 #4

Expert Mod 5K+
P: 5,397
Ahh.. I see you posted just before I hit enter...
try the following:
Use the transfer type as "acExportFixed"

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


[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
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.)

Aug 9 '12 #5

P: 232
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
Aug 9 '12 #6

Expert Mod 5K+
P: 5,397
Another solution is to read the table field properties and then append spaces.

I have a simple table "tbl_customproperty"
and a few fields in it

Expand|Select|Wrap|Line Numbers
  1. Sub Listtblformat()
  2. Dim zdbs As DAO.Database, ztbldef As DAO.TableDef, zfld As DAO.Field
  3. '
  4. 'setup for the database and look at just one table
  5. Set zdbs = CurrentDb
  6. Set ztbldef = zdbs.TableDefs("tbl_customproperty")
  7. '
  8. 'get the information for the table fields
  9. 'and print it to the debug window
  10. For Each zfld In ztbldef.Fields
  11.     Debug.Print "Field name ::: Field Size ::: Field Type"
  12.     Debug.Print zfld.Name & " ::: " & zfld.Size & " ::: " & zfld.Type
  13.     Debug.Print "field attributes::: " & zfld.Attributes
  14.     Debug.Print "-----"
  15. Next
  16. '
  17. 'clean up
  18. Set zdbs = Nothing
  19. Set ztbldef = Nothing
  20. '
  21. End Sub
  22. '
  23. 'In the immediate window the following will print:
  24. '
  25. 'Field name ::: Field Size ::: Field Type
  26. 'CustomPropertyDB ::: 50 ::: 10
  27. 'field attributes::: 2
  28. '-----
  29. 'Field name ::: Field Size ::: Field Type
  30. 'CustomPropertyName ::: 50 ::: 10
  31. 'field attributes::: 2
  32. '-----
  33. 'Field name ::: Field Size ::: Field Type
  34. 'CustomPropertyType ::: 50 ::: 10
  35. 'field attributes::: 2
  36. '-----
  37. 'Field name ::: Field Size ::: Field Type
  38. 'CustomPropertyValue ::: 50 ::: 10
  39. 'field attributes::: 2
  40. '-----
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.

Aug 9 '12 #7

P: 232
if its that much difficult in vba and require so many lines of code then i will prefer to print it through foxpro
Aug 9 '12 #8

Expert Mod 5K+
P: 5,397
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...

Aug 9 '12 #9

P: 232
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?
Aug 9 '12 #10

Expert Mod 2.5K+
P: 3,282

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...
Aug 9 '12 #11

Expert Mod 5K+
P: 5,397
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.

Aug 9 '12 #12

Post your reply

Sign in to post your reply or Sign up for a free account.