Hi all,

I am trying to open several .xls files in a folder one-by-one, pull data out of them and close them before moving to the next. I have coded this and it works perfectly for MOST records. My approach was to use the Dir() to define a string variable with the record's filepath + name.

However, some of the record names contain α (alpha) symbols - which don't get captured properly in the string variable in my code (VBA limitation!)! As a result when the code tries to locate the file by using the variable's string, the alphas have become "a", so I get errors telling my the file I'm looking for is out of range!

If I could change the problematic filename to get around this problem that would be a neat solution, but I can't manage to refer to the file I want to change the name of because of the alpha symbols!!!


My code (you'll need to create a folder with a .xls file inside called α (alpha) to replicate this issue:

Option Explicit
Dim PulledData As String
Dim wbDataCollect As Workbook
Dim x As Integer
Dim Y As Integer
Dim Check As Integer
Dim PulledDataCAS As String
Dim PulledDataRECNAME As String
Dim PulledDataCHEMNAME As String
Dim PulledDataTYPICALUSES As String
Dim PulledDataLegislation As String

Public Sub test()
Application.ScreenUpdating = False
Dim MyDir As String
Dim fn As String
Dim wb As Workbook

'My variables
Set wbDataCollect = ActiveWorkbook

x = Range("CAS").Row
'////

Check = MsgBox("Running ""Get data"" will overwrite existing data in this table. Continue?", vbYesNoCancel)
If Check = vbCancel Or Check = vbNo Then
    Exit Sub
End If

MsgBox "Select the folder containing your exporter Excel files."

Application.FileDialog(msoFileDialogFolderPicker).Show

MyDir = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
If MyDir = "" Then GoTo FolderSelect_Error


fn = Dir(MyDir & "*.xls")

Workbooks((Dir(MyDir & "*.xls"))).SaveAs Filename:="Test"

Do While fn <> ""
     
     Workbooks.Open (MyDir & fn)
     Set wb = ActiveWorkbook
     
  'some code
 

     
     wb.Close
     
     fn = Dir()
   
'some code to paste the result into my workbook

     End With
     
Loop


FolderSelect_Error:
MsgBox "Folder location not recognised. Consolidation not completed."
Application.ScreenUpdating = True
Exit Sub