EXCEL VBA：檔案處理

ActiveWorkbook.Path 目前活頁簿的路徑

Workbooks.Open Filename:="D:\new.xls"

要刪除檔案，可以使用Kill指令：
Kill "C:\Test\Test.txt"

'要建立目錄之前，通常要保險地先確定目錄不存在：
If Len(Dir("c:\Test\Temp", vbDirectory)) = 0 Then
   MkDir "c:\Test\Temp"
End If

要刪除目錄，則使用RmDir指令：
On Error Resume Next
RmDir "c:\Test\Temp"

'確定檔案是否存在？
set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(ActiveWorkbook.Path & "\" & "FileName.xls") Then
  MsgBox "File Exists!" '檔案存在
Else
  MsgBox "File Not Exists!" '檔案不存在
End If



'確定檔案是否存在？
Private Function FileExists(fname) As Boolean
'   Returns TRUE if the file exists
    Dim x As String
    x = Dir(fname)
    If x <> "" Then FileExists = True _
    Else FileExists = False
End Function

'確定檔案是否存在？
Function FileExist(ByVal strFile As String) As Boolean
    On Error Resume Next
    FileExist = (Len(Dir(strFile)) > 0)
End Function

==========================================
http://support.microsoft.com/kb/184982/zh-tw

下列範例巨集的 Visual Basic for Applications 呼叫函式 FileLocked，並傳遞的完整路徑和檔案的測試名稱。如果函式會傳回最有可能發生，則為 True，錯誤代碼 70 「 權限被拒 」，且檔案目前開啟並鎖定其他處理程序。函數會傳回 False，如果檔案未開啟，而且巨集開啟文件。

    Sub YourMacro()
      Dim strFileName As String

      ' Full path and name of file.
      strFileName = "C:\test.doc"

      ' Call function to test file lock.
      If Not FileLocked(strFileName) Then
         ' If the function returns False, open the document.
         Documents.Open strFileName
      End If
   End Sub

 Function FileLocked(strFileName As String) As Boolean
      On Error Resume Next

      ' If the file is already opened by another process,
      ' and the specified type of access is not allowed,
      ' the Open operation fails and an error occurs.
      Open strFileName For Binary Access Read Write Lock Read Write As #1
      Close #1

      ' If an error occurs, the document is currently open.
      If Err.Number <> 0 Then
         ' Display the error number and description.
         MsgBox "Error #" & Str(Err.Number) & " - " & Err.Description
         FileLocked = True
         Err.Clear
      End If
   End Function
====================================================

Dim w As Window
Dim wb As Workbook

strFilename = "new.xls"   '檔名+副檔名
Set d = CreateObject("Scripting.dictionary")
For Each w In Windows
   d(w.Caption) = w.Caption
Next
If d.exists(strFilename) = False Then Workbooks.Open Filename:="D:\new.xls"

Set wb = Workbooks("new.xls")
wb.Activate
    ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False

    wb.Save
=================================================
 http://edisonx.pixnet.net/blog/post/42267366-vba-%E6%B4%BB%E9%A0%81%E7%B0%BF%28workbooks%29%E7%AE%A1%E7%90%86

' ------------------------------------------------------------
' 查看目前開啟excel檔案數量

Dim OpenCnt as Integer
OpenCnt = Application.Workbooks.Count
' ------------------------------------------------------------
' 依序查已開檔名 - 方法一
    Dim i As Integer
    For i = 1 To Workbooks.Count
        MsgBox i & " " & Workbooks(i).Name
    Next
' ------------------------------------------------------------
' 依序查已開檔名 - 方法二
Dim my Sheet As WorkSheet
For Each mySheet In Worksheets
    MsgBox mySheet.Name
Next mySheet
' ------------------------------------------------------------
' 開啟特定檔案 - 方法一

filename = "C:\VBA\test.xls"
Workbooks.Open filename
' ------------------------------------------------------------
' 開啟特定檔案 - 方法二
Dim filename As String
filename = "C:\VBA\test.xls"

    Dim sn As Object
    Set sn = Excel.Application
    sn.Workbooks.Open filename
    ' sn.Workbooks(filename).Close ' 關閉
    Set sn = Nothing
' ------------------------------------------------------------
' 關閉指定檔案, 不提示訊息    Dim filename As String
    filename = "Test.xls"  ' 這裡只可以給短名,給全名會錯
    ' 假設 Test.xls 已於開啟狀態
    Application.DisplayAlerts = False ' 關閉警告訊息    Workbooks(filename).Close
    Application.DisplayAlerts = True ' 再打開警告訊息
' ------------------------------------------------------------
' 關閉所有開啟檔案, 但留下主視窗Workbooks.Close
' ------------------------------------------------------------
' 關閉 excel 程式
Application.Quit
' ------------------------------------------------------------
' 直接進行存檔
Dim filename As String
filename = "a.xls" ' 只可為短檔名WorkBooks(filename).Save

' ------------------------------------------------------------
' 指定檔名進行另存新檔，並關閉

' 假設要將 "a.xls" 存成 "C:\b.xls"
Application.DisplayAlerts = False ' 關閉警告訊息
Workbooks("a.xls").SaveAs "C:\b.xls" ' 另存新檔
Workbooks("b.xls").Close ' 關閉 b.xlsApplication.DisplayAlerts = True ' 開啟警告訊息
' ------------------------------------------------------------
' 指定當前活頁簿
Dim Caption as String
Caption = "a.xls"
Workbooks(Caption).Activate ' 將視窗切到 a.xls



=====================================================
http://ithelp.ithome.com.tw/question/10119961?tag=ithome.nq

1. Option Explicit  
2. Dim FileAlreadyOpened   '已開啟的檔案  
3. Sub OpenWorkbook()  
4.     Dim FileToOpen      '使用者選擇要開啟的檔案  
5.     FileToOpen = Application.GetOpenFilename(Title:="Please choose a file to import", FileFilter:="Excel Files *.xls (*.xls),")  
6.       
7.     If FileToOpen = False Then  
8.         MsgBox "未指定檔案", vbInformation  
9.         Exit Sub  
10.     Else  
11.         If FileToOpen = FileAlreadyOpened Then  
12.             MsgBox "檔案已開啟", vbInformation  
13.         Else  
14.             Workbooks.Open FileName:=FileToOpen  
15.             FileAlreadyOpened = FileToOpen  
16.         End If  
17.     End If  
18. End Sub