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
' 查看目前開啟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
- Option Explicit
- Dim FileAlreadyOpened '已開啟的檔案
- Sub OpenWorkbook()
- Dim FileToOpen '使用者選擇要開啟的檔案
- FileToOpen = Application.GetOpenFilename(Title:="Please choose a file to import", FileFilter:="Excel Files *.xls (*.xls),")
- If FileToOpen = False Then
- MsgBox "未指定檔案", vbInformation
- Exit Sub
- Else
- If FileToOpen = FileAlreadyOpened Then
- MsgBox "檔案已開啟", vbInformation
- Else
- Workbooks.Open FileName:=FileToOpen
- FileAlreadyOpened = FileToOpen
- End If
- End If
- End Sub
