- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
回復 13# yangjie - Option Explicit
- Sub 選擇檔名匯入()
- Dim Path1, Str1 As String
- Dim wb As Workbook
- Dim Filt As String
- Dim FilterIndex As Integer
- Dim Title As String
- '---------------------------------
- Dim xlfileName As String, xlFullName As String
- '修改型態 As String xlfileName:沒有路徑
- Dim MyPath
- MyPath = CurDir '紀錄原有的目錄或檔案夾。
- Path1 = Application.ActiveWorkbook.Path
- ' Path1 = "C:\WINDOWS\SYSTEM"
- ChDrive Split(Path1, ":")(0) 'ChDrive 陳述式 改變目前的磁碟機。
- ChDir Path1 'ChDir 陳述式 改變目前的目錄或檔案夾。
- '請注意 ChDir 陳述式會改變現有目錄位置，但不會改變磁碟機位置，
- '例如，如果現在的磁碟機是 C，陳述式將現有目錄切換到磁碟機 D，但是 C 仍然是現有的磁碟機位置：
- Filt = "Excel Files (*.xls),*.xls"
- FilterIndex = 5
- Title = "Select a File for Import"
- xlFullName = Application.GetOpenFilename _
- (FileFilter:=Filt, _
- FilterIndex:=FilterIndex, _
- Title:=Title)
-
- If UCase(xlFullName) = "FALSE" Then
- MsgBox "No file was selected."
- Exit Sub
- End If
- '''''''''''''''''''''''''
- ChDrive Split(MyPath, ":")(0) '改變為原有的磁碟機。
- ChDir MyPath '改變為原有的目錄或檔案夾。
- ''''''''''''''''''''''''''
-
- xlfileName = Split(xlFullName, "\")(UBound(Split(xlFullName, "\")))
- '陣列(上限元素數) 取的檔案名稱沒有路徑
- If IsOpen(xlfileName) <> False Then
- Workbooks(xlfileName).Activate
- Else
- Set wb = Workbooks.Open(xlFullName, True, False)
- End If
- f_bookname2 = ActiveWorkbook.Name
- Windows(f_bookname2).Activate
- Sheets(1).Activate
- End Sub
- Function IsOpen(Fs As String) As Boolean
- IsOpen = False
- For Each W In Windows
- If W.Caption = Fs Then IsOpen = True: Exit For
- Next
- End Function
|
|
發表於 2012-9-1 17:25
| 