'首先得引用Microsoft Excel x object library 其中x为版本号 Dim sFileNameExcel As String Dim objExcel As Excel.Application Dim objBook As Excel.Workbook Dim objSheet As Excel.WorksheetPrivate Sub Command1_Click() Command1.Enabled = False sFileNameTxt = txtPathTxt.Text Dim Str As String Set objExcel = CreateObject("Excel.Application") Set objBook = objExcel.Workbooks.Add objExcel.Visible = True Set objSheet = objBook.Worksheets(1)
Dim nStr(100) As String Dim i As Long Dim j As Long, k As Long Dim nLen As Long Dim MidStr As String Dim CurRow As Long
Open sFileNameTxt For Input As #1 Do While Not EOF(1) CurRow = CurRow + 1 Line Input #1, Str nLen = Len(Str) MidStr = "" j = 0 For i = 1 To nLen If Mid(Str, i, 1) <> " " And Mid(Str, i, 1) <> vbTab Then MidStr = MidStr & Mid(Str, i, 1) ElseIf MidStr <> "" Or Mid(Str, i, 1) = vbTab Then nStr(j) = MidStr j = j + 1 MidStr = "" End If If i = nLen And MidStr <> "" And MidStr <> vbTab Then nStr(j) = MidStr MidStr = "" End If Next For k = 0 To j objSheet.Cells(CurRow, k + 1) = "'" & nStr(k) Next Loop Close #1
sFileNameExcel = txtPathXls.Text objBook.SaveAs sFileNameExcel objBook.Close objExcel.Quit Set objExcel = Nothing Set objBook = Nothing Set objSheet = Nothing MsgBox "OK" Command1.Enabled = True End Sub
用打CommonDialog弹出一个保存对话框 with CommonDialog .... .showsave end with 把数据写入一个excel,保存到任意的地方
Dim sFileNameExcel As String
Dim objExcel As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.WorksheetPrivate Sub Command1_Click()
Command1.Enabled = False
sFileNameTxt = txtPathTxt.Text
Dim Str As String
Set objExcel = CreateObject("Excel.Application")
Set objBook = objExcel.Workbooks.Add
objExcel.Visible = True
Set objSheet = objBook.Worksheets(1)
Dim nStr(100) As String
Dim i As Long
Dim j As Long, k As Long
Dim nLen As Long
Dim MidStr As String
Dim CurRow As Long
Open sFileNameTxt For Input As #1
Do While Not EOF(1)
CurRow = CurRow + 1
Line Input #1, Str
nLen = Len(Str)
MidStr = ""
j = 0
For i = 1 To nLen
If Mid(Str, i, 1) <> " " And Mid(Str, i, 1) <> vbTab Then
MidStr = MidStr & Mid(Str, i, 1)
ElseIf MidStr <> "" Or Mid(Str, i, 1) = vbTab Then
nStr(j) = MidStr
j = j + 1
MidStr = ""
End If
If i = nLen And MidStr <> "" And MidStr <> vbTab Then
nStr(j) = MidStr
MidStr = ""
End If
Next
For k = 0 To j
objSheet.Cells(CurRow, k + 1) = "'" & nStr(k)
Next
Loop
Close #1
sFileNameExcel = txtPathXls.Text
objBook.SaveAs sFileNameExcel
objBook.Close
objExcel.Quit
Set objExcel = Nothing
Set objBook = Nothing
Set objSheet = Nothing
MsgBox "OK"
Command1.Enabled = True
End Sub
with CommonDialog
....
.showsave
end with
把数据写入一个excel,保存到任意的地方
Dlog.FileName = ""
Dlog.Filter = "Excel文件(*.xls)|*.xls"
Dlog.ShowSave
txtPathXls.Text = Dlog.FileName
sFileNameExcel = Dlog.FileName
End SubobjBook.SaveAs sFileNameExcel
sFileNameExcel 就是你要保存的文件路径及名称,可以任取,也可以来自保存对话框