Let's create a system with Excel
Let's use Excel as a system using the Forme series. It can be used on both Windows and Mac OS.
FormeStudio
You can aggregate CSV data of ForMe. It is compatible with both Windows and Mac so it can be used in environments where both OSs exist. Please use it as the base of your system.
Check Google Spreadsheet Version
Support
Excel 2013,Excel 2016,Excel 2019,Excel 2011 for Mac,Excel 2016 for Mac,Excel 2019 for Mac
Excel version16 Higher
LibreOffice Calc(6.2.6.2 Higher)
Google Spreadsheet (Code and implementation example only. Implementation example can be copied and used freely.)
default,UTF-16,UTF-8
CSV data capacity limit
- Windows : 3MB
- Excel 2011 for Mac : 1MB
- Excel 2016 for Mac : 20KB
- Excel 2019 for Mac : 1MB(Ver2.1)
- LibreOffice Calc : 15KB
1800000cells
In LibreOffice, the CSV reading character code is only OS standard.
For your reference
CSV data (150 B) 40 Time to expand data.
- Excel 2013(Fusion Windows8.1) : 0'07.75
- Excel 2011 for Mac(Macbook Air late 2012) : 0'09.85
- Excel 2016 for Mac(iMac mid 2011) : 1'20.25
Caution
The first 0 is omitted. "001A" > "1A"
When using the CSV database, please set the path each time the usage environment changes.
Mac
When reading CSV data of UTF 8 outside the Japanese environment, it is necessary to modify AppleScript.
">>" -> "»" , "<<" -> "«"
In Excel 2016,2019 for Mac, script file You need to go to "~/Library/Application Scripts/com.microsoft.Excel/". If the "com.microsoft.Excel" folder already exists, please move only the script file inside.
We have prepared an assist tool for installing script files. (A warning may be displayed. In that case, open it from the context menu.)
Script file save destination
Finder > Move(option) > Library > Application Scripts > com.microsoft.Excel
Function introduction
-
Add CSV datasheet
Extract the selected CSV data to a new sheet.
- 1.Select CSV data
- 2.Expand to a new sheet table
-
Add CSV Folder Sheet
Expand CSV data that matches part of the name of the selected CSV data in the same folder to a new sheet.
Expand CSV data that matches the first character string delimited by "_". If it is "AAA_123.csv", expand CSV data that matches "AAA".
- 1.Select CSV data
- 2.Expand to a new sheet table
-
Added CSV database sheet
Expand CSV data that matches part of the name of the selected CSV data in the same folder to a new sheet, Move to backup folder. The selected folder can be monitored automatically by the button on the sheet. Automatic monitoring is also possible.
Expand CSV data that matches the first character string delimited by "_". If it is "AAA_123.csv", expand CSV data that matches "AAA".
- 1.Select CSV data
- 2.Select backup folder
- 3.Expand to a new sheet table
-
File monitoring
Update the CSV database sheet.
-
Paste CSV data
Deploy selected CSV data to active cell.
- 1.Select the destination cell
- 2.Select CSV data
-
Export
If the active sheet is a sheet created by FormeStudio, the default code will export the table to the selected folder as CSV data. Excel 2016 and later will export the entire sheet as CSV data in UTF-8, or the default code if less. (Always overwritten.)
-
Launch FormeQRCoder
Start Form QR Coder. Excel 2016 or later will be automatically connected if FormeQRCoder.html exists in the same path as the workbook.
-
Acquire the CSV data path
Deploy the selected CSV data path to the active cell.
-
Get folder path
Extract the selected folder path to the active cell.
-
Get data name
Expand the selected data name to the active cell. If a folder path is entered in the active cell, file names under the folder will be enumerated.
-
Get current time
Expands current time to active cell.
-
Select A1
Select A1 on all sheets.
-
VBA project unlock code
Required to get the code to unlock the project in FormeStudio.
ForMe Throwawaykey Ver2.0 or higher is required.
- 1.Enter the displayed code in Throwawaykey, create ciphertext
- 2.Send ciphertext to PC
- 3.Select throwawaykey.txt
- 4.Enter the PIN code displayed on Throwawaykey
- 5.Since VBA project unlock code is displayed, enter the code on VBAEditor and unlock
Excel 2016,2019 for Mac VBA
The VBA of Excel 2016,2019 for Mac has been greatly restricted. You can complement the function by using the script file of FormeCollector.
- FormeGetFiles : Get all visible file names in folder
- FormeFolderChooser : Get folder path
- FormeGetFileSize : Get specified file path size
- FormeLoadTxtD : Read a text file
- FormeLoadTxt16 : Read UTF 16 text file
- FormeLoadTxt8 : Read UTF 8 text file
- FormeFileMove : Move the file. Use "file path, folder path" as an argument
If all fails, "false" will be returned.
・・・
Let's change the time of data input to analysis and presentation time.