Excel-VBA操作檔案四大方法(一）

 

本文主要介紹四種常用的方法：
1、利用Excel對象來處理檔案；
2、利用VBA檔案處理語句來處理檔案；
3、利用FileSystemObject對象來處理檔案；
4、利用API函數來處理檔案。

當然對於資料庫檔案，還可以利用ADO＋SQL的方法操作，不過論壇已經有前輩詳細介紹過此類方法，本文就不再重複了。

一、利用Excel對象來處理檔案

利用Excel對象內建的方法來操作檔案是最方便，也是最簡單的。
我們主要利用Workbooks集合和Workbook對象的方法來操作檔案。

1、開啟Excel檔案

我們可以用Workbooks.Open方法開啟一個Excel活頁簿。
Workbooks.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)
  其中FileName是必選的參數，表示要開啟的活頁簿名，如果沒有指定路徑，則代表當前路徑。另外14個是選擇性參數，除了密碼參數，其他的一般很少用。具體的含義可以參看VBA的協助。
例：
   Workbooks.Open "F:/test.xls"
可以開啟F盤的test.xls檔案。

2、開啟文字檔

  使用Open方法也可以開啟文字檔，但建議使用OpenText方法。此方法是載入一個文字檔，並將其作為包含單個工作表的活頁簿進行分列處理，然後在此工作表中放入經過分列處理的文字檔資料。完整文法如下：
Workbooks.OpenText(FileName, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local)

關於以上參數的具體含義可以參看VBA的協助，這裡就不重複了。在實際的編程中，一般無需對這些複雜的參數進行處理。可以通過錄製宏來得到開啟一個文字檔的VBA代碼。具體方法就是選擇“檔案——開啟”，然後選擇開啟文字檔，就會出現文本匯入嚮導，一步一步執行完，直到文本開啟後，停止錄製。
以下是錄製宏得到的代碼:
Sub Macro1()
'
' Macro1 Macro
' 宏由 MC SYSTEM 錄製，時間: 2007-3-29
'

'
    Workbooks.OpenText Filename:="F:/CallWindowProc.txt", Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
End Sub

在實際編程中只要做相應的修改就可以使用了。

3、開啟其他檔案

利用Excel對象還可以開啟XML檔案和一些資料庫（如Access）檔案，對應XML檔案，需要Excel2003以上的版本。

OpenXML方法的文法如下：
Workbooks.OpenXML(Filename, Stylesheets, LoadOption)
FileName      String 類型，必需。要開啟的檔案名稱。

Stylesheets      Variant 類型，可選。單個值或值的數組，用於指定要應用哪些 XSL 轉換 (XSLT) 樣式表處理指示。

LoadOption    Variant 類型，轉換。指定 Excel 開啟 XML 資料檔案的方式。可為 XlXmlLoadOption 常量之一。

XlXmlLoadOption 可為以下 XlXmlLoadOption 常量之一：
xlXmlLoadImportToList 將 XML 資料檔案的內容置於 XML 列表中。
xlXmlLoadMapXml 在“XML 結構”工作窗格中顯示 XML 資料檔案的架構。
xlXmlLoadOpenXml 開啟 XML 資料檔案。檔案的內容將展開。
xlXmlLoadPromptUser 提示使用者選擇開啟檔案的方式。

樣本
下面的代碼開啟了 XML 資料檔案“customers.xml”並在 XML 列表中顯示了此檔案的內容。

Sub UseOpenXML()
    Application.Workbooks.OpenXML _
        Filename:="customers.xml", _
        LoadOption:=xlXmlLoadImportToList
End Sub

OpenDatabase 方法文法如下：
Workbooks.OpenDatabase(FileName, CommandText, CommandType, BackgroundQuery, ImportDataAs)

FileName      String 類型，必需。連接字串。

CommandText      Variant 類型，可選。查詢的命令文本。

CommandType      Variant 類型，可選。查詢的命令類型。以下是可用的命令類型：Default、SQL 和 Table。

BackgroundQuery      Variant 類型，可選。查詢的背景。

ImportDataAs      Variant 類型，可選。確定查詢的格式。

樣本
本樣本中，Excel 開啟了“northwind.mdb”檔案。

Sub OpenDatabase()

Workbooks.OpenDatabase FileName:="C:/northwind.mdb"

