UTF8 format CSV file output method (AppleScriptask version)

XLUTF8CSV has been added to the SAVEAS function of Excel VBA, making it possible to easily create UTF8 format CSV files. However, on Excel for Mac, the operation is unstable and cannot be used. Windows can be easily realized by using CreateObject, but it is not on a Mac. In this article, I will introduce how to create UTF8 format CSV files using AppleScript, a Mac standard script. This article summarizes several articles in FORME-NOTE, a blog of the app Forme developed and operated. First, I will explain AppleScript and AppleScriptTask, but if you are already known, please read from the coding.

What is AppleScript?

A script language that boasts immovable position as a Mac standard script. To create it from the script editor. It has a structure similar to English. For a long time, Excel for Mac has executed Excel macros via AppleScript.

What is AppleScriptask?

This API is a function that uses AppleScript from Excel VBA introduced from Excel2016 for Mac. In the previous model, Excel 2011 for Mac, I controlled AppleScript from VBA by handing inline AppleScript to the argument of API called Macscript, but I moved to a safer Applescriptask from improving security awareness. applescripttask（”AppleScript file name”,“function name”,“argument”） Use AppleScriptTask as described above. The argument is a string, and the return value is also a string. Write it in a normal VBA MSGBOX or INPUTBOX form.

Dim Res as String
Res = AppleScriptTask("AppleScript file name","function name","argument")

Even if there is no return value, it must be used as a set with a variant function.

Dim anchor as Variant
anchor = applescripttask（"AppleScript file name","function name","argument"）

If you neglect this, VBE will process it as a syntax error. Also, if it is used in Windows, it is necessary to add a conditional branch as follows.

On Error GoTo 256

If Not Application.OperatingSystem Like "*Mac*" Then
    Windows
else
    If val(Application.Version) < 15 Then
        Excel2011 for Mac
    else
        #If MAC_OFFICE_VERSION >= 15 Then
            Processing after Excel2016 for Mac (using AppleScriptTask here)
         #end if
    end if
end if

In AppleScripttask, it is only a string, so you need to use the Split function when using an array. In addition, it seems that there are no special errors such as no script. (DIR function can be used.)

Relationship between VBA and AppleScript

The Mac version of the CreateObject function of the Windows version of Excel is AppleScriptask. The relationship between VBA and AppleScript and Excel is as shown below.

In fact, AppleScript, which runs from VBA, may operate the Excel sheet directly. The advantage of using AppleScriptask in VBA is that not only the replacement of the CreateObject function, but also not to worry about file authority. Excel2016 For Mac and later, the first files access are displayed with dialogs as shown in the figure below, and users have to manage access authority.

When assembling an Excel macro, I think that there is a case where you do work such as loading a CSV file in a predetermined folder, but if there is a file to access for the first time, the processing will stop. The only way to avoid this is to choose between the explicit access authority dialog before the processing, or to use Applescript. This access authority dialog is quite habitual, not displayed in the Loadpicture function, etc., just an error. In the case of an image path, you must explicitly display the access authority dialog.

Sub requestFilesPermission(ByRef files() As String)
    Dim fileAccessGranted As Boolean
    Dim filePermissionCandidates

    filePermissionCandidates = files()
    fileAccessGranted = GrantAccessToMultipleFiles(filePermissionCandidates)
End Sub

coding

Let’s enter the coding from here. The flow to use AppleScriptTask is as follows.

Normally, the procedure of the white frame is performed, but considering the troubles of folder operation and distribution, this time we will automatically create and execute the installation script, which is the blue part. In this method, just running the automatically created script will solve complicated folder operations and multiple files at the time of distribution.

There is a directory that cannot be accessed directly with VBA

The text file in the standard function of VBA cannot be stored in the script folder

/users/username/library/Application scripts/com.microsoft.Excel

When you execute it, the following error occurs.

I have never seen a clear explanation document, but it seems that VBA cannot access directly. You can open the folder with Hyperlink. Therefore, take a detour using AppleScript that can access the script folder directly. The following is as follows.

First, save AppleScript for Applescriptask installation from VBA in the shared folder (/users/share) and open it in Hyperlink. The script editor starts automatically. This time it was a shared folder, but if it can be saved, there is no problem with a current folder. Next, ask the user to execute the launch of the script editor. This work cannot be automated, so it is necessary to explain to the user in advance. In this AppleScript, after installing the script folder, the script folder is opened so that you can check the installation contents.

