Unable to open file

ryan8200

Active Member
Joined
Aug 21, 2011
Messages
355
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:
VBA Code:
[/
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]
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
3,812
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Can you upload the file and provide a link to it?
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,211
Office Version
  1. 2016
Platform
  1. Windows
I think due to the VBA environment does not support Unicode. Try this

VBA Code:
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

Active Member
Joined
Aug 21, 2011
Messages
355
I think due to the VBA environment does not support Unicode. Try this

VBA Code:
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

Well-known Member
Joined
Nov 26, 2020
Messages
2,211
Office Version
  1. 2016
Platform
  1. Windows
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 ?
 

Forum statistics

Threads
1,175,957
Messages
5,900,534
Members
434,835
Latest member
cmenconi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top