End Sub

4、儲存檔案

檔案的儲存使用Workbook對象的Save或SaveAs方法。
Save方法使用簡單，文法為
expression.Save，expression是某個Workbook對象。
如：ActiveWorkbook.Save
即儲存當前活動活頁簿。

如果是第一次儲存活頁簿或要另存新檔，請使用 SaveAs 方法為該檔案指定檔案名稱。
其文法為：
expression.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)

具體參數含義可參看VBA協助，使用都比較簡單。
樣本
本樣本建立一個活頁簿，提示使用者輸入檔案名稱，然後儲存該活頁簿。

Set NewBook = Workbooks.Add
Do
    fName = Application.GetSaveAsFilename
Loop Until fName <> False
NewBook.SaveAs Filename:=fName

Application.GetSaveAsFilename為調出標準的“另存新檔”對話方塊，擷取使用者檔案名稱，但並不真正儲存任何檔案，然後使用代碼儲存檔案。還有Application.GetOpenFileName可以調出標準的“開啟”對話方塊。

5、關閉檔案

關閉檔案可以使用Workbooks集合或Workbook對象的 Close 方法。前者是關閉所有開啟的活頁簿，後者關閉特定的活頁簿。
Workbook對象的 Close 方法文法為：
expression.Close(SaveChanges, Filename, RouteWorkbook)

SaveChanges參數表示是否儲存更改，對許多不需要更改的操作，可設定為False以免彈出儲存變更提示的對話方塊。
FileName 可選。以此檔案名稱儲存所做的更改。
RouteWorkbook  可選。如果指定活頁簿不需要傳送給下一個收件者（沒有傳送名單或已經傳送），則忽略該參數。

樣本
本樣本關閉 Book1.xls，並放棄所有對此活頁簿的更改。

Workbooks("BOOK1.XLS").Close SaveChanges:=False
  
本樣本關閉所有開啟的活頁簿。如果某個開啟的活頁簿有改變，Microsoft Excel 將顯示詢問是否儲存更改的對話方塊和相應提示。

Workbooks.Close

6、綜合執行個體

