假如数据库中已经有数据了
步骤1
在Excel表格中新建一页表格用于存储数据库的数据
步骤2
将待输入数据的表格与数据库关联
步骤3
在表格启动时,将表格与数据库进行关联
注意:一定要在ThisWorkbook的Workbook_open()方法中实现。
附上代码:
Private Sub Workbook_open()
'初始化物料库
initialMaterial
'其他操作
End Sub
Private Sub initialMaterial()
Set cnn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
strcn = "Provider=sqloledb;Data Source=127.0.0.1;Initial Catalog=AIS20140120174606;User ID=sa;Password=123;Persist Security Info=False;"
cnn.Open strcn
sql_str = "select FName from t_ICItem where FNumber like '1.03.%'"
rs.Open sql_str, cnn
m_row = 1
Do While Not rs.EOF
Worksheets("物料库").Cells(m_row, 1) = rs(0)
m_row = m_row + 1
rs.movenext
Loop
rs.Close
cnn.Close
rs = Nothing
cnn = Nothing
End Sub