用VBA列出檔案所有資訊

以下VBA會列出所有檔案資訊

(不論是否該檔有沒有開啟,都可以得到正確檔案資訊)

Sub getDetailsOfFile()
Dim myShl As New Shell
Dim curFolder As Folder
Dim theItm As FolderItem
Dim Fn As Variant
Dim theTitle As String
Dim outStr As String
Dim i As Long
Fn = Application.GetOpenFilename
If Fn = "False" Then Exit Sub
Set curFolder = myShl.Namespace(CurDir)
Fn = Split(Fn, "\"): Fn = Fn(UBound(Fn))
With curFolder
theTitle = .GetDetailsOf(0, i)
Do While theTitle <> ""
outStr = outStr & theTitle & ": " & vbTab & .GetDetailsOf(.Items.Item(Fn), i) & vbCrLf
Debug.Print i & .GetDetailsOf(.Items.Item(Fn), i)
i = i + 1
theTitle = .GetDetailsOf(0, i)
Loop
End With
Set myShl = Nothing
MsgBox outStr
End Sub

請設定引用項目Microsoft Shell Controls and Automation
VBE=>工具=>設定引用項目=>勾選Microsoft Shell Controls and Automation

VBA 轉碼 UTF-8轉Big5

VBA 轉碼 UTF-8轉Big5

有些網頁的格式是以UTF-8編碼, 透過VBA以URLDownloadToFile下載該網頁後,若要進一步處理,會遇到中文變成亂碼的問題,用ADODB.Stream處理後可以轉成Big5碼.

Function UTF8ToBig5(HtmlFile)
Dim objStream As Object
Set objStream = CreateObject("ADODB.Stream")
With objStream
.Type = 2
.Mode = 3
.Open
.Charset = "UTF-8" ' 或其他編碼
.LoadFromFile HtmlFile
UTF8ToBig5 = .ReadText
'也可透過 .SaveToFile 方法把檔案存檔
.Close
End With
End Function

Excel 檔案修復功能

Excel檔案毀損時,可用excel內建的修復功能!
(Excel 2002後才有此功能)

1. 開啟excel
2. Ctrl+O
3. 點選要開啟的檔案
4. 在該視窗右下角,按一下[開啟]按鈕右邊小箭頭.
5. 選擇[開啟並修復].[修復]
6. 開啟後,再另存新檔.
7. 如果不能修復,就選擇[開啟並修復].[抽選資料]