发布时间 : 2024/5/20 21:45:12 星期一 文章Excel VBA 实现读取SQL数据更新完毕开始阅读f71d4201b52acfc789ebc9d9

Private Sub CommandButton1_Click() Worksheets(\ Cells.Select

Selection.Delete Shift:=xlUp Range(\

'清除在Excel中的数据,确保导入信息不出现与原Excel数据进行叠加 Dim cnnConnect As Object Dim rstRecordset As Object Columns(\ Range(\

Selection.Delete Shift:=xlToLeft

Set cnnConnect = CreateObject(\ Set rstRecordset = CreateObject(\

cnnConnect.Open \ \

\

rstRecordset.Open _

Source:=\ ActiveConnection:=cnnConnect

'建立数据库连接,Source为数据库数据服务器IP,及连接用户名与密码,在实现时确保用户对K3数据库有读取权限

With ActiveSheet.QueryTables.Add( _ Connection:=rstRecordset, _ Destination:=Range(\ .Name = \ .FieldNames = True .RowNumbers = False

.FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True

.AdjustColumnWidth = True .RefreshPeriod = 0

.PreserveColumnInfo = True

.Refresh BackgroundQuery:=False End With

'导入K3数据到Excel中, Range(\工号\ Range(\姓名\ Range(\

ActiveWorkbook.Worksheets(\

ActiveWorkbook.Worksheets(\ Range(\ :=xlSortNormal

With ActiveWorkbook.Worksheets(\ .Header = xlYes .MatchCase = False

.Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With MsgBox \

'提示完成 End Sub