UTF8 format CSV file output AppleScript

The AppleScript used this time is only the output string, and the storage location, file name, etc. should be processed on the Applescript side. If the processing fails, “false” is returned.

ST8（"TEST"） 'debug code. Please remove it in production environment.

on st8(txtval)
	if txtval = "" then
		return
	end if
	tell application "Finder"
		activate
		try
			set dpath to choose file name with prompt "Input New FileName." default name "newdata.csv"
			set spath to (dpath as string)
			if (offset in spath of ".") = 0 then
				set spath to spath & ".txt"
			end if
			try
				set tfile to open for access file spath with write permission
				set eof of tfile to 0
				write txtval to tfile as «class utf8»
			end try
			close access tfile
		on error number err_num
			false
		end try
	end tell
	
end st8

When you start this code, the following storage dialog is displayed, and the text file with the name specified in the specified location is output in UTF8.

If you save this file in a script folder manually, you will be ready to use AppleScriptTask. Avoid using a file name other than English to the created AppleScript. In my experience, it is safer not to attach a pass other than English at Excel for Mac, so it is better not to attach it. The trouble when using AppleScriptTask is that you have to learn AppleScript. It is good to try and error with the script editor at hand, but let’s borrow the know -how of the great predecessors. The site that I mainly refer to is here.

The code that is VBA inline with A2V and outputs to the shared folder

Access the VBA inline web tool A2V of the AppleScript developed by the author and click Upload.

Select the created AppleScript and click Convert.

Copy or download a converted one.

Speaking of what the A2V is doing, the created Applescript is wrapped to the installer and the inline is rewritten at the same time to match the VBA specification. In VBA, the number of characters that can be set in the function at once is determined to be 256 characters. In addition, characters that cannot be written directly are re -set with character code. The following shows the VBA code for outputting the converted AppleScript.

sub SaveAppleScript
Dim path1 as String,applescript as String

path1 = "/Users/Shared/test.applescript"
applescript = Code output by A2V

Open path1 For Output As #2
    Print #2, applescript
Close #2

ActiveWorkbook.FollowHyperlink Address:=path1

'When you run the opened script, it becomes a script file that can be used by AppleScriptTask.

End Sub

Finally, open with Hyperlink. When a user runs in a script editor, a script for AppleScriptTask is output to the script folder.

timing and code to install

The timing to install Applescript is either when you open the workbook or when you use ApplescriptTask. From the viewpoint of user work results, it is appropriate to execute when the workbook is opened, but if you use a relatively stable AppleScript like this time, it is fine. There is no function to find out if AppleScript exists, so execute it once, judge that there is no AppleScript if an error occurs, and execute the installation macro.

Exit Sub
256
    If Application.OperatingSystem Like "*Mac*" Then
        'Install it or tell it that there is no script file.
    End If

Completed code

The ultimately completed code is as follows. It is easy to distribute because it is compiled by one VBA code, and it can be used in various environments because it is added to Windows and Excel2016 for Mac.

Sub saveUTF8txt()
Dim txtValue as String
On Error GoTo 256

txtValue = ActiveCell.Value

If Not Application.OperatingSystem Like "*Mac*" Then
    'Windows
else
    If val(Application.Version) < 15 Then
        'Excel2011 for Mac
    else
        #If MAC_OFFICE_VERSION >= 15 Then
            Dim Res as String
            Res = AppleScriptTask("FormeSaveTxt8.applescript","st8",txtValue)
         #end if
    end if
end if

Exit Sub
256
    If Application.OperatingSystem Like "*Mac*" Then
		installAs 'Installation measures
	End If
End Sub

Sub installAs()
Dim path1 as String,applescript as String

path1 = "/Users/Shared/scriptInstaller.applescript"
applescript = FormeSaveTxt8

requestFilesPermission(path1) 'Get access explicitly. Without this, the first time may be an error.

Open path1 For Output As #2
    Print #2, applescript
Close #2

ActiveWorkbook.FollowHyperlink Address:=path1

'When you run the opened script, it becomes a script file that can be used by AppleScriptTask.
End Sub

’Explicit permission acquisition
Sub requestFilesPermission(ByVal fpath As String)
    Dim fileAccessGranted As Boolean
    Dim filePermissionCandidates

    filePermissionCandidates = Array(fpath)
    fileAccessGranted = GrantAccessToMultipleFiles(filePermissionCandidates)
