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]

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

Yes, that article seems to be the workaround for the issue with Macs.

I think the issue may be with how you are trying to set-up your array.
I would recommend starting with a hard-coded array of just a few files, as shown in the link.
See if you can get that to work (as a "proof of concept").

If you get that to work, then you know the code is solid and should work for you.
Then it is just a matter of creating and populating the array from your workbook, which we can look at at that point.
But first we want to make sure that this process will work for you.

 

[wwrightchess]

The following code works but it would be nice to shorten the list.
I ran it once with filelist(1) to filelist(100) then a second time with the list below. I also need to run it a third time to allow access to the folder as well as the individual file.

Sub requestFileAccess()
Dim fileAccessGranted As Boolean
Dim filePermissionCandidates As Variant
Dim path As String
Dim name As String
Dim fullname As Variant
Dim fullname2 As Variant
Dim filelist(192) As Variant

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(101), filelist(102), filelist(103), filelist(104), filelist(105), filelist(106), filelist(107), filelist(108), filelist(109), filelist(110), _
filelist(111), filelist(112), filelist(113), filelist(114), filelist(115), filelist(116), filelist(117), filelist(118), filelist(119), filelist(120), _
filelist(121), filelist(122), filelist(123), filelist(124), filelist(125), filelist(126), filelist(127), filelist(128), filelist(129), filelist(130), _
filelist(131), filelist(132), filelist(133), filelist(134), filelist(135), filelist(136), filelist(137), filelist(138), filelist(139), filelist(140), _
filelist(141), filelist(142), filelist(143), filelist(144), filelist(145), filelist(146), filelist(147), filelist(148), filelist(149), filelist(150), _
filelist(151), filelist(152), filelist(153), filelist(154), filelist(155), filelist(156), filelist(157), filelist(158), filelist(159), filelist(160), _
filelist(161), filelist(162), filelist(163), filelist(164), filelist(165), filelist(166), filelist(167), filelist(168), filelist(169), filelist(170), _
filelist(171), filelist(172), filelist(173), filelist(174), filelist(175), filelist(176), filelist(177), filelist(178), filelist(179), filelist(180), _
filelist(181), filelist(182), filelist(183), filelist(184), filelist(185), filelist(186), filelist(187), filelist(188), filelist(189), filelist(190), _
filelist(191), filelist(192))
fileAccessGranted = GrantAccessToMultipleFiles(filePermissionCandidates)
End Sub

 

[Joe4]

One thing: You should NEVER use reserved words (the names of existing functions, properties, methods, objects, etc) as the name of your variables, functions, or procedures.
Doing so can cause errors and unexpected results.
So you should NOT use reserved words like "Path" and "Name" as names of your variables.

Try this and see if this works:

Sub BuildArray()

    Dim fileAccessGranted As Boolean
    Dim lr As Long
    Dim i As Long
    Dim myPath As String
    Dim myName As String
    Dim fullname As String
    Dim fullname2 As String
    Dim filelist() As Variant
    
'   Set path
    myPath = "/Volumes/MyPassport/parameter_studies/roughness/GlennIce_nml/outputs/"
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    
'   Redimension array
    ReDim filelist(lr * 2 - 3)
    
'   Loop through all rows in column A
    Workbooks("large_and_glaze_adjusted_clean.xlsx").Activate
    For i = 2 To lr
'       Set variable values
        myName = Cells(i, 1)
        fullname = myPath & myName & "/slice_output.txt"
        fullname2 = myPath & myName & "/lewiceoutputs/lewice_output.txt"
'       Populate array
        filelist(i - 2) = fullname
        filelist(i + lr - 3) = fullname2
    Next i
    
'   Grant access
    fileAccessGranted = GrantAccessToMultipleFiles(filelist)

End Sub

 

[wwrightchess]

That worked. I have to remember that arrays start at 0. My fortran brain always wants to start at 1.

 

[Joe4]

That worked. I have to remember that arrays start at 0. My fortran brain always wants to start at 1.

I believe you can change them to start at 1, but 0 is the default.

Also, I think you have a problem with this line:

filelist(i + 95) = fullname2

as 2 to 97 is actually 96 values, not 95.