|
请问各位前辈
我用VBA从EXCEL到ACCESS里读值,我有一个值的格式格式为:compo+id六位数+YYMMDDHHMMSS
(例如compocisoqo140101083001)
我想只取得里面那个ID值cisoqo,然后写在到EXCEL里,这样子我下面的代码该如何改啊?
我这个值就是下面代码中的SerialNum。
Private Sub CommandButton1_Click()
Dim Serious$, Rst As New ADODB.Recordset, Strsql$, Ctl As Control, i%
On Error GoTo ErrLine
’链接数据库
If Condb("TargetDatabase.mdb", "ALEX") = False Then GoTo Line1
’从ACCESS选取数据
Strsql = "select CUSTNM,ACCTNO,BALAMT,LSTPYM,Status,PTPAmt,FirstPayDT,NumPay,SettleCompleDT,SerialNum from [Table] order by SerialNum"
Set Rst = Cnn.Execute(Strsql)
If Rst.EOF And Rst.BOF Then MsgBox "Cannot find data in the database": GoTo Line1
With Sheet4
.Cells.Delete
‘讲数据逐个打印在EXCEL中
For i = 0 To Rst.Fields.Count - 1
.Cells(1, i + 1) = Rst.Fields(i).Name
Next i
.Range("a2").CopyFromRecordset Rst
.Columns.AutoFit
.Activate
End With
Line1: Set Rst = Nothing: Exit Sub
ErrLine: MsgBox "system error", 1 + 16, "Alert": GoTo Line1
End Sub
Public Function Condb(dbName$, Optional pwd$ = "") As Boolean
On Error GoTo Line1
Condb = True
Set Cnn = New ADODB.Connection
Cnn.ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\" & dbName & _
";Jet OLEDB:Database Password=" & pwd & ";"
Cnn.CursorLocation = adUseClient
Cnn.ConnectionTimeout = 5
Cnn.Open
Exit Function
Line1:
Condb = False
MsgBox "database connection error£º" & Chr(10) & Err.Description
End Function
zixiaoruixue 发表于 2014-4-16 13:25
请问前辈是这样子吗?
Strsql = "select CUSTNM,ACCTNO,BALAMT,LSTPYM,Status,PTPAmt,FirstPayDT,NumP ...
Strsql = "select CUSTNM,ACCTNO,BALAMT,LSTPYM,Status,PTPAmt,FirstPayDT,NumPay,SettleCompleDT, mid(SerialNum,6,6) as 编号 from [Table] order by SerialNum"
列名你在后面再加上 as 新列名 就可以了。
|
|