I am trying to run a VBA macro in Excel 2016 for Mac. The macro works on Windows platforms with Excel 2016, and on Mac platforms with earlier than the 2016 version. Auto Refresh Pivot Tables Excel 2016 for Mac I would like a Pivot Table and Chart to refresh automatically when the source data has changed. I have tried some VBA codes that were posted on the internet but they made Excel crash. Excel 2016 for Mac was upgraded to 64-bit from 32-bit versions. This has caused issues with the VBA-Web functionality in Excel 2016 for Mac. Microsoft acknowledges that this is an issue for many add-ins. Look for whatever you are trying to convert inside those tabs. When you open Ondesoft iTunes Converter you’ll see a series of tabs that divide the content: music, videos, podcasts or audiobooks, for example. The issue appears specific to Excel 2016 for Mac when trying to export a CSV. The code is supposed to allow the user to click a button, which will then export an active worksheet to a CSV file. While a similar issue was documented here () and here (), neither of the solutions worked for me (one solution was changing the FileFormat parameter to 6 from xlCSV and the other was to replace / in a file path with:). The code works up until the ActiveWorkbook.SaveAs Filename:=newFileName, FileFormat:=6, CreateBackup:=False line, which then throws the error: Run-time error '1004':Method 'SaveAs' of object '_Workbook' failed If I change FileFormat to 51 (.xlsx) or 53 (.xlsm) the code will successfully finish. However, if FileFormat is set to 6 (.csv) the code will throw the error above. I am unable to SaveAs xlCSV or xlCSVMac. The full script is: Sub btnExportCSV_Click () Dim oldFileName As String Dim newFileName As String Dim timeStamp As String Dim fileAccessGranted As Boolean Dim filePermissionCandidates Dim wsPath As String timeStamp = Format ( Now, 'yyyymmddhhmmss' ) wsPath = Application. Path oldFileName = ThisWorkbook. FullName newFileName = Mid ( oldFileName, 1, InStrRev ( oldFileName, '.' ) - 1 ) & timeStamp & '.csv' ' Check if software is Office 2016 for Mac ' Documentation for this comes from # If MAC_OFFICE_VERSION >= 15 Then filePermissionCandidates = Array ( wsPath ) fileAccessGranted = GrantAccessToMultipleFiles ( filePermissionCandidates ) # End If Application. DisplayAlerts = False Sheets ( 'OfflineComments' ). How to turn off scripts in word for mac 2011. Activate Sheets ( 'OfflineComments' ). Copy ActiveWorkbook. ![]() SaveAs Filename:= newFileName, FileFormat:= 6, CreateBackup:= False ActiveWorkbook. Save ActiveWindow. Close MsgBox ( 'Offline comments exported to ' & newFileName ) Application. DisplayAlerts = True End Sub I have tried: • Adding a full path to the input/output file names • Ensuring that version of Excel is checked for and permissions allowed by user • Various file types for the FileFormat parameter, but as mentioned, only two types actually worked. • Trying the code mentioned in the above-linked article, which did not help (and which is why I am posting the original code here). • Changing the problematic line to Sheets('OfflineComments').SaveAs Filename:=newFileName, FileFormat:=6, CreateBackup:=False which throws the error Run-time error '1004': SaveAs method of Worksheet class failed. • For some reason, it worked randomly about 10 times (only in one directory, if the workbook was moved to another directory and I tried to run the code, it would throw the 1004 error) and then when I commented out a line of code, tried something else, and then reverted back to the working code, it stopped working. This issue is also documented here. For anyone who finds this thread, the most recent version of the conversation is here: There is no update or solution to the issue, other than that it is likely a permissions problem specific to Office 2016 for Mac which has occurred on multiple machines. Workarounds include: • Following these (instructions to save to a folder created within the depths of your Office system files, as that is the only one with correct permissions, somehow. Not really a solution if you are distributing your macro to multiple users and you don't want to create random folders in their system files. Or • Check if the user is running Office 2016 for Mac, and if so, prompt them to save the CSV file manually using Application.GetSaveAsFilename.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |