请问怎样把一个目录下的所有文件的文件名导出到execl表?
请问怎样把一个目录下的所有文件的文件名导出到execl表?谢谢 --------------------编程问答--------------------工程->引用->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
如果纯粹的把一堆文字放在一个新的excel文件里面显示并作后期处理,建议直接生成简单的CSV格式文本文件,不需要调用excel对象
补充:VB , 基础类