解决Excel自动化(Automation)错误发生意外遇到问题需要关闭问题

今天遇到的问题,打开任意Excel文档,均出现“Microsoft Office Excel 遇到问题需要关闭。我们对此引起的不便表示抱歉。您正在处理的信息有可能丢失。Microsoft Office Excel 可以尝试为您恢复”。

Microsoft Office Excel 遇到问题需要关闭

然后出现“Microsoft Visual Basic 自动化(Automation)错误 发生意外。”的对话框,如下图所示:

继续阅读

解决Excel”该工作簿中含有一种既无法被禁用又无法被签署的宏”导致打不开问题

最近单位收发上来的Excel文件包含有一种宏病毒,常常导致较高安全级别的Excel不能正常打开,会提示如下问题:

该工作簿中含有一种既无法被禁用又无法被签署的宏(Microsoft Excel 4.0 版本宏)。因此,该工作簿无法在“高安全级”下打开。如果要打开此工作簿,请单击“工具”菜单上的“宏”,然后单击“安全性”,再单击“安全性”对话框中的“中”。

之前提示同事使用宏病毒专杀工具进行查杀,基本上被识别的病毒都能被正常清理,但是有些提示不是病毒,或者是残留导致出现这种情况,而且我们又不希望调低安全级别(低安全级别往往会导致病毒乘虚而入),即使你被迫运行了宏程序并正常打开了Excel,若这份Excel再发送给其他人,对于高级别安全设置的Excel依旧打不开,给对方也带来了麻烦。

继续阅读

Excel列编号(序号A、B、C…AA、AB…)英文字母字符生成算法备忘

近期很忙,博客也不怎么更新了,这里记录一个Excel扩展项目中的列编号生成算法实现。在VBA中,Excel的行可以是1、2、3、4、5…,但是Excel的列编号却是形如A、B、C…AA、AB…BA这样的编号模式,我期望将1、2、3、4、5转换为对应的Excel列编号,但对于我这种不太喜欢钻研算法的来说,确实有点棘手。

不过硬着头皮写了一段,并且也勉强能用,代码分享如下:

/**
*
*  buffer 字符缓冲区,用来存储A、B..AA..BB这样的转换结果
*  cch    字符缓冲区容量,最多可以容下字符数
*  num    表示要转换的数值数据
**/
char *TranslateToColumnName(char *buffer, int cch, int num)
{
    const int factor = 26;
    int f1 = (num) / (factor);
    int f2 = (num+1) - (f1) * factor;
    memset(buffer, 0, cch * sizeof(char));
 
    if (f1 == 0) {
        buffer[0] = 'A'+(f2-1);
        buffer[1] = '\0';
    } else {
        buffer[0] = 'A'+(f1-1);
        buffer[1] = 'A'+(f2-1);
    }
    return buffer;
}

有一天我在网上看到了现成的算法《如何将 Excel 列号转换为字母字符》,竟然还是微软官方提供的,看来我是重复造了一个轮子,微软的代码如下(VB实现):

继续阅读

VBA/VBScript批量搜索多个Word或者Excel指定的包含文字

假设我们有多个Word或者Excel文件,现在我们要从这些文件中搜索特定的关键字,比较笨的办法就是一个一个打开,然后“编辑 – 查找”,文件少还好办,如果文件比较多的话,那么打开这么多文件也累得我们够呛。

下面我讲解一个利用VBScript自动批量搜索特定文字的办法,首先还是上面的算法,只不过将人工一个一个打开换成计算机自动打开。实际上这里主要是枚举文件,然后在利用ActiveX控件调用Word.Application或者Excel.Application内部的查找方法来实现相关功能。

对于一份简单的Word文档,基本的查找VBA可以像下面这样实现:

Dim hasFound ' 定义是否找到
Selection.WholeStory
With Selection.Find
    .ClearFormatting
    .MatchWholeWord = False
    .MatchCase = False
    hasFound = .Execute("要查找的文字")
End With

转化为VBScript代码也很容易,多个创建Word.Application并打开Word文件的过程。

继续阅读

解决打开Excel自动弹出Book1.xls的VNN.R9 XL4Test5宏脚本病毒

由于工作上要用到VBA宏,为了确保自己编写的未签名宏脚本能够顺利执行,于是将单位那台破机子的宏安全性设置为低(打开Excel直接执行宏脚本)。没想到今天在Excel上中招了。

