Latest Update Release for Excel on Mac

VBA learner ITG

Active Member
Joined
Apr 18, 2017
Messages
272
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
[FONT=&quot]Hi all,[/FONT]

[FONT=&quot]I have been advised that the latest update to Excel on the Mac has caused the below piece of code to stop working and showing an error message.[/FONT]

Can anyone advise if this is a settings issue or does a new code need to be introduced with the latest update?


Code that is highlighted when i debug:

Code:
[COLOR=#000000][FONT=Courier]vFile = Application.GetOpenFilename[/FONT][/COLOR]

[FONT=&quot]ERROR MESSAGE:
[/FONT]
[FONT=&quot]Run time error 1004 Method Getopenfilename as object_application failed[/FONT]


[FONT=&quot]Below is the master code:
[/FONT]
Code:
Sub Import()


    Dim vFile       As Variant
    Dim wbCopyTo    As Workbook
    Dim wsCopyTo    As Worksheet
    Dim wbCopyFrom  As Workbook
    Dim wsCopyFrom  As Worksheet


    Set wbCopyTo = ActiveWorkbook
    Set wsCopyTo = wbCopyTo.Sheets.Add
    ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
    
    
Application.DisplayAlerts = False


    vFile = Application.GetOpenFilename
    
    If TypeName(vFile) = "Boolean" Then
        Exit Sub
    Else
    Set wbCopyFrom = Workbooks.Open(vFile)
    Set wsCopyFrom = wbCopyFrom.Worksheets(1)


    Application.CutCopyMode = False


    End If
    
        
Set oneRange = Range("B26:Z1000")
Set aCell = Range("A1")


    wsCopyFrom.Range("B26:Z1000").Copy
    wsCopyTo.Range("A1").PasteSpecial Paste:=xlValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            
            
wbCopyFrom.Close False


Sheets("Assessment Tab").Select
    Range("C6").Select




End Sub
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Try changing

Code:
[COLOR=#000000][FONT=Courier]vFile = Application.GetOpenFilename[/FONT][/COLOR]

To

Code:
​[COLOR=#303336][FONT=inherit][COLOR=#000000][FONT=Courier]vFile = [/FONT][/COLOR]Select_File_Or_Files_Mac[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR]

and add a module with

Code:
[COLOR=#101094][FONT=inherit]Function[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Select_File_Or_Files_Mac[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]String[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">[COLOR=#303336][FONT=inherit]    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] MyPath [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]String[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] MyScript [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]String[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] MyFiles [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]String[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] MySplit [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Variant[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] N [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Long[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] FName [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]String[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] mybook [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Workbook

    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]On[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Error[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Resume[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Next[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    MyPath [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] MacScript[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"return (path to documents folder) as String"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#858C93][FONT=inherit]'Or use MyPath = "Macintosh HD:Users:Ron:Desktop:TestFolder:"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

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

    MyFiles [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] MacScript[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]MyScript[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] returnList[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]String[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]On[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Error[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]GoTo[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] MyFiles [/FONT][/COLOR][COLOR=#303336][FONT=inherit]<>[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]""[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Application
            [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]ScreenUpdating [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]False[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
            [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]EnableEvents [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]False[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

        [/FONT][/COLOR][COLOR=#858C93][FONT=inherit]'MsgBox MyFiles[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        MySplit [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Split[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]MyFiles[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]","[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]ReDim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] returnList[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]LBound[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]MySplit[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]To[/FONT][/COLOR][COLOR=#303336][FONT=inherit] UBound[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]MySplit[/FONT][/COLOR][COLOR=#303336][FONT=inherit]))[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]For[/FONT][/COLOR][COLOR=#303336][FONT=inherit] N [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] LBound[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]MySplit[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]To[/FONT][/COLOR][COLOR=#303336][FONT=inherit] UBound[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]MySplit[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

            returnList[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]N[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] MySplit[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]N[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Next[/FONT][/COLOR][COLOR=#303336][FONT=inherit] N
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Application
            [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]ScreenUpdating [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]True[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
            [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]EnableEvents [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]True[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        Select_File_Or_Files_Mac [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] returnList
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Else[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]ReDim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] returnList[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]To[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        returnList[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"False"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        Select_File_Or_Files_Mac [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] returnList
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR]</code>[COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Function[/FONT][/COLOR]
 
Last edited:
Upvote 0
I haven’t had an opportunity to try this yet, but does anyone know why this new update causes this code to break and needs amending?
 
Upvote 0
Be sure to click the Smile button in the upper right corner of the XL window and report this bug to Microsoft so they get it on the list of bugs to fix.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top