关于用EXCEL自定义函数操作EXCEL文件遇到的两个问题

smooth · 发表于 2013-5-3 11:15:18

本人每月都要从用友的报表系统中，导出6个EXCLE文件，然后把数据复制到另外一个EXCEL文件的同一工作表相应的地方。这种操作属于纯数字操作，不涉及数据合并，也不涉及到复制公式等问题，简单而繁琐，且容易复制粘贴到错误的地方。于是我写了一个工具用来解决这个种简单的重复劳动，但是发现2个无法解决的问题，请教论坛里各位老师。问题一：当目标EXCEL是*.xls格式的文件时，写入正常，可是如果目标EXCEL文件是*.xlsx格式时，则软件只写如一个源文件的数据，其他5个源文件数据无法写入。问题二：如果源目录中存在以纯数字作为文件名的EXCEL文件，则软件会报错，循环不正常，数据无法正常写入目标EXCEL。

Global Const $xlSheetVisible = -1

MsgBox(4096, "严重警告！", "接下来你将使用本工具把“总部”,“敏泰”,“数码”,“纺机”,“博尚”,“三笠”六张利润表的数据拷贝到你接下来选择的目标文件中。请确认你选择的目标文件是你准备好的空白文件，否则数据覆盖后将不可恢复！", "")
Global $SeleteF = FileOpenDialog("请选择目标文件", "", "Excel文件 (*.xls;*.xlsx)", 1 + 4)
If @error = 1 Then Exit
If MsgBox(1, "提示", "你确定要把所有项目及总部利润表数据复制到：" & $SeleteF & "吗？", "") = 2 Then Exit
Global $oExcel_GL = _ExcelBookOpen($SeleteF)
_ExcelSheetActivate($oExcel_GL, "项目汇总")
Local $sFolder = FileSelectFolder("请选择源文件存放路径.", "", 2);Local
Local $hSearch = FileFindFirstFile($sFolder & "\*.xls")
If $hSearch = -1 Then Exit
While 1
        Local $sFile = FileFindNextFile($hSearch);Local
        If @error Then ExitLoop
        $gFileNanme = _WinAPI_PathFindFileName($SeleteF);$gFileNanme = StringRight($SeleteF, StringLen($SeleteF) - stringinstr($SeleteF, '\', 0,-1));这里用两种方法从路径中提取文件名，一种是用AU3基本函数，另外一中是用UDF函数，都可以的。
        If $sFile <> $gFileNanme Then
                Local $oExcel = _ExcelBookOpen($sFolder & "" & $sFile)
                $close = $oExcel
                For $cl In _ExcelSheetList($oExcel)
                        _ExcelSheetActivate($oExcel, $cl)
                        Switch _ExcelReadCell($oExcel, 1, 1)
                                Case "利润表（总部）"
                                        _Copy("利润表（总部）", 4)
                                Case "利润表（敏泰）"
                                        _Copy("利润表（敏泰）", 8)
                                Case "利润表（数码）"
                                        _Copy("利润表（数码）", 12)
                                Case "利润表（纺机）"
                                        _Copy("利润表（纺机）", 16)
                                Case "利润表（博尚）"
                                        _Copy("利润表（博尚）", 20)
                                Case "利润表（三笠）"
                                        _Copy("利润表（三笠）", 24)
                                Case Else
                                        ContinueLoop
                        EndSwitch
                Next
        EndIf
        _Excelbookclose( $close, 0)
WEnd
FileClose($hSearch)

_ExcelBookClose($oExcel_GL, 1)
_ExcelBookClose($oExcel, 0)

Func _Copy($str, $num)
        For $i = 6 To 22
                If $i <> 8 And $i <> 18 And $i <> 21 Then
                        _ExcelWriteCell($oExcel_GL, _ExcelReadCell($oExcel, $i, 3), $i - 2, $num) ;写入内容到单元格
                EndIf
        Next
EndFunc   ;==>_Copy

;
;
;UDF函数
;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
;
Func _WinAPI_PathFindFileName($sPath);从路径中提取文件名

        Local $Ret = DllCall('shlwapi.dll', 'wstr', 'PathFindFileNameW', 'wstr', $sPath)

        If @error Then
                Return SetError(1, 0, '')
        EndIf
        Return $Ret[0]
EndFunc   ;==>_WinAPI_PathFindFileName

Func _ExcelBookOpen($sFilePath, $fVisible = 1, $fReadOnly = False, $sPassword = "", $sWritePassword = "")
        Local $oExcel = ObjCreate("Excel.Application")
        If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
        If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
        If $fVisible > 1 Then $fVisible = 1
        If $fVisible < 0 Then $fVisible = 0
        If $fReadOnly > 1 Then $fReadOnly = 1
        If $fReadOnly < 0 Then $fReadOnly = 0
        With $oExcel
                .Visible = $fVisible
                If $sPassword <> "" And $sWritePassword <> "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, $sWritePassword)
                If $sPassword = "" And $sWritePassword <> "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, Default, $sWritePassword)
                If $sPassword <> "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, Default)
                If $sPassword = "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly)

                ; Select the first *visible* worksheet.
                For $i = 1 To .ActiveWorkbook.Sheets.Count
                        If .ActiveWorkbook.Sheets($i).Visible = $xlSheetVisible Then
                                .ActiveWorkbook.Sheets($i).Select()
                                ExitLoop
                        EndIf
                Next
        EndWith
        Return $oExcel
EndFunc   ;==>_ExcelBookOpen

Func _ExcelSheetActivate($oExcel, $vSheet)
        If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
        If IsNumber($vSheet) Then
                If $oExcel.ActiveWorkbook.Sheets.Count < $vSheet Then Return SetError(2, 0, 0)
        Else
                Local $fFound = 0
                Local $aSheetList = _ExcelSheetList($oExcel)
                For $xx = 1 To $aSheetList[0]
                        If $aSheetList[$xx] = $vSheet Then $fFound = 1
                Next
                If Not $fFound Then Return SetError(3, 0, 0)
        EndIf
        $oExcel.ActiveWorkbook.Sheets($vSheet).Select()
        Return 1
EndFunc   ;==>_ExcelSheetActivate

Func _ExcelSheetList($oExcel)
        If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
        Local $iTemp = $oExcel.ActiveWorkbook.Sheets.Count
        Local $aSheets[$iTemp + 1]
        $aSheets[0] = $iTemp
        For $xx = 1 To $iTemp
                $aSheets[$xx] = $oExcel.ActiveWorkbook.Sheets($xx).Name
        Next
        Return $aSheets
EndFunc   ;==>_ExcelSheetList

Func _ExcelReadCell($oExcel, $sRangeOrRow, $iColumn = 1)
        If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
        If Not StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then
                If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
                If $iColumn < 1 Then Return SetError(2, 1, 0)
                Return $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value
        Else
                Return $oExcel.Activesheet.Range($sRangeOrRow).Value
        EndIf
EndFunc   ;==>_ExcelReadCell

Func _ExcelWriteCell($oExcel, $sValue, $sRangeOrRow, $iColumn = 1)
        If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
        If Not StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then
                If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
                If $iColumn < 1 Then Return SetError(2, 1, 0)
                $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value = $sValue
                Return 1
        Else
                $oExcel.Activesheet.Range($sRangeOrRow).Value = $sValue
                Return 1
        EndIf
EndFunc   ;==>_ExcelWriteCell

Func _ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0)
        If Not IsObj($oExcel) Then Return SetError(1, 0, 0)

        Local $sObjName = ObjName($oExcel)

        If $fSave > 1 Then $fSave = 1
        If $fSave < 0 Then $fSave = 0
        If $fAlerts > 1 Then $fAlerts = 1
        If $fAlerts < 0 Then $fAlerts = 0

        ; Save the users specified settings
        Local $fDisplayAlerts = $oExcel.Application.DisplayAlerts
        Local $fScreenUpdating = $oExcel.Application.ScreenUpdating
        ; Make necessary changes
        $oExcel.Application.DisplayAlerts = $fAlerts
        $oExcel.Application.ScreenUpdating = $fAlerts

        Switch $sObjName
                Case "_Workbook"
                        If $fSave Then $oExcel.Save()
                        ; Check if multiple workbooks are open
                        ; Do not close application if there are
                        If $oExcel.Application.Workbooks.Count > 1 Then
                                $oExcel.Close()
                                ; Restore the users specified settings
                                $oExcel.Application.DisplayAlerts = $fDisplayAlerts
                                $oExcel.Application.ScreenUpdating = $fScreenUpdating
                        Else
                                $oExcel.Application.Quit()
                        EndIf
                Case "_Application"
                        If $fSave Then $oExcel.ActiveWorkBook.Save()
                        $oExcel.Quit()
                Case Else
                        Return SetError(1, 0, 0)
        EndSwitch

        Return 1
EndFunc   ;==>_ExcelBookClose

请教各位，谢谢！

zcsgamer · 发表于 2013-5-3 11:52:18

excel是不是03版

smooth · 发表于 2013-5-3 14:43:13

Excel 是2010版本的。

kevinch · 发表于 2013-5-4 21:14:25

程序执行过程中关键的地方输出日志看下，否则真看不出问题出在哪

smooth · 发表于 2013-5-6 08:39:43

程序执行过程中关键的地方输出日志看下，否则真看不出问题出在哪
kevinch 发表于 2013-5-4 21:14

我不晓得日志文件在哪里。纯数字作为文件名操作出现问题的情况，可能和测试环境有很大的关系，我把用于测试的表格都删除掉，再复制备份过来，这个问题又不存在了。

但往*.XLSX格式的EXCEL文件写入数据的问题，还是没有找到根源。

kk_lee69 · 发表于 2013-5-6 10:19:42

回复 1# smooth

問題出在哪裡  我是不清楚  但是下面兩個方式  你測試看看也許可以解決你的問題

1. 不要讓對方準備目標文件檔......直接用 EXCEL UDF 開新檔然後設好格式  直接存XLS  另存

2. 不要用  FileFindFirstFile  這個方式抓取檔案名稱，改用 _FileListToArray  取得檔案清單的數組(陣列) 然後再 FOR  NEXT 去循環

試看看也許可以逃過你之前的那些問題

xms77 · 发表于 2013-5-8 11:47:36

回复 4# kevinch
我也遇到类似的问题，$oExcel第一次写入时可以，到第二次写入时，$oExcel都已经不是对象了，但是过程中我没有使用_ExcelBookClose($oExcel, 1, 0)啊？头疼啊~~

kevinch · 发表于 2013-5-8 14:13:12

回复 7# xms77
有具体文件我可以写一写测试一下，类似的情况我还没有遇到过

smooth · 发表于 2013-5-8 22:05:44

还有，使用这个工具时，会出现一些莫名其妙的问题，比如只能打开一个EXCLE文件，再打开另外一个时，前一个就自动关闭，不出现任何提示。把电脑重启之后，又恢复正常。这个问题我遇到了两次，看来并非偶然事件。

smooth · 发表于 2013-5-8 22:05:59

还有，使用这个工具时，会出现一些莫名其妙的问题，比如只能打开一个EXCLE文件，再打开另外一个时，前一个就自动关闭，不出现任何提示。把电脑重启之后，又恢复正常。这个问题我遇到了两次，看来并非偶然事件。

		自动登录	找回密码
密码			加入

[AU3基础] 关于用EXCEL自定义函数操作EXCEL文件遇到的两个问题

相关帖子