打开别人传过来的Excel,接下来异常出现了,先是打开一个存在的Excel时会自动跳出一个新建的Book1.xls工作簿,并且打开的Excel状态始终停留在最后一张Sheet表上。如果这时再打开一份Excel,会提示Book1正处于锁定状态,“VNN.R9”正在编辑类似的消息,而且无论你是否改动表,关闭时都会提醒是否保存,选择后才正常关闭随后Book1.xls也自动关闭。
按照网上的办法尝试着清除下面路径目录下的文件:

Book1正处于锁定状态,“VNN.R9”正在编辑

C:\Program Files\Microsoft Office\OFFICE11\STARTUP
C:\Program Files\Microsoft Office\OFFICE11\XLSTART
C:\Documents and Settings\用户名\Application Data\Microsoft\Excel\XLSTART

继续阅读

Excel/VBA宏打印奇数偶数页并实现正反打印

大家都知道Word里面有一项设置可以让我们选择打印奇数页还是偶数页,这样我们很容易就实现了Word的正反打印,Excel中貌似找不到这个选项,如果你的打印机支持的话,你可以通过页面设置,工作表选项卡,选项按钮,切换到完成方式,可能会有奇数偶数或者正反打印,当然这一切取决于你的打印机驱动是否支持,如果找不到,那该怎么办呢?难道只能手动打印吗?

其实我们可以利用VBA宏变通一下的,首先获取总页数,奇数页打印也就是从1开始,然后1、3、5、7、9,步长为2,利用VB语言可以描述如下:

1
2
3
4
5
' TotalPages就是总页数
' Step是计算步长,这里i每次加2
For i=1 To TotalPages Step 2
  ' 打印 i 页
Next i

这里很明显将打印1、3、5、7、9这样的奇数页,如果是2、4、6、8、10这样的偶数页该怎么办呢,可以看出步长依旧是2,只是起始从2开始了,那么i初始应该设置为2。

通过PrintOut方法就能进行打印了,这里我们默认打印选中的Sheet,所以使用ActiveWindow.SelectedSheets:

继续阅读

用Excel函数公式升位15位、校验18位居民身份证号码

我在培训班讲课的内容,在这里再简单的说一下:

根据国标,18位身份证号码的最后一位是校验码,这位校验码是由前面17位数字经过数学运算得出来的,通过这位校验码,可以检测出18位数字是否存在录入错误,具体的算法如下:

1. 17位数字自身加权求和,公式是S=SUM(Ai*Wi), (0<i<18)这里的i也就是第1~17位的索引,比如A1就是第1个数字,依次类推。Wi是加权因子,对应的也是17位,数值是{7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2},也就是说身份证号码前17位依次与这里17位相乘,最后求和,求和的结果为S。

2. 求余(求模),将S与11求模。Y=MOD(S,11),得到Y。

3. 通过Y的值查找表{1,0,X,9,8,7,6,5,4,3,2},最后得到校验位,为什么会有X,X是罗马数字10,因为10是两位,所以只好用X代替,但是不要认为带有X的身份证号码是错误的哦,还有不要将身份证号码字段设计成数字的哦。
好,有了上面的算法,简单的来说Excel函数也就知道怎么写了:

继续阅读

用Excel函数提取公民身份证号码中信息(出生日期、性别)

大家可能在单位经常要处理一些Excel表格,其中可能接触到身份证号码的处理,比如提取出出生年月日,判断性别和计算年龄等等,对于这些我们都可以通过Excel函数进行处理。

根据身份证号码标准GB11643-1989以及最新的标准GB11643-1999,得知我国公民身份证号码的格式如下:

15位号码:区域地址编码(6位)+出生年(2位)+出生月(2位)+出生日(2位)+数字顺序码(2位)+性别码(1位)
示例:110111 00 01 01 112 (1900年1月1日出生)

18位号码:区域地址编码(6位)+出生年(4位)+出生月(2位)+出生日(2位)+数字顺序码(2位)+性别码(1位)+校验码(1位)
示例:110111 1900 01 01 112 4 (1900年1月1日出生)

好了有了这些信息,下面主要是如何取出年月日,首先介绍一下Excel函数中的MID函数,学过Visual Basic的都应该对这个函数比较眼熟,是的,这个函数主要是用于截取字符串的指定位和指定长度的子字符串,比如aabbccdde这个字符串中bcc就是从第4位开始长度为3的子字符串。

MID函数的原型如下:
MID(text, start_num, num_chars)