End Sub

’Inline code automatically output by a2v
Function FormeSaveTxt8() as String
Dim code as String

code = "set root to (path to home folder) as string"& vbnewline &"set mkEdir to root & "& chrw(34) &"Library:Application Scripts:"& chrw(34) & vbnewline _
& "set tgtpath to root & "& chrw(34) &"Library:Application Scripts:com.microsoft.Excel:"& chrw(34) & vbnewline &"set mkas to tgtpath & "& chrw(34) &"FormeSaveTxt8.applescript"& chrw(34) & vbnewline & vbnewline _
&"set asval to "

code = code & chrw(34) &"on st8(txtval)"& chrw(34) & "& linefeed & "
code = code & chrw(34) &" if txtval = "& chrw(92) & chrw(34) & chrw(92) & chrw(34) &" then"& chrw(34) & "& linefeed & "
code = code & chrw(34) &" return"& chrw(34) & "& linefeed & "
code = code & chrw(34) &" end if"& chrw(34) & "& linefeed & "
code = code & chrw(34) &" tell application "& chrw(92) & chrw(34) &"Finder"& chrw(92) & chrw(34) & chrw(34) & "& linefeed & "
code = code & chrw(34) &" activate"& chrw(34) & "& linefeed & "
code = code & chrw(34) &" try"& chrw(34) & "& linefeed & "
code = code & chrw(34) &" set dpath to choose file name with prompt "& chrw(92) & chrw(34) &"Input New FileName."& chrw(92) & chrw(34) &" default name "& chrw(92) & chrw(34) &"newdata.csv"& chrw(92) & chrw(34) & chrw(34) & "& linefeed & "
code = code & chrw(34) &" set spath to (dpath as string)"& chrw(34) & "& linefeed & "
code = code & chrw(34) &" if (offset in spath of "& chrw(92) & chrw(34) &"."& chrw(92) & chrw(34) &") = 0 then"& chrw(34) & "& linefeed & "
code = code & chrw(34) &" set spath to spath & "& chrw(92) & chrw(34) &".txt"& chrw(92) & chrw(34) & chrw(34) & "& linefeed & "
code = code & chrw(34) &" end if"& chrw(34) & "& linefeed & "
code = code & chrw(34) &" try"& chrw(34) & "& linefeed & "
code = code & chrw(34) &" set tfile to open for access file spath with write permission"& chrw(34) & "& linefeed & "
code = code & chrw(34) &" set eof of tfile to 0"& chrw(34) & "& linefeed & "
code = code & chrw(34) &" write txtval to tfile as "& chr(-32397) &"class utf8"& chr(-32396) & chrw(34) & "& linefeed & "
code = code & chrw(34) &" end try"& chrw(34) & "& linefeed & "
code = code & chrw(34) &" close access tfile"& chrw(34) & "& linefeed & "
code = code & chrw(34) &" on error number err_num"& chrw(34) & "& linefeed & "
code = code & chrw(34) &" false"& chrw(34) & "& linefeed & "
code = code & chrw(34) &" end try"& chrw(34) & "& linefeed & "
code = code & chrw(34) &" end tell"& chrw(34) & "& linefeed & "
code = code & chrw(34) &" "& chrw(34) & "& linefeed & "
code = code & chrw(34) &"end st8" & chrw(34)

code = code & vbnewline & "tell application "& chrw(34) &"Finder"& chrw(34) & vbnewline & "if not (exists tgtpath) then"& vbnewline &"make new folder at mkEdir with properties {name:"& chrw(34) &"com.microsoft.Excel"& chrw(34) &"}"& vbnewline & "end if"& vbnewline &"end tell"& vbnewline _
& vbnewline &"try"& vbnewline &"set tfile to open for access file mkas with write permission"& vbnewline &"set eof of tfile to 0"& vbnewline &" write asval to tfile"& vbnewline &"end try"& vbnewline & vbnewline &"close access tfile"& vbnewline & vbnewline _
&"tell application "& chrw(34) &"Finder"& chrw(34) & vbnewline &" open tgtpath"& vbnewline &"end tell"

FormeSaveTxt8 = code

End Function

The contents introduced this time are used in the macro sheet of the Kaizen Handy Terminal App ForMe, which I have developed and operated. If you are interested in FORME or difficult to build a macro, please use it.