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
Bookmarks