Export as Text problem

[rnegde]

I have a command button that builds a text file based on a table. Like this:

DoCmd.TransferText acExportDelim, "MySpecs", "Mytable", "F:\Mytable.txt", True

That works fine, my problem is that the program that I have using this file for import into has to have a line of dashes between the field names and the data. Like this:

"Field1","Field2","Field3", etc.
----------------------
"Data1","Data2","Data3", etc.

Does anyone know how I could code this in so that the line of dashes are written into the text file? Any help would be appreciated!!!

 

[euskadi]

I don't know of a way to directly get the output function to do what you want.

Three things I'd try...

First I'd find out if the program your importing into is a file type that's already supported by Access, and then use those specs.

Second, I'd find out if the importing program really needs dashes, or if it just needs a 1 row buffer with any content to separate the field names from the next. If so then I'd tackle the problem by creating second table with the same number of fields and only one row with some bogus data that will be ignored in import... then do a union query of Table1 and Table2, and then export that.

Third, (if 1 and 2 failed) I'd ditch the transfertext method and write a little routine to output to a text file and throw in the field names and a separater on row 1 and row2.

Good luck!

 

[rnegde]

Actually, "write a little routine" is just what I would like to do, just don't quite know how. I can write code that will manipulate data, populate Excel and Word documents, send email, and some other stuff, but I've never tried to write a text file from code. If you know of somewhere I could go look at some code that kinda does that I could probably figure out how to make it work for me. Thanks for the help!

 

[euskadi]

From the visual basic module, search for "print" or "write" in Help.

You're looking for "print #" or "write #" statements where you open a file (temorarily numbered #1 or #2 or whatever) and you write lines of text to the file. If you've done those other projects, you should be able to quickly figure it out from the help example.

 

[rnegde]

Thanks euskadi, I do believe that will work! I appreciate the help!

 

[jonscott8]

Try:

DoCmd.TransferText acExportDelim, "MySpecs", "Mytable", "F:\Mytable.txt", True
Set oFSO = CreateObject("Scripting.FileSystemObject&quot
Set oFileIn = oFSO.OpenTextFile("F:\Mytable.txt", 1)
Set oFileOut = oFSO.CreateTextFile("F:\Mytable2.txt",2)

sHold = oFileIn.ReadLine
oFileOut.WriteLine sHold
oFileOut.WriteLine String(len(sHold),"-&quot
oFileOut.Write oFileIn.ReadAll
oFileIn.Close
oFileOut.Close Jon Hawkins