How to write a txt file based on table info? (1 Viewer)

GKite · Mar 27, 2002

I need to be able to write a text file based on data from a table/query. So for instance if I have 3 table entries, I need to write something like this:

Table Entries:
45
12453
332

Text file to write:
[some header info]
C:\softjuke\files\45.mp3
C:\softjuke\files\12453.mp3
C:\softjuke\files\332.mp3

cpod · Mar 27, 2002

Something like this should do it:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("table1")
Dim fs, TextFile
Set fs = CreateObject("Scripting.FileSystemObject")
Set TextFile = fs.CreateTextFile("c:\testfile.txt", True)
Do Until rst.EOF = True
TextFile.WriteLine ("C:\softjuke\files\" & rst!num & "mp3")
rst.MoveNext
Loop
TextFile.Close

GKite · Mar 27, 2002

OK Thanks! However, I'm having a little trouble with this. I get an error on the very first line that reads 'user defined type not defined'. This is the exact code I'm using:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("qryPlaylist_ToTextFile")
Dim fs, TextFile
Set fs = CreateObject("Scripting.FileSystemObject")
Set TextFile = fs.CreateTextFile("c:\softjuke\playlist.m3u", True)

TextFile.writeline ("#EXTM3U")
Do Until rst.EOF = True
TextFile.writeline ("C:\softjuke\files\" & rst!songid & "mp3")
rst.MoveNext
Loop

TextFile.Close

GKite · Mar 27, 2002

Thanks CPOD! I REM'd out that first line that was giving me trouble and now it works perfect! Isn't that strange? If you want a copy of the finished jukebox program email me and i'll send it to you when its complete. This was my last major hurdle

Alexita · Aug 14, 2008

That worked great

I just wanted to thank you for this information, I was desesperately looking for this code sample to apply in a form in access that has to be done by today.

drb555 · Jul 13, 2013

cpod said:
Something like this should do it:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("table1")
Dim fs, TextFile
Set fs = CreateObject("Scripting.FileSystemObject")
Set TextFile = fs.CreateTextFile("c:\testfile.txt", True)
Do Until rst.EOF = True
TextFile.WriteLine ("C:\softjuke\files\" & rst!num & "mp3")
rst.MoveNext
Loop
TextFile.Close

is there a way to output the textfile part as single string instead of each record on a new line?

ie
aaaaaa bbbbbbb cccccc

in stead of

aaaaaa
bbbbb
cccccc

nanscombe · Jul 13, 2013

It looks like the Write method, rather than WriteLine, should achieve this as it does not add a NewLine character at the end.

However think about how you are going to separate the data, especially if it has spaces in. You may wish to wrap the entries in double quotes.

So could try...

Code:

' Chr(34) gives a " (double quote symbol)
TextFile.Write (Chr(34) & "C:\softjuke\files\" & rst!num & "mp3" & Chr(34) & " ")

"aaaa" "bbbb" "cccc" "dd ee"

How to write a txt file based on table info? (1 Viewer)

GKite

Registered User.

cpod

Registered User.

GKite

Registered User.

GKite

Registered User.

Alexita

New member

drb555

Registered User.

nanscombe

Registered User.

Similar threads

Users who are viewing this thread