How to keep Greek letters in VBA processing (1 Viewer)

swpps

New member
Local time
Today, 13:39
Joined
May 7, 2020
Messages
7
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

..forever waiting... waiting for jellybean!
Local time
Today, 20:39
Joined
May 7, 2009
Messages
19,175
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

New member
Local time
Today, 13:39
Joined
May 7, 2020
Messages
7
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:


IdDesignation
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

..forever waiting... waiting for jellybean!
Local time
Today, 20:39
Joined
May 7, 2009
Messages
19,175
don't worry the ψ is still there, only unable to display because of the Language.
so your code will still work.
 

swpps

New member
Local time
Today, 13:39
Joined
May 7, 2020
Messages
7
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

..forever waiting... waiting for jellybean!
Local time
Today, 20:39
Joined
May 7, 2009
Messages
19,175
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.
 

Attachments

  • checkOri.accdb
    632 KB · Views: 105

swpps

New member
Local time
Today, 13:39
Joined
May 7, 2020
Messages
7
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

You've got your good things, and you've got mine.
Local time
Today, 08:39
Joined
May 21, 2018
Messages
8,463
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
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

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

You've got your good things, and you've got mine.
Local time
Today, 08:39
Joined
May 21, 2018
Messages
8,463
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
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
Greek.jpg
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:39
Joined
May 7, 2009
Messages
19,175
instead of renaming, you can try to Copy existing file to New filename.
then Kill (delete) the original file.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:39
Joined
May 21, 2018
Messages
8,463
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

New member
Local time
Today, 13:39
Joined
May 7, 2020
Messages
7
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.
 

Users who are viewing this thread

Top Bottom