我正在尝试创建一个宏,该宏将每周从对Excel文件的访问中导出新记录。目前,我的代码已将其添加到Excel文件中,但是如何才能仅更新文件中已有的工作表呢?
Const ConStraccess As String = "xxxx;"
Sub CopyDataFomDatabase()
Dim BrokerConn As ADODB.Connection
Dim BrokerData As ADODB.Recordset
Dim BrokerField As ADODB.Field
Set BrokerConn = New ADODB.Connection
Set BrokerData = New ADODB.Recordset
BrokerConn.ConnectionString = ConStraccess
BrokerConn.Open
On Error GoTo CloseConnection
With BrokerData
.activeConnection = BrokerConn
.Source = "SELECT * FROM xxxxx;"
.LockType = adLockReadOnly
.CursorType = adOpenForwardOnly
.Open
End With
On Error GoTo CloseRecordset
Worksheets.Add
For Each BrokerField In BrokerData.Fields
activeCell.Value = BrokerField.Name
activeCell.Offset(0,1).Select
Next BrokerField
Range("A1").Select
Range("A2").CopyFromRecordset BrokerData
Range("A1").CurrentRegion.EntireColumn.AutoFit
CloseRecordset:
BrokerData.Close
CloseConnection:
BrokerConn.Close