假如F盤有一個Excel檔案test.xls，現在有另一個Excel檔案要訪問test.xls的資料，我們來看用VBA代碼如何操作。代碼如下：
Public Sub test()
Application.ScreenUpdating = False
Workbooks.Open "f:/test.xls"
ThisWorkbook.Sheets(1).Range("b1") = ActiveWorkbook.Sheets(1).Range("a2")
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub
首先關閉螢幕重新整理，是為了防止test.xls在開啟時被看見(有時候還是看的見）。開啟後，見test.xls的Sheet1的儲存格A2中的值賦給當前活頁簿的Sheet1的儲存格B2，然後關閉test.xls。
當要開啟的活頁簿不確定的時候，可以通過調用開啟對話方塊來讓使用者自己選擇。
可改為如下：
Public Sub test()
Application.ScreenUpdating = False
Dim Filename as String
Filename = Application.GetOpenFileName
Workbooks.Open Filename
ThisWorkbook.Sheets(1).Range("b1") = ActiveWorkbook.Sheets(1).Range("a2")
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub

7、總結

利用Excel對象的方法進行檔案操作是最簡單，也是最方便的，適合初學者。對於Excel檔案格式，如果我們僅僅是讀取其表格中的內容，這種方法也是首選。對於文字檔的操作，使用第二種方法比較方便，若要將文本轉換成表格，那麼使用此方法也是合適的。

 

二、利用VBA檔案處理語句來處理檔案

    VBA包含了許多用於檔案操作的語句和函數，可以滿足絕大多數情況下的檔案操作要求。下面我們按照操作目的進行一一介紹。

（一）檔案處理
   
1.Name 語句

文法：Name oldpathname As newpathname

功能：重新命名一個檔案、目錄、或檔案夾，移動一個檔案。

說明：在一個已開啟的檔案上使用 Name，將會產生錯誤。進行檔案操作時，一定要注意錯誤處理。

樣本：
On Error Resume Next    '錯誤處理
Name "f:/TEST.xls" As "f:/TEST123.xls"  '重新命名
Name "f:/TEST.xls" As "f:/dll/TEST.xls"  '移動檔案
Name "f:/TEST.xls" As "d:/TEST123.xls"  '跨磁碟機移動並重新命名檔案

注意：Name不能移動一個目錄或檔案夾。

2、FileCopy 語句

文法：FileCopy source, destination

功能：複製一個檔案。

說明：如果對一個已開啟的檔案使用 FileCopy 語句，則會產生錯誤。

樣本：
FileCopy "f:/TEST.xls", "e:/TEST.xls"  '從F盤複製TEST.xls到E盤

3、Kill 語句

文法：Kill pathname

功能：從磁碟中刪除檔案。

說明：Kill 支援多字元 (*) 和單字元 (?) 的統配符來指定多重檔案。如果使用 Kill 來刪除一個已開啟的檔案，則會產生錯誤。

樣本：
Kill "f:/TEST.xls"  ’刪除F盤的TEST.xls檔案
Kill "f:/*.xls"   ' 刪除F盤所有xls檔案

4、GetAttr 函數

文法：GetAttr(pathname)

功能：擷取一個檔案、目錄、或檔案夾的屬性。返回一個 Integer值。

傳回值

由 GetAttr 返回的值，是下面這些屬性值的總和：

常數    值   描述
vbNormal   0   常規
vbReadOnly   1   唯讀
vbHidden   2   隱藏
vbSystem   4  系統檔案
vbDirectory   16   目錄或檔案夾
vbArchive   32   封存檔案
vbalias   64   指定的檔案名稱是別名。只在Macintosh中可用。

說明：若要判斷是否設定了某個屬性，在 GetAttr 函數與想要得知的屬性值之間使用 And 運算子與逐位比較。如果所得的結果不為零，則表示設定了這個屬性值。

樣本：
Debug.Print GetAttr("F:/test.txt") '若為封存檔案，在立即視窗可看到值為32
Debug.Print GetAttr("F:/test.txt") '將屬性—進階—可封存檔案的勾去掉後，值為0

為判斷一個檔案是否唯讀，可用下法:
Debug.Print GetAttr("F:/test.txt") And vbReadOnly
若值非零，說明時唯讀。

5、SetAttr 語句

文法：SetAttr pathname, attributes

功能：為一個檔案設定屬性。

說明：如果想要給一個已開啟的檔案設定屬性，則會產生執行階段錯誤。

樣本：
SetAttr"F:/test.txt", vbHidden       ' 設定隱藏屬性。
SetAttr"F:/test.txt", vbHidden + vbReadOnly      ' 設定隱藏並唯讀。

6、FileLen 函數

文法：FileLen(pathname)

功能：擷取一個檔案的長度，單位是位元組。

說明：當調用 FileLen 函數時，不需要開啟檔案，如果所指定的檔案已經開啟，則返回的值是這個檔案在開啟前的大小。

7、FileDateTime 函數

文法：FileDateTime(pathname)

功能：擷取一個檔案被建立或最後修改後的日期和時間。

樣本：
Debug.Print FileDateTime("F:/TEST.xls")  '在立即視窗可看到2007-3-29 19:28:27

（二）目錄處理

1、CurDir 函數

文法：CurDir[(drive)]

功能：返回當前的路徑。

說明：drive 參數是可選的，它指定一個存在的磁碟機。如果沒有指定磁碟機，或 drive 是零長度字串 ("")，則 CurDir 會返回當前磁碟機的路徑。

樣本：
Debug.Print CurDir      ' 返回“C:/Documents and Settings/yc/My Documents”。
Debug.Print CurDir("C")     ' 返回“C:/Documents and Settings/yc/My Documents”。
Debug.Print CurDir("D")     ' 返回“D:/”。

2、ChDir 語句

文法：ChDir path

功能：改變當前的目錄或檔案夾。

說明：ChDir 語句改變預設目錄位置，但不會改變預設磁碟機位置。預設磁碟機一般是C。

樣本：

ChDir "D:/temp"
Debug.Print CurDir      ' 返回“C:/Documents and Settings/yc/My Documents”。
Debug.Print CurDir("D")     ' 返回“D:/temp”。
與上例比較，此時D盤的目前的目錄已經變為“D:/temp”，但是預設磁碟機還是C。

3、ChDrive 語句

文法：ChDrive drive

功能：改變當前的磁碟機。

說明：如果使用零長度的字串 ("")，則當前的磁碟機將不會改變。如果 drive 參數中有多個字元，則 ChDrive 只會使用首字母。

樣本：
ChDrive "D"
ChDir "D:/temp"
Debug.Print CurDir      ' 返回“D:/temp”。
Debug.Print CurDir("D")     ' 返回“D:/temp”。

與上例比較，用CurDir返回的是“D:/temp”，當前磁碟機已經變為D了。

4、Dir 函數

文法：Dir[(pathname[, attributes])]
      兩個參數都是可選的，attributes表示檔案屬性。
功能：返回一個檔案名稱、目錄名或檔案夾名稱，它必須與指定的模式或檔案屬性、或磁碟卷標相匹配。

說明：在第一次調用 Dir 函數時，必須指定 pathname，否則會產生錯誤。如果也指定了檔案屬性，那麼就必須包括 pathname。

Dir 會返回匹配 pathname 的第一個檔案名稱。若想得到其它匹配 pathname 的檔案名稱，再一次調用 Dir，且不要使用參數。如果已沒有合乎條件的檔案，則 Dir 會返回一個零長度字串 ("")。一旦傳回值為零長度字串，並要再次調用 Dir 時，就必須指定 pathname，否則會產生錯誤。不必訪問到所有匹配當前 pathname 的檔案名稱，就可以改變到一個新的 pathname 上。但是，不能以遞迴方式來調用 Dir 函數。以 vbDirectory 屬性來調用 Dir 不能連續地返回子目錄。

樣本：
Debug.Print Dir("F:/TEST.xls")  ’返回"TEST.xls"
Debug.Print Dir("F:/*.xls")  ’返回按條件第一個找到的檔案名稱。
Debug.Print Dir("F:/*.txt",vbReadOnly) ’返回第一個唯讀txt檔案

以下過程可顯示C盤根目錄下的所有目錄.
Sub DirC()

MyPath = "c:/"  
MyName = dir(MyPath, vbDirectory)     ' 找尋第一項。
Do While MyName <> ""      ' 開始迴圈。
    ' 跳過當前的目錄及上層目錄。
    If MyName <> "." And MyName <> ".." Then
        ' 使用位比較來確定 MyName 代表一目錄。
        If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then
            Debug.Print MyName     ' 如果它是一個目錄，將其名稱顯示出來。
        End If
    End If
    MyName = dir    ' 尋找下一個目錄。
Loop

End Sub

以下過程利用遞迴可以尋找目錄和子目錄下的所有檔案。

Public Sub FindFile(mPath As String, Optional sFile As String = "")
On Error Resume Next
Dim s As String, sDir() As String
Dim i As Long, d As Long

If Right(mPath, 1) <> "/" Then
    mPath = mPath & "/"
End If
'尋找目錄下的檔案
s = dir(mPath & sFile, vbArchive + vbDirectory + vbHidden + vbNormal + vbReadOnly + vbSystem)
Do While s <> ""
   Debug.Print mPath & s
   s = dir
Loop
'尋找目錄下的子目錄
s = dir(mPath, vbArchive + vbDirectory + vbHidden + vbNormal + vbReadOnly + vbSystem)
Do While s <> ""
    If s <> "." And s <> ".." Then
        If (GetAttr(mPath & s) And vbDirectory) = vbDirectory Then
        d = d + 1
        ReDim Preserve sDir(d)
        sDir(d) = mPath & s
        End If
    End If
    s = dir
Loop

'開始遞迴       
For i = 1 To d
    FindFile sDir(d) & "/"
Next

End Sub

5、MkDir 語句

文法：MkDir path

功能：建立一個新的目錄或檔案夾。

說明：path 可以包含磁碟機。如果沒有指定磁碟機，則 MkDir 會在當前磁碟機上建立新的目錄或檔案夾。

樣本：
MkDir "MYDIR"    '在目前的目錄建立新的目錄或檔案夾。

6、RmDir 語句

文法：RmDir path

功能：刪除一個存在的目錄或檔案夾。

說明：如果想要使用 RmDir 來刪除一個含有檔案的目錄或檔案夾，則會發生錯誤。在試圖刪除目錄或檔案夾之前，先使用 Kill 語句來刪除所有檔案。

樣本：
RmDir "MYDIR"      ' 將 MYDIR 刪除。