其中text是要截取的文本,可以是表格单元格位置,start_num是开始的位数从1开始,num_chars是截取的字符长度(字符个数)。好吧,我们要截取15位身份证号码的年,那么start_num应该是7,num_chars应该是2,同样的截取月份start_num应该是9,num_chars应该是2,依次类推。

假设15位身份证号码存放于A1单元格,那么函数可以如下写法:

1
2
3
4
5
6
7
8
9
=MID(A1, 7, 2)        ' 获取2位的年
' 获取2位的年并与19拼接成4位的年(常用函数)
="19" & MID(A1, 7, 2)
=MID(A1, 9, 2)        ' 获取月份
' 取整月份,这样形如01、02等等月份就变成1、2去除了前导0
=INT(MID(A1, 9, 2))
=MID(A1, 11, 2)       ' 获取日份
' 取整日份,这样形如01、02等等日份就变成1、2去除了前导0
=INT(MID(A1, 11, 2))

这里注意一下第二个函数,其中15位身份证号码截取的是2位年份,为了便于阅读,我们需要将其与字符串19进行拼接,拼接的办法就是使用&字符串连接符。另外INT函数也要注意,就是我们可能不需要前导0的月和日,这时取整有助于去掉这些前置0。

同样的道理截取18位身份证号码,函数可以这样写:

1
2
3
4
5
6
7
=MID(A1, 7, 4)        ' 获取4位的年
=MID(A1, 11, 2)       ' 获取月份
' 取整月份,这样形如01、02等等月份就变成1、2去除了前导0
=INT(MID(A1, 11, 2))
=MID(A1, 13, 2)       ' 获取日份
' 取整日份,这样形如01、02等等日份就变成1、2去除了前导0
=INT(MID(A1, 13, 2))

好了,我们已经分别取出了年月和日,可能读者要问了,如何取出类似于2011.03.12或者2011-03-12这种格式的日期呢,其实很容易,只要将上面的函数与“.”或者“-”进行拼接就可以了,我们以18位身份证号码为例,具体如下:

1
2
3
4
5
6
' 形如2011.03.12这样的日期
=MID(A1, 7, 4) & "." & MID(A1, 11, 2) & "." & MID(A1, 13, 2)
' 形如2011-03-12这样的日期
=MID(A1, 7, 4) & "-" & MID(A1, 11, 2) & "-" & MID(A1, 13, 2)
' 形如2011年03月12日这样的日期
=MID(A1, 7, 4) & "年" & MID(A1, 11, 2) & "月" & MID(A1, 13, 2) & "日"

谈到提取出生日期,很多朋友可能会想到如何计算年龄,这里还是先介绍下Excel的两个函数NOW()和YEAR()。其中NOW()始终代表着当前的时间。而YEAR则是在合法时间量中提取出年份。那么聪明的读者应该想到通过YEAR(NOW())我们就能获取当前的年份,然后通过与身份证号码上截取的4位年份进行相减就可以获得年龄了,好,还是以18位身份证号码为例,下面我们来看公式:

=YEAR(NOW())-MID(A1, 7, 4)

很简单吧,但是有读者可能需要一种更复杂的计算机制来计算年龄,比如要精确到月,出生月小于当前月的就以当前年份相减,否则就按当前年份减去1,然后再相减。这里我们需要用到IF函数,具体格式是 IF(条件, 为真, 为假),另外刚刚我们了解了取年份函数YEAR(),现在再介绍个取月份函数MONTH(),最后具体函数如下(18位身份证号码):

=IF(INT(MONTH(NOW()))>=INT(MID(A1, 11, 2)), YEAR(NOW()), YEAR(NOW())-1)-MID(A1, 7, 4)

也可以写成

=(YEAR(NOW())-MID(A1, 7, 4))-IF(INT(MONTH(NOW()))>=INT(MID(A1, 11, 2)), 0, 1)

好的,关于出生日期及年龄计算就先介绍到这里,下面我将给大家介绍如何判断性别。

根据先前讲的公民身份证号码格式,我们知道对于15位身份证号码,性别是看最后一位,对于18位身份证号码性别看倒数第二位,如果该位为奇数就为男性,如果为偶数就为女性,我们可以通过MID函数提取出该位,但是如何判断奇偶呢,其实这里用到了Excel的另一个函数MOD进行求余数,我们可以通过将该位与2进行求余,为0就为女性,为1就为男性,以18位身份证为例:

=MOD(MID(A1, 17, 1), 2)

如果我们需要将显示文本变得更友好,可以通过IF函数进行判断,那么原来的公式更改如下:

