access 2007 vba 开发中学到的知识（二）

文件的导入和导出

excel

‘excel导入
Private Sub btnInExcel_Click()

Dim strSelectFile As String
With Application.FileDialog(3)
.AllowMultiSelect = False
.InitialFileName = ""
.Filters.Clear
.Filters.Add "EXCEL文件", "*.xls"
If .Show = -1 Then
strSelectFile = .SelectedItems.Item(1)
Else
Exit Sub
End If
End With
DoCmd.TransferSpreadsheet 0, 8, "测试表", strSelectFile, True
MsgBox "导入成功！"
DoCmd.OpenTable "测试表"
End Sub

‘excel导出
Private Sub btnOutExcel_Click()

Dim strSelectFile As String
With Application.FileDialog(2)
.AllowMultiSelect = False
.InitialFileName = "test.xls"
If .Show = -1 Then
strSelectFile = .SelectedItems.Item(1)
Else
Exit Sub
End If
End With
DoCmd.TransferSpreadsheet 1, 8, "测试表", strSelectFile, True
MsgBox "导出成功！"
ShellEx strSelectFile
End Sub

CSV

‘导入csv
Private Sub btnInDOCMD_Click()
Dim strSelectFile As String
With Application.FileDialog(3)
.AllowMultiSelect = False
.InitialFileName = ""
.Filters.Clear
.Filters.Add "CSV文件", "*.CSV"
If .Show = -1 Then
strSelectFile = .SelectedItems.Item(1)
Else
Exit Sub
End If
End With
DoCmd.TransferText acImportDelim, , "测试表", strSelectFile, True
MsgBox "导入成功！"
DoCmd.OpenTable "测试表"

End Sub
‘导出csv
Private Sub btnOutDOCMD_Click()
Dim strSelectFile As String
With Application.FileDialog(2)
.AllowMultiSelect = False
.InitialFileName = "test.csv"

If .Show = -1 Then
strSelectFile = .SelectedItems.Item(1)
Else
Exit Sub
End If
End With
DoCmd.TransferText acExportDelim, , "测试表", strSelectFile, True
MsgBox "导出成功！"
ShellEx strSelectFile
End Sub

TXT

‘导出TXT
Private Sub btnOutTXT_Click()

Dim strSelectFile As String
With Application.FileDialog(2)
.AllowMultiSelect = False
.InitialFileName = "test.txt"

If .Show = -1 Then
strSelectFile = .SelectedItems.Item(1)
Else
Exit Sub
End If
End With
DoCmd.TransferText acExportDelim, , "测试表", strSelectFile, True
MsgBox "导出成功！"
ShellEx strSelectFile
End Sub

创建TXT并写入内容

需要引用 Microsoft Script Runtime

Dim fso As New FileSystemObject

fso.CreateTextFile (CurrentProject.Path & "\test.txt")
fso.OpenTextFile(CurrentProject.Path & "\test.txt", ForWriting).WriteLine "测试数据"

在原有数据后面追加新数据

fso.OpenTextFile(CurrentProject.Path & "\test.txt", ForAppending).WriteLine "测试数据"