VBA梳理:举一反三,处理方法只要改变个方式就可以!
1.连接数据库:
Dim sht As Worksheet, i As Long, j As Integer, k As Integer 'j为整数变量;sht 为excel工作表对象变量,指向某一工作表
Dim cn As New ADODB.Connection '定义数据链接对象 ,保存连接数据库信息;请先添加ADO引用
Dim rs As New ADODB.Recordset '定义记录集对象,保存数据表
Dim strCn As String, strSQL As String '字符串变量
strCn = "Provider=sqloledb;Server=0.0.0.0;Database=XMDEV;Uid=sa1;Pwd=529780;" '定义数据库链接字符串
strSQL = "select MARA_MATNR as '主件品号',MAKT_MAKTX as '主件品名',MARA_WRKST as '主件规格'from INVMB where MARA_MATNR='" & Cells(i, 1) & "'" '定义SQL查询命令字符串"-----根据一定的条件抓数据
cn.Open strCn '与数据库建立连接,如果成功,返回连接对象cn
rs.Open strSQL, cn '执行strSQL所含的SQL命令,结果保存在rs记录集对象中
2. 数据导入Excel:
//循环Excel表,并在对应的单元格导入数据
For Each myrange1 In Range("E3:E200")
'If myran