Conversion of special characters

[pcc]

I have an Excel tool that plays my mp3 files (like a jukebox, but far more sophisticated).
It currently relies on an Access database for its function, but I am in the process of
converting it so that it does not rely on Access. To this end, I have converted all of the
Access tables into text files (.txt extension). The tool then reads data from text files
rather than from Access tables. All tables have therefore been converted to text files using the
Print statement in VB eg
''''''''''''''''

Open "C:\users\peter\data\text_files\test.txt" For Output As #ff
Set cnt = CreateObject("ADODB.Connection")
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strdb & ";" & _
"Jet OLEDB:Database Password=" & pw & ";"

strSql = "Select * from [thetable] order by ID;"
rst.Open strSql, cnt, adOpenStatic
rst.MoveFirst
While Not rst.EOF
mytext = vbNullString
For y = 1 To 11 ' number of fields in this table
mytext = mytext & Trim(rst(y - 1)) & "|"
Next y
Print #ff, Left(mytext, Len(mytext) - 1)
rst.MoveNext
Wend
rst.Close
Close #ff

''''''''''''''
This creates records in the text file such as:
C:\Users\Peter\mp3\RPMellow\Alison Krauss\ðOh, Atlanta.mp3|

However, during this process, some special characters get changed. This means that when the file is read
back into the Excel tool, the filenames have effectively changed, and the tool doesn't work as intended.
I can overcome this by using a 'Find and replace' routine when reading the files back, but I would like to
know if there is a way of preserving the files' correct names and avoiding the special character conversion

This is how it is stored in Access:
C:\Users\Peter\mp3\RPMellow\Alison Krauss\¥Oh, Atlanta.mp3
C:\Users\Peter\mp3\RPMellow\Alison Krauss & Gillian Welch\©I'll Fly Away.mp3


This is how it looks in the text file (note special characters have been altered)
C:\Users\Peter\mp3\RPMellow\Alison Krauss\ðOh, Atlanta.mp3|
C:\Users\Peter\mp3\RPMellow\Alison Krauss & Gillian Welch\½I'll Fly Away.mp3|

This is how it looks when read back into Excel
C:\Users\Peter\mp3\RPMellow\Alison Krauss\ðOh, Atlanta.mp3
C:\Users\Peter\mp3\RPMellow\Alison Krauss & Gillian Welch\½I'll Fly Away.mp3


(NB the special characters in the filenames are used to trigger certain functionality in the Excel tool)




Can anyone advise on how to retain the filenames without conversion of those characters?

 

[JEC]

Maybe...
I miss some code though because you posted not all of it

Sub test()
 Dim cnt As Object, strSql, y As Long
 
 Set cnt = CreateObject("ADODB.Connection")
 cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strdb & ";" & "Jet OLEDB:Database Password=" & pw & ";"
 strSql = "Select * from [thetable] order by ID;"
 rst.Open strSql, cnt, adOpenStatic
 
 With CreateObject("scripting.filesystemobject").createtextfile("C:\users\peter\data\text_files\test.txt", True, True)
   While Not rst.EOF
     mytext = ""
     For y = 1 To 11 ' number of fields in this table
       mytext = mytext & Trim(rst(y - 1)) & "|"
     Next
     .writeline Left(mytext, Len(mytext) - 1)
     rst.MoveNext
   Wend
 End With
 rst.Close
End Sub

 

[pcc]

Thanks for prompt reply. I will try this and repost the results ( tomorrow).
Regards

 

[JEC]

While reading the data into Excel, you should open the textfile like

.OpenTextFile("C:\users\peter\data\text_files\test.txt", 2, True, -1)

 

[pcc]

Thanks for your reply. I will try this and report back.(tomorrow)

 

[pcc]

I have used this code and it works perfectly. Thank you so much!

 

[JEC]

You’re welcome! Cheers