FileSystemObject error when the file names has special letters (1 Viewer)

Ocicek

New member
Local time
Today, 16:32
Joined
Jan 19, 2021
Messages
25
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:

Code:
Dim FSO As Object, objFolder As Object, objFile As Object

Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = FSO.GetFolder(Me.MB_Klasor_Konumu)


For Each objFile In objFolder.Files
  If Right(objFile.Name, 3) = "csv" Then
 
    DoCmd.TransferText acImportDelim, "MB3", "MB_Sheet_Ham", objFolder & "\" & objFile.Name, False

  End If
  i = i
Next objFile

Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing

It does work but doesn't work with if files has some special letter like "İ, Ş, Ğ, Ö,Ü, Ç"

I don't know what I should do?

I research for rename files before import but I couldn't found the solution, maybe there is a else trick ?

Is there someone to help me about?

Thank you in advance.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:32
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

To rename a file, you can use the Name As statement. For example:
Code:
Name OldName.ext As NewName.ext
If you want to use FSO to do it, you could try something like:
Code:
objFile.Name = NewName
Hope that helps...
 

Ocicek

New member
Local time
Today, 16:32
Joined
Jan 19, 2021
Messages
25
Hi. Welcome to AWF!

To rename a file, you can use the Name As statement. For example:
Code:
Name OldName.ext As NewName.ext
If you want to use FSO to do it, you could try something like:
Code:
objFile.Name = NewName
Hope that helps...

Thank you the DBguy, I saw that code but don't know to how to use in loop. It's enough to copy these files into a temp folder, rename them file1.csv, file2.csv etc.. after importing delete them.

As I said I am not good to solve VB codes 🙏
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:32
Joined
May 21, 2018
Messages
8,463
Do something like this. for the newname
Code:
Private Function ReplaceInternationalCharacters(ByVal strText As String) As String
   ' "A", "[AÁÀÂÄaá]")
   ' "E", "[EÉÈÊËeé]")
   ' "I", "[IÍÌÎÏií]")
   ' "O", "[OÓÒÔÖ0oóøØ]")
   ' "U", "[UÚÙÛÜuú]")
   ' "N", "[NnñÑ]")
   ' "C", "[CcçÇ]")
   'I, S, G, Ö,Ü, Ç
   
    ReplaceInternationalCharacters = Replace(strText, "Ö", "O")
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Ü", "U")
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Ç", "C")
    'add all cases may need upper and lower
End Function


Public Sub Testit()
  Debug.Print ReplaceInternationalCharacters("AbcÖÜÇ")
End Sub

So my result is
AbcOUC. You
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:32
Joined
May 21, 2018
Messages
8,463
,replaceInsternationalCharacters(objFile.Name), in your transfer text
 

Ocicek

New member
Local time
Today, 16:32
Joined
Jan 19, 2021
Messages
25
,replaceInsternationalCharacters(objFile.Name), in your transfer text
Dear Majp I think firstly I have to create module Replace International characters than I'll add this line to my transfer text. Is it right?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:32
Joined
May 21, 2018
Messages
8,463
Yes create a standard vba module if you need to use throughout the database or you can place that code in the same location as your other code, but that may not be available from other places in the application. Paste that function. You can add more lines below the other code for other conditions.
ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Old", "New")
 

Ocicek

New member
Local time
Today, 16:32
Joined
Jan 19, 2021
Messages
25
Yes create a standard vba module if you need to use throughout the database or you can place that code in the same location as your other code, but that may not be available from other places in the application. Paste that function. You can add more lines below the other code for other conditions.
ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Old", "New")
I added more lines below the code for other conditions and I saved that as module. Bu I didn't get the paste where I add the ",replaceInsternationalCharacters(objFile.Name) on my transfer text" :rolleyes:
 

Attachments

  • ReplaceInternational Chars Code.png
    ReplaceInternational Chars Code.png
    89.9 KB · Views: 522

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:32
Joined
May 21, 2018
Messages
8,463
Code:
Dim FSO As Object, objFolder As Object, objFile As Object
Dim NewName as String

Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = FSO.GetFolder(Me.MB_Klasor_Konumu)
For Each objFile In objFolder.Files
  If Right(objFile.Name, 3) = "csv" Then

   NewName = replaceInternationalCharacters(ObjFile.name)
    DoCmd.TransferText acImportDelim, "MB3", "MB_Sheet_Ham", objFolder & "\" & NewName, False

  End If
  i = i
Next objFile

Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing

Do the folders also have special characters?
 

Ocicek

New member
Local time
Today, 16:32
Joined
Jan 19, 2021
Messages
25
Dear MajP it doesn't work when I save it as module, I got sub or function not defined error. Than I did paste that on my same page with my code, than I saw the code sentence changed the reference but filename was still same. What was my mistake? You can see the pictures attached what happened detailed.
 

Attachments

  • First Attempt.png
    First Attempt.png
    139.3 KB · Views: 468
  • Secon Attempt.png
    Secon Attempt.png
    141 KB · Views: 347
  • Second Attempt Result.png
    Second Attempt Result.png
    258.7 KB · Views: 381

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:32
Joined
May 21, 2018
Messages
8,463
Put the function in a standard module and make it public. Sorry I made it private when I copied. You have two modules already where you can place it or make a new one.
 

Ocicek

New member
Local time
Today, 16:32
Joined
Jan 19, 2021
Messages
25
I removed that from main code , I cleaned all modules and add new one, changed the function as Public Function, and saved as with your sign :)

But still that is not changing the file nime, (Runtime 3011 error) 🙈
 

Attachments

  • Ekran Resmi 2021-01-19 22.29.04.png
    Ekran Resmi 2021-01-19 22.29.04.png
    71.6 KB · Views: 379

Ocicek

New member
Local time
Today, 16:32
Joined
Jan 19, 2021
Messages
25
Put the function in a standard module and make it public. Sorry I made it private when I copied. You have two modules already where you can place it or make a new one.
I'm attaching my project and some sample files if that helps to understand what's happening exactly.

By the way I am thankful for your time to concern my case.
 

Attachments

  • MajP.zip
    2.1 MB · Views: 603

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:32
Joined
May 21, 2018
Messages
8,463
I apologize again. You are doing an import and not an export. What you sent will help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:32
Joined
Oct 29, 2018
Messages
21,357
Thank you the DBguy, I saw that code but don't know to how to use in loop. It's enough to copy these files into a temp folder, rename them file1.csv, file2.csv etc.. after importing delete them.

As I said I am not good to solve VB codes 🙏
Hi. Just as FYI, I am not ignoring you. I think you're in good hands already and don't want to confuse the issue with my suggestions. Good luck!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:32
Joined
May 21, 2018
Messages
8,463
Can the files be permanently renamed or you need a copy? I see what DBguy was saying now? I will demo with a rename.
 

Ocicek

New member
Local time
Today, 16:32
Joined
Jan 19, 2021
Messages
25
I apologize again. You are doing an import and not an export. What you sent will help.
I sent the simple part of my database and my project included the sample csv files.
So what we should do if we are importing files?
 

Ocicek

New member
Local time
Today, 16:32
Joined
Jan 19, 2021
Messages
25
Can the files be permanently renamed or you need a copy? I see what DBguy was saying now? I will demo with a rename.
It doesn’t matter. You can prefer whichever way is easy. If we copy files we should delete after import.
 

Users who are viewing this thread

Top Bottom