Unable to open file

[ryan8200]

I'm not able to open file with filename in Chinese character. It hit runtime error 1004 with following messages:

Sorry, we couldn't find C:\Users\My\Downloads\Data\?????.csv. Is it possible, it was moved, renamed or deleted". I have Chinese font been installed in both Window and Excel. What is the possible root cause for this problem ?

Here is the code I'm using:

[/
Path = "C:\Users\My\Downloads\Data\"
Filename = Dir(Path & "*.csv")

  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
     Workbooks(Filename).Close
     Filename = Dir()
  Loop
CODE]

 

[johnnyL]

Can you upload the file and provide a link to it?

 

[Zot]

I think due to the VBA environment does not support Unicode. Try this

Sub Test()

Dim wb As Workbook
Dim FSO As Object, Folder As Object, File As Object

Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder("C:\Users\My\Downloads\Data")

For Each File In Folder.Files
    Set wb = Workbooks.Open(Filename:=File, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    For Each Sheet In wb.Sheets
        Sheet.Copy After:=ThisWorkbook.Sheets(1)
    Next
    wb.Close False
Next

End Sub

 

[ryan8200]

I think due to the VBA environment does not support Unicode. Try this

Sub Test()

Dim wb As Workbook
Dim FSO As Object, Folder As Object, File As Object

Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder("C:\Users\My\Downloads\Data")

For Each File In Folder.Files
    Set wb = Workbooks.Open(Filename:=File, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    For Each Sheet In wb.Sheets
        Sheet.Copy After:=ThisWorkbook.Sheets(1)
    Next
    wb.Close False
Next

End Sub

Hi Zot, thanks. It works. The above code will read all files within the specified folder regardless of file type ? The solution is to create File System Object to handle unicode filename ?

 

[Zot]

Hi Zot, thanks. It works. The above code will read all files within the specified folder regardless of file type ? The solution is to create File System Object to handle unicode filename ?

Yess, all files in folder. I guess you can just use wildcards to filter that out. Using the FSO you are going through Windows environment which able to handle unicode.

Ohh.. I should have add Application.ScreenUpdating = False to prevent flashing and make it run faster ?