使用VBA在Apple Macintosh上使用Excel [英] Excel on Apple Macintosh using VBA

问题描述

我正在尝试以编程方式在Excel中打开工作表以响应用户操作。我的代码非常简单：

    Dim doit As Integer 
    doit = Application.Dialogs（xlDialogOpen）。显示
   如果doit = 0那么
       退出Sub 
   结束如果

在Windows中的Excel上运行时，会显示一个打开的文件对话框，然后我可以选择一个随后打开的文件。一切都很好。

但是当在Macintosh上运行时，从"打开文件"对话框中选择要打开的文件后出现错误：

运行时错误1004 
 
"无法找到。检查文件名的拼写，并验证文件位置是否正确。

感谢任何帮助。

Randy

解决方案

RLZack ，

我只适用于Windows PC。我尝试在其上运行代码并且它可以正常工作。

您的上述代码可能只适用于Windows PC。

我建议你参考下面为MAC编写的代码示例。

 Sub Select_File_Or_Files_Mac（）
 Dim MyPath As String 
 Dim MyScript As String 
 Dim MyFiles As String 
 Dim MySplit As Variant 
 Dim N As Long 
 Dim Fname As String 
 Dim mybook As Workbook 
 
 On Error继续下一个
 MyPath = MacScript（"返回（文档文件夹的路径）为String"）
'或者使用MyPath =" Macintosh HD：用户：Ron：桌面：TestFolder：" 
 
'在以下语句中，在行"多个
"选项允许的情况下将true更改为false"如果你不想比一个文件选择更多
'。此外，如果您要过滤多个文件，请将
'{"" com.microsoft.Excel.xls"}}更改为
'{"" com.microsoft.excel。例如，如果要对xls和csv文件进行过滤，请使用xls""，""" public.comma-separated-values-text""} 
'。 
 MyScript = _ 
"将applescript的文本项分隔符设置为""，"" " &安培; vbNewLine& _ 
"将theFiles设置为（选择类型文件& _ 
" {""" com.microsoft.Excel.xls""}"& _ 
"带有提示"""请选择一个或多个文件"""默认位置别名""& _ 
 MyPath&""""多个选择允许为true）为字符串" ; &安培; vbNewLine& _ 
