0

I am using a macro that reads every excel file in one folder and subfolders and refreshes it by opening the file and closing it with 'save changes' attribute as True. The problem is that VBA doesn't read non-english letters correctly and it causes error when trying to save the spreadsheet. My region settings in Windows control panel are correct. When I try to use the beta option of using Unicode UTF-8 for every language it works but that causes a lot of other programs I use to display some weird characters. The language I try to incorporate is polish. Any idea what to do?

Sub RefreshExcelDocs()

Const startFolder As String = "C:\Users\Patryk\Downloads\Przykład óżęą\Folder\"
Dim file As Variant, wb As Excel.Workbook

For Each file In Filter(Split(CreateObject("WScript.Shell").Exec("CMD /C DIR """ & startFolder & "*.xl*"" /S /B /A:-D").StdOut.ReadAll, vbCrLf), ".")
    Set wb = Workbooks.Open(file)
    wb.Close SaveChanges:=True '
    Set wb = Nothing
Next

End Sub
Jorhanc
  • 310
  • 2
  • 13
  • I think the issue is in `WScript.Shell` try using the `FileSystemObject` see [Iterating through files in folder via FileSystemObject](https://stackoverflow.com/questions/43367671/vba-excel-iterating-through-files-in-folder-via-filesystemobject) or the [Dir function](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dir-function). See [Loop through files in a folder using VBA?](https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba) – Pᴇʜ Jun 30 '21 at 11:33
  • 2
    VBA reads non-English letters correctly. If, however, you try to `Debug.Print` them or display them in a `MsgBox` they don't display correctly. But the underlying unicode/ascii character is correct. To debug you can always display their unicode using `AscW` function. – Super Symmetry Jun 30 '21 at 11:40
  • 1
    "*... it causes error when trying to save the spreadsheet*": What are the code and description of the error and exactly which line does it occur at? – Super Symmetry Jun 30 '21 at 11:41

0 Answers0