How to keep Greek letters in VBA processing

[swpps]

In MS Access have a table with attribute values (in my case star designations such as ψ Ori) that can contain all Greek letters.

In VBA I query the table with a recordset, but the Greek letters are changed into their Latin counterparts or into a question mark.

The problem is that I need the original Greek letters in my further processing.

How to do that?

 

[arnelgp]

how was that?
whatever is saved in your table is "as-is", they don't get altered or anything.
maybe you can "change the system locale" of your pc to greek.

control panel->region->Administrative (tab)->change system locale.

 

[swpps]

Thanks.

But I'm not Greek so I keep my current settings....

Maybe I have to be more clear.

Let's stick to that ψ Ori. This is what the table says:

Id	Designation
523​	ψ Ori

Then VBA:

Set rst = CurrentDb.OpenRecordset("SELECT Designation FROM [tbl Stars] where ID=523")

The value of rst.Designation is now "? Ori"

But I really need the value ψ Ori in my further VBA processing!

 

[arnelgp]

don't worry the ψ is still there, only unable to display because of the Language.
so your code will still work.

 

[swpps]

In my case it doesn't.

I use the value rst.Designation as part of the name I give to an audio file (with my comments about the observation of this double star with my telescope). Apart from that ? is not accepted as part of a file name, I need the original ψ.

 

[arnelgp]

as ive said the "Greek" letter is in there, it is just your "Language" cannot show it (use the method i show you in post #2, it will not change the look of anything).

see this demo. Table1 has Greek letter on [greek] field.
no open Module1 and run t() sub (change the folder to what you have).
now go to that folder on explorer, and surprise the "greek" letter is there on the filename.

 

[swpps]

Thanks, much appreciated.

I've run your example, and true, the ψ is in the file name.

But I'm still not there.

What I do is not create (and name) a new file (as in your example), but rename an existing audio file with the Name ... As ... statement.

VBA: Name <oldname>&".mp3" As <prefix>&rst.Designation&".mp3"

This way with rst.Designation = 'ψ Ori' the new name is not accepted (ψ becomes '?') and in the case of e.g. the star α UMi the α becomes a.

So, what to do?

 

[MajP]

Here are some lenghty threads that may be relevant.
In this the user is trying to import files with unicode characters in the name. Although Windows excepts these, neither Acess SQL or vba string functions do

FileSystemObject error when the file names has special letters

Hello All I'm not bad on Microsoft Access but I am quite new on VBA codes. I spent almost 24 hours to get solution below. I have several csv files I am importing them to table with bellowing code: Dim FSO As Object, objFolder As Object, objFile As Object Set FSO =...

www.access-programmers.co.uk

The solution was to strip these characters before importing and rename the file

In a similar instance the OP was spanning the file directory and logging files with unicode characters. Windows excepts these characters, and you can store them in the DB as you show. Again you cannot do SQL on these characters, so you cannot search for them.
In this the

Solved - Multiple search engine with dropdowns

See demo. I used module level variables, it was the simplest but maybe not the cleanest way.

www.access-programmers.co.uk

So some of the suggestions use a replace function for other characters not handled by SQL.

However, I am confused on your issue. Because what the above shows was that the file name can handle these characters and these characters can also be stored in the table. You are not doing SQL or access string searches on these. So I would think it would work and there should be no issue with renaming using a unicode character.

Can you provide your renaming code?

 

[MajP]

I tested this an it works no problem. I put greek letters in a table. I read the letters and modified the existing CSV files with the concatenated letter from the table. I started with Cat.csv and Dog.csv and created the below files.

DEMO CODE

Public Sub RenameFile()
 Dim FSO As Object
 Dim objFolder As Object
 Dim objFile As Object
 Dim OldName As String
 Dim NewName As String
 Dim FolderPath As String
 Dim I As Integer
 Dim j As Integer
 Set FSO = CreateObject("Scripting.FileSystemObject")
 
  Dim fileName As String
  fileName = DLookup("Greek", "table2")
On Error GoTo errlog
'modify your path
FolderPath = CurrentProject.Path & "\" & "SampleCSVFiles"
Set objFolder = FSO.GetFolder(FolderPath)
For Each objFile In objFolder.Files
  If Right(objFile.Name, 3) = "csv" Then
    OldName = objFile.Name
    
    j = j + 1
    NewName = Left(OldName, Len(OldName) - 4) & "_" & fileName & "_" & j & ".csv"
   'Debug.Print OldName & " " & NewName
    'Choose to copy
    FSO.CopyFile FolderPath & "\" & OldName, FolderPath & "\" & NewName
    'Or choose to rename
    'FSO.MoveFile FolderPath & "\" & OldName, FolderPath & "\" & NewName
    ' FSO.DeleteFile FolderPath & "\" & NewName
   Me.Text2 = Me.Text2 & NewName
  End If
Next objFile
Exit Sub
errlog:
  Debug.Print Err.Number & " " & Err.Description & " new name: " & NewName
  Resume Next
Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing
End Sub

 

[arnelgp]

instead of renaming, you can try to Copy existing file to New filename.
then Kill (delete) the original file.

 

[MajP]

instead of renaming, you can try to Copy existing file to New filename.
then Kill (delete) the original file.

In fact that is what you have to do and that is what I demoed. I just did not kill the original for demonstration purposes. As far as I know using the FSO there is no rename capability. You have to move to new location (which is basically a copy with a new name).

 

[swpps]

Thanks the both of you.

I used only 'FSO.MoveFile FolderPath & "\" & OldName, FolderPath & "\" & NewName' from MajP's post and it works. The Greek letters are kept.