Excel在广域网中的运用

创作立场声明：办公EXCEL运用交流，禁止乱转载

去年写了一篇文章关于EXCEL在局域网中的使用，获得了一些点赞，今年由于疫情发展，工作需要在家和公司都能办公，这就不得不研究下如何把之前的局域网运用给升级到广域网环境下仍然正常运行，另一方面，去年的ACCESS数据库经过一年的运行，数据量不小了，单看大小都有20MB了（数万行数据），未雨绸缪，为了防止出现数据访问出现异常，缓慢情况，也需要对数据库进行升级，趁春节闭关期间，把这事给搞定了。

第一步网上查了下ACCESS能否广域网访问，发现这是个难题，并且不安全；于是换个思路，换个能广域网访问并且可靠性经过验证的数据库MYSQL ，这是很多网站采用的数据库，免费够用。

搭建MYSQL环境；

l 搭建mysql数据库

l 设置mysql配置

l 数据库数据迁移。

l EXCEL vba 代码

1.安装MySQL和myODBC for MySQL

官网：https://dev.mysql.com/downloads/windows/

下载mysql-installer-community-8.0.13.0

安装教程见网上。

mysql-connector-odbc-8.0.13-win32

mysql-connector-odbc-8.0.13-winx64

上面驱动安装 有的电脑会提示缺少vc环境，去微软官网下载这个vc_redist.x86.exe，即可解决。

3. MySQL数据库图形化管理工具

Navicat 8.2.12 For MySQL简体中文版 注册码

Navicat for MySQL Enterprise Edition 8注册码：
NAVJ-W56S-3YUU-MVHV
NAVE-WAGB-ZLF4-T23K

官方下载（中文）：
http://download.navicat.com/download/navicat8_mysql_cs.exe

4. 引用ADO对象。引用方法：EXCEL 的VBE中"工具"菜单→引用→选择"Microsoft Activex Data Objects 2.8 Library"和"Microsoft ActiveX Data Object recordset 2.8 Library"

至此环境搭建完成。但如果要设置成局域网无障碍访问。还需对MYSQL数据库进行设置。

1让mysql数据库允许被远程连接访问

mysql -u root -p

mysql>use mysql;

mysql>select 'host' from user where user='root';

mysql>update user set host = '%' where user ='root';

mysql>flush privileges;

mysql>select 'host' from user where user='root';

第一句是以权限用户root登录

第二句：选择mysql库

第三句：查看mysql库中的user表的host值（即可进行连接访问的主机/IP名称）

第四句：修改host值（以通配符%的内容增加主机/IP地址），当然也可以直接增加IP地址

第五句：刷新MySQL的系统权限相关表

第六句：再重新查看user表时，有修改。。

重起mysql服务即可完成。这时候通过navicat才能连接成功

(1.打开mysql命令行：

2.输入命令

grant all privileges on *.* to root@"%" identified by 'admin' with grant option;

%代表所有的局域网用户都能连，root改成自己的mysql密码

flush privileges;

3.局域网内电脑打开navicat，新建一个连接，输入当作服务器端的ip,密码，就可以连接了。)

解决了远程登录 还得解决解析的问题。这个会导致第一下连接很费时。

MySQL在处理新的线程连接请求时，会尝试进行DNS解析，如果在host cache和Hosts里找不到，处理起来就会很慢，因此最直接简便的方法就是禁用该反向解析功能，可以通过修改MySQL的配置文件实现，Linux下是my.cnf文件，windows下是my.ini文件，在配置文件[mysqld]下新增如下一行代码：

skip-name-resolve

然后重启MySQL服务，再次连接发现已是秒连了。这个方案的不足之处就是，以后在使用grant对用户进行授权时只能使用IP格式，而不能使用主机名称了。

通过修改系统hosts文件也可以实现，举例来说，我想解决192.168.1.100远程连接MySQL服务器缓慢的问题，只需要在MySQL库所在服务器的hosts文件中新增一条记录如下：

192.168.1.100 test.com

保存退出，再次远程连接该MySQL库，同样很快。之所以说绝，是因为这样设置，你添加记录的192.168.1.100远程连接速度变快了，其他主机连接速度跟之前一样慢。该方法同样可以解决ssh远程连接某主机响应很慢的问题，原理一样。

数据库数据如何从ACCESS迁移到MYSQL呢？一种方法是直接用MYSQL官方提供的迁移工具，我采取第二种，先把access数据调出到EXCEL表保存，然后用EXCEL表导入MYSQL。这种方法好在不用去研究ACCESS字段对应MYSQL的哪种字段。

我把MYSQL数据库搭建在原来那台电脑上，MYSQL的链接代码如上，之前的那些增删改vba代码大部分可用，稍微修改下即可使用。到这步，已经完成了把ACCESS数据库迁移升级到MYSQL数据库下，访问速度比之前局域网下ACCESS快了很多。

这时候还是局域网，仍不能广域网访问，离我想要在家办公的目标还有点距离，怎办呢？以前玩过局域网搭建个人BLOG，网站（PHP+MYSQL），那时候是怎样处理外网访问的？，想起花生壳软件，它的功能就是把内网的主机端口映射，以让广域网电脑访问，接下来就是怎样用花生壳软件来让我的EXCEL表能访问到办公室的服务器电脑。

注册了花生壳，然后用其提供的域名端口映像到MYSQL服务器IP的3306端口（MYSQL用的端口，自己可以用Navicat看MYSQL就能看到这个端口），如图 链接成功，这下可以用域名来访问数据库了。回到EXCEL，修改下链接代码。

把SERVER=zjb主机名改为域名地址，后面增加个PORT=端口（花生壳的端口）。成功。

把EXCEL表发给深圳的同事，让其使用，经过测试，能正常的访问到MYSQL上的数据。花生壳免费版有2G的流量可以用，一个月的数据远远够用。

最后运行结果：见视频