Open text file in macro

[wwrightchess]

I am running the same process over a number of files. The first one opens and processes correctly but the second one fails.
I can open the file manually but in the macro it says it failed.
Error is "Method 'OpenText' of object 'Workbooks' failed.
I think the problem is I need to grant access to the folder for some reason but I do not know how to do that.
Here is the code (fullname is a string containing the path and name of the file)

        Workbooks.OpenText filename:=fullname, _
            Origin:=xlMacintosh, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
            Array(Array(0, 1), Array(15, 1), Array(30, 1), Array(45, 1), Array(60, 1), Array(75, 1), _
            Array(90, 1), Array(105, 1), Array(120, 1), Array(135, 1), Array(150, 1), Array(165, 1), _
            Array(180, 1)), TrailingMinusNumbers:=True

 

[Joe4]

Welcome to the Board!

Are all the text file formateed EXACTLY the same?

If you change the order, so you try opening the second file (the one that was erroring out) first, does it open that file successfully then?

Are you looping through some list of files?

If so, can you post the rest of your code, and the list of file names to open?

 

[wwrightchess]

The files are all generated exactly the same and yes it loops through a list in a separate spreadsheet. The files have the same name, they are just in different directories.
When it ran the first time, it asked for permission to access the directory. After trying different things, it asked for access to the second directory and processed those files. Then it broke trying to access the third directory. Like I mentioned, I thing I just need to grant access to each directory somehow and it will work. Here is the complete macro.
Sub import_files()
'
' import_files Macro
'

'
Application.DisplayAlerts = False
ChDir ("/Volumes/MyPassport/parameter_studies/roughness/GlennIce_nml/outputs")
Dim i As Integer
Dim name As String
Dim fullname As String
Dim path As String
Dim rho As Double
Dim v As Double

path = "/Volumes/MyPassport/parameter_studies/roughness/GlennIce_nml/outputs/"
For i = 2 To 97
Workbooks("large_and_glaze_adjusted_clean.xlsx").Activate
name = Cells(i, 1)
fullname = path & name & "/slice_output.txt"
Workbooks.OpenText filename:=fullname, _
Origin:=xlMacintosh, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(15, 1), Array(30, 1), Array(45, 1), Array(60, 1), Array(75, 1), _
Array(90, 1), Array(105, 1), Array(120, 1), Array(135, 1), Array(150, 1)), _
TrailingMinusNumbers:=True
Sheets("slice_output").Select
Sheets("slice_output").name = "lewice_output"
Sheets.Add After:=ActiveSheet
Sheets("Sheet1").Select
Sheets("Sheet1").name = "GlennICE_output"
fullname = path & name & "/lewiceoutputs/lewice_output.txt"
Workbooks("combine_files_macro.xlsm").Activate
Sheets("Sheet1").Select
Cells(i, 1) = name
Cells(i, 2) = fullname
Workbooks.OpenText filename:=fullname, _
Origin:=xlMacintosh, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(15, 1), Array(30, 1), Array(45, 1), Array(60, 1), Array(75, 1), _
Array(90, 1), Array(105, 1), Array(120, 1), Array(135, 1), Array(150, 1), Array(165, 1), _
Array(180, 1)), TrailingMinusNumbers:=True
Columns("A:M").Select
Selection.Copy
Workbooks("slice_output.txt").Activate
Sheets("GlennICE_output").Select
ActiveSheet.Paste
Sheets("lewice_output").Select
Range("L1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "cp"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=1-(RC[-8]*RC[-8])"
Range("L2:L1516").Select
Selection.FillDown
Range("M1").Select
ActiveCell.FormulaR1C1 = "htc(W/m^2K)"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=RC[-7]*1000"
Range("M2:M1516").Select
Selection.FillDown
Sheets("GlennICE_output").Select
Range("N1").Select
ActiveCell.FormulaR1C1 = "ff"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-6]>0,RC[-4]/RC[-6],1)"
Range("N2:N1516").Select
Selection.FillDown
Range("O1").Select
ActiveCell.FormulaR1C1 = "cp"
Range("O2").Select
Workbooks("large_and_glaze_adjusted_clean.xlsx").Activate
Sheets("conditions_Unlinked_clean").Activate
rho = Cells(i, 19)
v = Cells(i, 6)
Workbooks("slice_output.txt").Activate
ActiveCell.FormulaR1C1 = _
"=(RC[-10]-93000)/(0.5*rho*v^2)"
Range("O2:O1516").Select
Selection.FillDown
fullname = path & name & "/combined_output.xlsx"
ActiveWorkbook.SaveAs filename:= _
fullname _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Workbooks("lewice_output.txt").Activate
ActiveWindow.Close
Next i

End Sub

 

[Joe4]

How exactly is it asking you to grant access to a directory, and what exactly do you do to grant it?

Also, when posting code, please use the Code Tags to make your code much more readable.
See: How to Post Your VBA Code

Thank you.

 

[wwrightchess]

I dialog box comes up and asks me to grant access to the directory. I click the button to grant access. I have not been able to reproduce it.

 

[wwrightchess]

Not sure it is relevant, but all files are on a removable USB drive I bought for additional storage, not on my main drive.

 

[Joe4]

So, it asks you to grant access to the first few, then stops?
I am not sure what kind of security has been placed on the USB drive that is requiring you to do that, but you may want to try to find out, and see if there is a way to grant access to every folder at once, or to remove that requirement entirely.

Without access to that USD drive, I don't know that I am going to be able to provide much more insight/help.

 

[wwrightchess]

I found this page but have not gotten the fix provided to work

Request access to multiple files

Office VBA reference topic

docs.microsoft.com

 

[wwrightchess]

Here is the code I have so far. I get an error on the last line saying there is a type mismatch.
Sub requestFileAccess()
Dim fileAccessGranted As Boolean
Dim filePermissionCandidates
Dim path As String
Dim name As String
Dim fullname As String
Dim fullname2 As String
Dim filelist(192) As String

path = "/Volumes/MyPassport/parameter_studies/roughness/GlennIce_nml/outputs/"
For i = 2 To 97
Workbooks("large_and_glaze_adjusted_clean.xlsx").Activate
name = Cells(i, 1)
fullname = path & name & "/slice_output.txt"
fullname2 = path & name & "/lewiceoutputs/lewice_output.txt"
filelist(i - 1) = fullname
filelist(i + 95) = fullname2
Next i

filePermissionCandidates = Array(filelist)
fileAccessGranted = GrantAccessToMultipleFiles(filePermissionCandidates)
End Sub

 

[wwrightchess]

In the routine I posted, what worked was to set filePermissionCandidates=Array(filelist(1),filelist(2), etc
tried filePermissionCandidates=filelist and filrPermissionCandidates=Array(filelist) but both gave me the type mismatch error.
According to the link I posted, this appears to be an issue with Macs