How to write a txt file based on table info?

[GKite]

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]

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]

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]

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]

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]

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]

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

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

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