Mac Excel MySQL: (1) VBA (2) ODBC.
(1) VBA ( ):
Option Explicit
Sub WaitQueryTableRefresh(ByVal qt As QueryTable)
While qt.Refreshing
Application.Wait (Now + TimeValue("0:00:01"))
Wend
End Sub
Sub ErrorIfQueryTableOverflowed(ByVal qt As QueryTable)
If qt.FetchedRowOverflow Then
err.Raise 5, "ErrorIfQueryTableOverflowed", _
"QueryTable '" & qt.ListObject.Name & "' returned more rows than can fit in the spreadsheet range"
End If
End Sub
' Create a table from scratch
Function CreateTableFromSql( _
ByVal table_sheet As Worksheet, _
ByVal table_range As Range, _
ByVal table_name As String, _
ByVal sql As String _
) As ListObject
' table_range is simply the top-left, corner cell for the table
'ListObject.SourceType
'https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.xllistobjectsourcetype?view=excel-pia
'QueryTable.CommandType
'https://docs.microsoft.com/en-us/office/vba/api/Excel.QueryTable.CommandType
'QueryTable.BackgroundQuery
'https://docs.microsoft.com/en-us/office/vba/api/excel.querytable.backgroundquery
'QueryTable.RefreshStyle
'https://docs.microsoft.com/en-us/office/vba/api/excel.xlcellinsertionmode
'QueryTable.PreserveColumnInfo
'https://stackoverflow.com/a/28621172
'https://docs.microsoft.com/en-us/office/vba/api/Excel.QueryTable.PreserveColumnInfo
Dim global_odbc_str As String
global_odbc_str = "ODBC;DSN=my_dsn_name;"
Dim qt As QueryTable
Set qt = table_sheet.ListObjects.Add( _
SourceType:=xlSrcExternal, _
Source:=global_odbc_str, _
Destination:=table_range _
).QueryTable
With qt
.ListObject.Name = table_name
.ListObject.DisplayName = table_name
.CommandText = sql
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.Refresh BackgroundQuery:=False
End With
Call WaitQueryTableRefresh(qt)
Call ErrorIfQueryTableOverflowed(qt)
Set CreateTableFromSql = qt.ListObject
End Function
' Update a table (columns do not have to be the same)
Sub UpdateTableFromSql( _
ByVal table As ListObject, _
ByVal sql As String _
)
Dim qt As QueryTable
Set qt = table.QueryTable
qt.CommandText = sql
qt.Refresh BackgroundQuery:=False
Call WaitQueryTableRefresh(qt)
Call ErrorIfQueryTableOverflowed(qt)
End Sub
(2) MySQL ODBC ()
MySQL :
(a) () iODBC Admin: http://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads
(b) MySQL ODBC: https://dev.mysql.com/downloads/connector/odbc/
(c) Mac , ( Excel) . - MySQL , Excel . , DSN Test iODBC Test ODBC Excel.
:
#!/bin/bash
base_src_dir="/usr/local"
mysql_odbc_name=$(ls "$base_src_dir" | grep -m 1 "mysql-connector-odbc")
odbc_dir="/Library/ODBC"
src="$base_src_dir/$mysql_odbc_name/lib"
dst="$odbc_dir/$mysql_odbc_name/lib"
echo "creating '$dst'"
sudo mkdir -p "$dst"
echo "copying '$src' to '$dst'"
sudo cp -af "$src/." "$dst"
odbc_ini_path="$odbc_dir/odbc.ini"
odbc_ini_bak_path="$odbc_ini_path.bak"
odbcinst_ini_path="$odbc_dir/odbcinst.ini"
odbcinst_ini_bak_path="$odbcinst_ini_path.bak"
echo "backing up '$odbc_ini_path' to '$odbc_ini_bak_path'"
sudo cp -f "$odbc_ini_path" "$odbc_ini_bak_path"
echo "backing up '$odbcinst_ini_path' to '$odbcinst_ini_bak_path'"
sudo cp -f "$odbcinst_ini_path" "$odbcinst_ini_bak_path"
function replace {
sudo sed -i '' "s/$(sed 's/[^^]/[&]/g; s/\^/\\^/g' <<< "$1")/$(sed 's/[&/\]/\\&/g' <<< "$2")/g" "$3"
}
ansi_driver=$(ls "$dst" | grep -m 1 "^lib.*a\.so$")
unicode_driver=$(ls "$dst" | grep -m 1 "^lib.*w\.so$")
old_ansi_path="$src/$ansi_driver"
new_ansi_path="$dst/$ansi_driver"
old_unicode_path="$src/$unicode_driver"
new_unicode_path="$dst/$unicode_driver"
echo "updating '$old_ansi_path' to '$new_ansi_path' in '$odbc_ini_path'"
replace "$old_ansi_path" "$new_ansi_path" "$odbc_ini_path"
echo "updating '$old_ansi_path' to '$new_ansi_path' in '$odbcinst_ini_path'"
replace "$old_ansi_path" "$new_ansi_path" "$odbcinst_ini_path"
echo "updating '$old_unicode_path' to '$new_unicode_path' in '$odbc_ini_path'"
replace "$old_unicode_path" "$new_unicode_path" "$odbc_ini_path"
echo "updating '$old_unicode_path' to '$new_unicode_path' in '$odbcinst_ini_path'"
replace "$old_unicode_path" "$new_unicode_path" "$odbcinst_ini_path"
High Sierra Excel 2016.