工程->引用->Microsoft Excel 12.0(或者11.0) Object ,即你机器相对应的excel对象Private Function AutoListFiles(ByVal sDirName As String, ByVal FileFilter As String) As Boolean On Error GoTo RF_ERROR
Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets(1) Dim sName As String, sFile As String, sExt As String Dim sDirList() As String, iDirNum As Integer, I As Integer
'首先枚举所有文件 sFile = Dir(sDirName + FileFilter, vbNormal + vbArchive + vbHidden) I = 1 Do While Len(sFile) > 0 sFile = UCase(Trim(sFile)) Debug.Print sFile xlSheet.Cells(I, 2).Value = sFile I = I + 1 sFile = Dir '下一个文件 Loop xlApp.Application.Visible = True '交还控制给Excel Set xlApp = Nothing RF_EXIT: AutoListFiles = True Set xlApp = Nothing Exit Function RF_ERROR: MsgBox Err.Description, vbCritical, "" Resume RF_EXIT End Function Private Sub Command1_Click() Dim bln As Boolean '将d:\code目录下的所有文件和目录列出来 bln = AutoListFiles("d:\code\", "*.*") End Sub
Private Function AutoListFiles(ByVal sDirName As String, ByVal FileFilter As String) As Boolean On Error GoTo RF_ERROR
Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets(1) Dim sName As String, sFile As String, sExt As String Dim sDirList() As String, iDirNum As Integer, I As Integer
'首先枚举所有文件 sFile = Dir(sDirName + FileFilter, vbNormal + vbArchive + vbHidden) I = 1 Do While Len(sFile) > 0 sFile = UCase(Trim(sFile)) Debug.Print sFile xlSheet.Cells(I, 2).Value = sFile I = I + 1 sFile = Dir '下一个文件 Loop xlApp.Application.Visible = True '交还控制给Excel Set xlApp = Nothing RF_EXIT: AutoListFiles = True Set xlApp = Nothing Exit Function RF_ERROR: MsgBox Err.Description, vbCritical, "" Resume RF_EXIT End Function Private Sub Command1_Click() Dim bln As Boolean '将d:\code目录下的所有文件和目录列出来 bln = AutoListFiles("d:\code\", "*.*") End Sub
工程->引用->Microsoft Excel 12.0(或者11.0) Object ,即你机器相对应的excel对象Private Function AutoListFiles(ByVal sDirName As String, ByVal FileFilter As String) As Boolean On Error GoTo RF_ERROR
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
Dim sName As String, sFile As String, sExt As String
Dim sDirList() As String, iDirNum As Integer, I As Integer
'首先枚举所有文件
sFile = Dir(sDirName + FileFilter, vbNormal + vbArchive + vbHidden)
I = 1
Do While Len(sFile) > 0
sFile = UCase(Trim(sFile))
Debug.Print sFile
xlSheet.Cells(I, 2).Value = sFile
I = I + 1 sFile = Dir '下一个文件
Loop
xlApp.Application.Visible = True
'交还控制给Excel
Set xlApp = Nothing
RF_EXIT:
AutoListFiles = True
Set xlApp = Nothing
Exit Function
RF_ERROR:
MsgBox Err.Description, vbCritical, ""
Resume RF_EXIT
End Function
Private Sub Command1_Click()
Dim bln As Boolean
'将d:\code目录下的所有文件和目录列出来
bln = AutoListFiles("d:\code\", "*.*")
End Sub
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
Dim sName As String, sFile As String, sExt As String
Dim sDirList() As String, iDirNum As Integer, I As Integer
'首先枚举所有文件
sFile = Dir(sDirName + FileFilter, vbNormal + vbArchive + vbHidden)
I = 1
Do While Len(sFile) > 0
sFile = UCase(Trim(sFile))
Debug.Print sFile
xlSheet.Cells(I, 2).Value = sFile
I = I + 1 sFile = Dir '下一个文件
Loop
xlApp.Application.Visible = True
'交还控制给Excel
Set xlApp = Nothing
RF_EXIT:
AutoListFiles = True
Set xlApp = Nothing
Exit Function
RF_ERROR:
MsgBox Err.Description, vbCritical, ""
Resume RF_EXIT
End Function
Private Sub Command1_Click()
Dim bln As Boolean
'将d:\code目录下的所有文件和目录列出来
bln = AutoListFiles("d:\code\", "*.*")
End Sub
如果纯粹的把一堆文字放在一个新的excel文件里面显示并作后期处理,建议直接生成简单的CSV格式文本文件,不需要调用excel对象