VBA 常用语句

VBA 常用语句汇总

1.Excel 连接Access:工程引用前勾选Microsoft DAO 3.6 Object Library

2.Excel 与Access建立连接并查询数据:

Set conn = CreateObject("adodb.connection")(建立数据库)

Set rs = CreateObject("adodb.recordset")(建立)

conn.Open "Provider =microsoft.ACE.oledb.12.0; Data Source(驱动) =" & link(连接)

SQL = "select * from [Excel 12.0(驱动);hdr=no(无标题);Database=" & ThisWorkbook.FullName & "].[Adjust$k2:m]as a left join[参数]on(a.f1=[参数].[Topcode]) and (a.f2=[参数].[BOM 版本]) and (a.f3=[参数].[BOM 状态])"

rs.Open SQL, conn

Set yy = conn.Execute(SQL)

3.定义字典

Set d = CreateObject("scripting.dictionary")

4.状态栏显示内容

Application.StatusBar = "☆☆☆☆程序正在运行，请稍后……☆☆☆☆"(显示内容)

Application.StatusBar = ""(还原显示)

5.关闭/开启错误提示

Application.DisplayAlerts = False(关闭错误提示)

Application.DisplayAlerts = True(开启错误提示)

6.获取最后一个非空单元格对应的行和列

r=Range("a:a").Find(What:="*",After:=[a1],searchorder:=xlByRows,SearchDirection:=xlPrevio us).Row

[a:a].Find("*", , xlValues, , , xlPrevious).Row

c=Range("1:1").Find(What:="*",After:=[a1],searchorder:=xlByRows,SearchDirection:=xlPrevio us).Column

7.设置单元格边框

With Range()(所要设置的区域)

.Borders(xlEdgeLeft).Weight = xlThin

.Borders(xlEdgeLeft).ThemeColor = 1

.Borders(xlEdgeLeft).TintAndShade = -0.499984740745262

.Borders(xlEdgeTop).Weight = xlThin

.Borders(xlEdgeTop).ThemeColor = 1

.Borders(xlEdgeTop).TintAndShade = -0.499984740745262

.Borders(xlEdgeBottom).Weight = xlThin

.Borders(xlEdgeBottom).ThemeColor = 1

.Borders(xlEdgeBottom).TintAndShade = -0.499984740745262

.Borders(xlEdgeRight).Weight = xlThin

.Borders(xlEdgeRight).ThemeColor = 1

.Borders(xlEdgeRight).TintAndShade = -0.499984740745262

.Borders(xlInsideVertical).Weight = xlThin

.Borders(xlInsideVertical).ThemeColor = 1

.Borders(xlInsideVertical).TintAndShade = -0.499984740745262

.Borders(xlInsideHorizontal).Weight = xlThin

.Borders(xlInsideHorizontal).ThemeColor = 1

.Borders(xlInsideHorizontal).TintAndShade = -0.499984740745262 End With

8.设置单元格格式

With Range()(所有设置的区域)

.Interior.Color = RGB(128, 128, 128)(单元格颜色)

= "Arial"(字体)

.Font.Size = 9(字号)

.Font.Color = RGB(255, 255, 255)(字体颜色)

.NumberFormatLocal = "0_ ;[红色]-0 "(数字格式)

.Merge(合并单元格)

.HorizontalAlignment = xlCenter(水平居中)

.VerticalAlignment = xlCenter(垂直居中)

End With

9.设置单元格有效性

With Selection.Validation

On Error Resume Next(忽略错误语句)

.Delete(删除原有效性)

.Add Type:=xlValidateList(序列方式), AlertStyle:=xlValidAlertStop(警告方式), Operator:=xlBetween, Formula1:=”选项A,选项B,选项C”(序列的值)

End With

10.打开文件

fname1 = Application.GetOpenFilename("All Files (*.*),*.*")

Workbooks.Open Filename:=fname1

11.提取文件夹中所有文件名

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False

Range("a2:a1048576").ClearContents

On Error Resume Next

Dim f As String

Dim file() As String

Dim i, k, x

x = 1: i = 1: k = 1

ReDim file(1 To i)

file(1) = Cells(1, 3).Value

Do Until i > k

f = Dir(file(i), vbDirectory)