Option Base 1Private Sub Command1_Click()
Dim conn As Connection
Dim rs As Recordset
Dim SQL As String
Dim mystring As StringDim i, j As Integer
Dim ex As Object
Dim exwbook As Object
Dim exsheet As ObjectSet ex = CreateObject("excel.application")
Set exwbook = Nothing
Set exsheet = Nothing
Set exwbook = ex.workbooks().Add
Set exsheet = exwbook.worksheets("sheet1")Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
mystring = "provider=sqloledb.1;persist security info=false;user id=sa;password=;Initial Catalog=student;Data Source=ybyb"
conn.CursorLocation = adUseClient
conn.Open mystring
SQL = "select * from class_info"
rs.Open SQL, conn, adOpenDynamic, adLockOptimistic
Set DataGrid1.DataSource = rsex.Columns(1).columnwidth = 9
ex.Columns(2).columnwidth = 15
ex.Columns(3).columnwidth = 15
ex.Columns(4).columnwidth = 15
ex.range("a1").Value = "class_no"
ex.range("b1").Value = "course_nmae"
ex.range("c1").Value = "course_type"
ex.range("d1").Value = "course_des"For i = 2 To rs.RecordCount + 1
j = i + 1
For k = 0 To 3
q = Chr(97 + k) & jex.range(q).Value = DataGrid1.Columns(k)
Next k
If rs.EOF = False Then
rs.MoveNext
End If
Next iexwbook.saveas "c:\111.xls"
ex.quitEnd SubPrivate Sub Command2_Click()
Unload Me
End Sub我用上面的方法把datagrid里面的数据导入excel,第一没问题,第二次就提示我是否覆盖,我想把那个提示换成msgbox形式的,而不是系统提示的,请问该怎么做?
Dim conn As Connection
Dim rs As Recordset
Dim SQL As String
Dim mystring As StringDim i, j As Integer
Dim ex As Object
Dim exwbook As Object
Dim exsheet As ObjectSet ex = CreateObject("excel.application")
Set exwbook = Nothing
Set exsheet = Nothing
Set exwbook = ex.workbooks().Add
Set exsheet = exwbook.worksheets("sheet1")Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
mystring = "provider=sqloledb.1;persist security info=false;user id=sa;password=;Initial Catalog=student;Data Source=ybyb"
conn.CursorLocation = adUseClient
conn.Open mystring
SQL = "select * from class_info"
rs.Open SQL, conn, adOpenDynamic, adLockOptimistic
Set DataGrid1.DataSource = rsex.Columns(1).columnwidth = 9
ex.Columns(2).columnwidth = 15
ex.Columns(3).columnwidth = 15
ex.Columns(4).columnwidth = 15
ex.range("a1").Value = "class_no"
ex.range("b1").Value = "course_nmae"
ex.range("c1").Value = "course_type"
ex.range("d1").Value = "course_des"For i = 2 To rs.RecordCount + 1
j = i + 1
For k = 0 To 3
q = Chr(97 + k) & jex.range(q).Value = DataGrid1.Columns(k)
Next k
If rs.EOF = False Then
rs.MoveNext
End If
Next iexwbook.saveas "c:\111.xls"
ex.quitEnd SubPrivate Sub Command2_Click()
Unload Me
End Sub我用上面的方法把datagrid里面的数据导入excel,第一没问题,第二次就提示我是否覆盖,我想把那个提示换成msgbox形式的,而不是系统提示的,请问该怎么做?
在你saveas之前做个判断就行了。
判断文件的存在性可以用FileSystemObject对象,在“工程、引用”中选中Microsoft Scripting Runtime即可。
Dim fso As New FileSystemObject
if fso.FileExists("C:\lll.xls") Then
MsgBox "My box"
...
else
exwbook.saveas "C:\lll.xls"
end if
on error goto err
代码
exit sub
err:
msgbox "出错!"
试一下这样加个错误处理,当出现系统提示,如果也是返回error的话,就转到你的err段,只弹出对话框,而忽略系统信息!
ex.DisplayAlerts = False