=IF(MOD(MID(A1, 17, 1), 2)=1, “男”, “女”)

好的,我关于用Excel公式函数提取公民身份证号码中信息的介绍到这里就结束了,想必大家在实际操作中能发掘出更好的方法吧。

2011年8月16日更新

部分读者反映,取年龄会计算不正确,实际上计算年龄的一个重点就是NOW()函数,NOW()函数返回的是当前计算机的时间,如果NOW()函数返回的时间不正确,计算结果当然也就不正确了,只有一种情况导致NOW()函数返回错误的时间,那就是你电脑上的时间设置错了,当然你也可以将NOW()替换成时间字符串,从而写死时间。

VBScript/VBA使用UsedRange确定Excel的有效可读数据区域

Excel的有效可读数据区域是指拥有数据的最大范围,比如只有100行50列拥有数据区域,那么其Range就是100*50的范围。正常情况下,我们要如何编程获得这个区域呢,其实我们可以借助于UsedRange这个方法,下面举个简单的例子,比如用VBScript或者VBA将有效数据区域的行高度自动(自动调整行高),那么可以参考下面的VBScript代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Option Explicit
 
Const MSEXCEL_FILENAME = "Excel文件路径"
Sub VBMain()
  Dim xlApp, WorkBooks, Sheet
  Set xlApp = WSH.CreateObject("Excel.Application")
  xlApp.Visible = False
  Set WorkBooks = xlApp.Workbooks.Open(MSEXCEL_FILENAME)
 
  Set Sheet = WorkBooks.Worksheets(1)  '打开第一个工作表
  ' 也可以打开上次保存时打开的活动工作表
  ' Set Sheet = WorkBooks.ActiveSheet  打开活动的工作表
  Sheet.UsedRange.EntireRow.AutoFit ' 自动调整行高
  Set Sheet = Nothing
 
  WorkBooks.Close
  Set WorkBooks = Nothing
  xlApp.Quit
  Set xlApp = Nothing
End Sub
 
Call VBMain()
WSH.Quit()

那么如何获取有效区域的行数和列数呢,参照上面的代码其实可以使用Sheet.UsedRange.Rows.Count或者Sheet.UsedRange.Columns.Count分别取得。

VBScript实现Word和Excel的打印

只要是安装了Microsoft Office的Word和Excel电脑,微软都为我们提供了Word和Excel的COM组件,方便我们以COM对象访问的形式操纵Word或Excel文件,今天介绍个小功能,那就是打印。

首先是Word的打印,基本上的流程就是创建Word.Application对象,然后调用Documents.Open的方式打开doc文件,然后执行PrintOut方法打印文件,然后Close关闭,最后退出并销毁Word.Application对象。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Option Explicit
 
Const MSWORD_FILENAME = "Word文件路径"
Sub VBMain()
  Dim wdApp, Doc
  Set wdApp = WSH.CreateObject("Word.Application")
  wdApp.Visible = False
  Set Doc = wdApp.Documents.Open(MSWORD_FILENAME)
  Doc.PrintOut
  Doc.Close
  Set Doc = Nothing
  wdApp.Visible = True
  wdApp.Quit
  Set wdApp = Nothing
End Sub
 
Call VBMain()
WSH.Quit()

最后介绍下Excel的打印,同样的道理,创建对象,打开要打印的工作簿,选择要打印的工作表,打印,然后关闭工作表,退出并销毁对象。同样的一些操作可以参考Excel的VBA手册《Microsoft Excel Visual Basic参考》

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Option Explicit
 
Const MSEXCEL_FILENAME = "Excel文件路径"
Sub VBMain()
  Dim xlApp, WorkBooks, Sheet
  Set xlApp = WSH.CreateObject("Excel.Application")
  xlApp.Visible = False
  Set WorkBooks = xlApp.Workbooks.Open(MSEXCEL_FILENAME)
 
  Set Sheet = WorkBooks.Worksheets(1)  '打开第一个工作表
  ' 也可以打开上次保存时打开的活动工作表
  ' Set Sheet = WorkBooks.ActiveSheet  打开活动的工作表
  Sheet.PrintOut
  Set Sheet = Nothing
 
  WorkBooks.Close
  Set WorkBooks = Nothing
  xlApp.Quit
  Set xlApp = Nothing
End Sub
 
Call VBMain()
WSH.Quit()

对于想批量打印的朋友可以参考我这篇文章《采用插件机制的批量文件扫描及进程处理工具》