Excel VBA / Mac (Big Sur) -无法访问只读文档

Question

我想写一个简单的宏在我的Mac上运行(Excel 16.61, Mac Book Pro运行Big Sur 11.4)，将表的可见行复制到新的工作簿中，然后将新工作簿保存为*.csv文件。

当前(无效)代码:

Sub Macro()
Dim wb as Workbook
Dim wbOutput As Workbook
Dim FilePath As String
Set wb = ThisWorkbook
FilePath = "/path/to/filename.csv"
' Copy the visible rows of a filtered table
With wb.Sheets("WorksheetName").ListObjects("tblName")

.Range.AutoFilter Field:=18, Criteria1:="TRUE"
.Range.SpecialCells(xlCellTypeVisible).Copy

End With

' Paste the copied table rows into a new workbook and save as a *.csv file
Set wbOutput = Workbooks.Add

wbOutput.Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
wbOutput.SaveAs FileName:=FilePath, FileFormat:=xlCSV, CreateBackup:=False
wbOutput.Close

End Sub

当我运行它时，我得到以下错误:

Run-time error '1004': Cannot access read-only document [filename]

花了几个小时在网上搜索，我还是没有找到答案。互联网上的建议包括:

• 在System Preferences.../Security & Privacy/Files and Folders中添加Excel(我看不到添加新应用程序的明显方式，只需删除已经具有文件夹访问权限的应用程序的访问权限)
• GrantAccessToMultipleFiles函数，但在函数的输入数组中加入FilePath没有区别。

如何从表中创建*.csv文件?

Answer 1

遇到了同样的问题，但我的文件格式是。txt，但这是我做了一些研究后的解决方案，并从Mac VBA大师Ron De Bruin那里得到了一些可靠的帮助。

代码基本上绕过创建输出文件，在我的情况下。txt文件在文件夹位置有安全协议，导致错误1004消息，并在我的用户配置文件下的Microsoft文件夹中创建一个子文件夹，无论出于何种原因Excel/Mac不认为是安全威胁，并允许VBA创建/保存输出文件到该文件夹。

希望，您可以从代码和函数中提取出所需的内容以使您的工作正常进行。还有一件事，由于输出到一个如此奇怪的文件夹位置，我建议你将文件夹路径保存在Finder左面板的收藏夹下，这样你就可以很容易地找到文件。查看文件夹位置的MsgPopup框

Sub Create_TxtFiles()
Dim MacroFolder        As String
Dim nW                 As Workbook
Dim ws1                As Worksheet, ws2 As Worksheet
Dim DT                 As String, RelativePath As String, wbNam1 As String, wbNam2 As String, Filepath As String
'Declarations
Set ws1 = ThisWorkbook.Sheets("Extract1")
Set ws2 = ThisWorkbook.Sheets("Extract2")

RelativePath = ThisWorkbook.Path & "/"
DT = Format(CStr(Now), "mm_dd_yyyy hh.mmam/pm")
wbNam1 = "Extract 1 Output"    'Creates the File Name
wbNam2 = "Extract 2 Output"    'Creates the File Name

MacroFolder = "Upload Files"
Call CreateFolderinMacOffice2016(MacroFolder)

'set the savepath as the obscure folder vba has access to'
savepath = Application.DefaultFilePath & MacroFolder & "/"

'copy the Output 1
ws1.Copy
ActiveWorkbook.SaveAs savepath & wbNam1 & DT & ".txt", FileFormat:=42
Workbooks(wbNam1 & DT & ".txt").Close

'copy the Output 2
ws2.Copy
ActiveWorkbook.SaveAs savepath & wbNam2 & DT & ".txt", FileFormat:=42
Workbooks(wbNam2 & DT & ".txt").Close

Application.ScreenUpdating = True
msgbox ("Upload file saved to folder: " & vbNewLine & vbNewLine & savepath)
End Sub
Function CreateFolderinMacOffice2016(NameFolder As String) As String
'Function to create folder if it not exists in the Microsoft Office Folder
'Ron de Bruin : 1-Feb-2019
Dim OfficeFolder As String
Dim PathToFolder As String
Dim TestStr As String
OfficeFolder = Application.DefaultFilePath
PathToFolder = OfficeFolder & NameFolder
On Error Resume Next
TestStr = Dir(PathToFolder & "*", vbDirectory)
On Error GoTo 0
If TestStr = vbNullString Then
MkDir PathToFolder
End If
CreateFolderinMacOffice2016 = PathToFolder
End Function

Answer 2

我找到了一个适合我的解决方案

首先保存为"Normal File"但是使用。txt或。csv扩展名，然后立即用正确的文件格式覆盖它:

ActiveWorkbook.SaveAs FileName:=myfilename & ".txt", FileFormat:=xlWorkbookNormal ActiveWorkbook.SaveAs FileName:=myfilename & ".txt", FileFormat:=xlTextWindows