"将applescript的文本项分隔符设置为"""" " &安培; vbNewLine& _ 
" return theFiles" 
 
 MyFiles = MacScript（MyScript）
 On Error GoTo 0 
 
如果MyFiles<> ""然后
使用应用
 .ScreenUpdating = False 
 .EnableEvents = False 
以
 
结束MySplit = Split（MyFiles，"，"）
对于N = LBound（MySplit）到UBound（MySplit）
 
'只获取文件名并测试它是否打开。 
 Fname = Right（MySplit（N），Len（MySplit（N）） -  InStrRev（MySplit（N），Application.PathSeparator ,, 1））
如果bIsBookOpen（Fname）= False那么
 
设置mybook = Nothing 
 On Error Resume Next 
设置mybook = Workbooks.Open（MySplit（N））
 On Error GoTo 0 
 
如果Not mybook Is Nothing那么
 MsgBox"你打开这个文件：" &安培; MySplit（N）& vbNewLine& _ 
"然后按OK后它将被关闭" &安培; vbNewLine& _ 
"不保存，请用您自己的代码替换此行。" 
 mybook.Close SaveChanges：= False 
 End If 
 Else 
 MsgBox"我们跳过这个文件：" &安培; MySplit（N）& "因为它已经开放了。" 
结束如果
下一个N 
申请
 .ScreenUpdating = True 
 .EnableEvents = True 
结束
结束如果
结束Sub 
 
函数bIsBookOpen（ByRef szBookName As String）As Boolean 
'由Rob Bovey供稿
 On Error Resume Next 
 bIsBookOpen = Not（Application.Workbooks（szBookName） ）什么都没有）
结束函数

参考：

以编程方式在Excel中为Windows选择文件，在Mac中为Excel选择

其他外部参考：

在Excel VBA中打开文件在mac

在VBA中使用Mac和Wi的打开文件对话框ndows

免责声明：此回复包含对第三方万维网站点的引用。 Microsoft提供此信息是为了方便您。 Microsoft不控制这些网站，也未测试在这些网站上找到的任何软件或信息;因此，Microsoft不能就其中发现的任何软件或信息的质量，安全性或适用性做出任何陈述。使用互联网上的任何软件都存在固有的危险，并且Microsoft提醒您在从互联网上检索任何软件之前确保您完全了解风险的
。

问候

Deepak

I am trying to programmatically open a worksheet in Excel in response to a user action. My code is fairly simple:

    Dim doit As Integer
    doit = Application.Dialogs(xlDialogOpen).Show
    If doit = 0 Then
        Exit Sub
    End If

When run on Excel in Windows, an open file dialog is displayed, and I can select a file which is then opened. All is good.

But when run on a Macintosh, I get an error after selecting a file to open from the Open File dialog:

    Run-time error 1004

    " could not be found. Check the spelling of the file names, and verify that the file location is correct.

Any help is appreciated.

Randy

解决方案

Hi RLZack,

I am only available with Windows PC. I try to run code on that and it works correctly.

it is possible that your above code is only able to work for Windows pc.

I suggest you to refer code example below which is written for MAC.

Sub Select_File_Or_Files_Mac()
    Dim MyPath As String
    Dim MyScript As String
    Dim MyFiles As String
    Dim MySplit As Variant
    Dim N As Long
    Dim Fname As String
    Dim mybook As Workbook

    On Error Resume Next
    MyPath = MacScript("return (path to documents folder) as String")
    'Or use MyPath = "Macintosh HD:Users:Ron:Desktop:TestFolder:"

    ' In the following statement, change true to false in the line "multiple 
    ' selections allowed true" if you do not want to be able to select more 
    ' than one file. Additionally, if you want to filter for multiple files, change 
    ' {""com.microsoft.Excel.xls""} to 
    ' {""com.microsoft.excel.xls"",""public.comma-separated-values-text""}
    ' if you want to filter on xls and csv files, for example.
    MyScript = _
    "set applescript's text item delimiters to "","" " & vbNewLine & _
               "set theFiles to (choose file of type " & _
             " {""com.microsoft.Excel.xls""} " & _
               "with prompt ""Please select a file or files"" default location alias """ & _
               MyPath & """ multiple selections allowed true) as string" & vbNewLine & _
               "set applescript's text item delimiters to """" " & vbNewLine & _
               "return theFiles"

    MyFiles = MacScript(MyScript)
    On Error GoTo 0

    If MyFiles <> "" Then
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With

        MySplit = Split(MyFiles, ",")
        For N = LBound(MySplit) To UBound(MySplit)

            ' Get the file name only and test to see if it is open.
            Fname = Right(MySplit(N), Len(MySplit(N)) - InStrRev(MySplit(N), Application.PathSeparator, , 1))
            If bIsBookOpen(Fname) = False Then

                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(MySplit(N))
                On Error GoTo 0

                If Not mybook Is Nothing Then
                    MsgBox "You open this file : " & MySplit(N) & vbNewLine & _
                           "And after you press OK it will be closed" & vbNewLine & _
                           "without saving, replace this line with your own code."
                    mybook.Close SaveChanges:=False
                End If
            Else
                MsgBox "We skipped this file : " & MySplit(N) & " because it Is already open."
            End If
        Next N
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End If
End Sub

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Contributed by Rob Bovey
    On Error Resume Next
    bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Reference:

Programmatically Selecting Files in Excel for Windows and Excel for the Mac

other external references:

Opening files in Excel VBA on a mac

Use an Open File dialog in VBA with both Mac and Windows

Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

Regards

Deepak

这篇关于使用VBA在Apple Macintosh上使用Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！