excel表格怎么批量修改表格目录其中一个数值？

一、首先是打开需要 *** 作的excel文件。

二、按电脑键盘组合键ctrl+f。或者使用office软件编辑上的查找替换功能。点击【替换】按钮，输入需要替换的内容。

三、选中需要替换的列，可以在需要替换的列中，点击向下箭头，选择需要替换的列。

四、替换框选中需要替换的内容，按键盘ctrl+A，选中需要替换的单元格。

五、查看需要替换的单元格是否正确。在左侧可以看到待替换的文本内容。

六、开始替换。点击【全部替换】可以对选中的列的内容全量替换，替换完成后，点击保存文件即可。

将excel数据导入access

Sub Excel2access()

Dim conn As ADODB.Connection

Dim WN As String

Dim TableName As String

Dim sSql As String

Dim tStr As String

'数据库名，请自行修改，路径与当前工作簿在同一目录

WN = "进销存表.mdb"

'数据库的表名与当前工作表名一致

TableName = ActiveSheet.Name

Set conn = New ADODB.Connection

conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0" &_

"Extended Properties=Excel 8.0" &_

"Data Source=" &ThisWorkbook.Path &"\" &ActiveWorkbook.Name

conn.Open

If conn.State = adStateOpen Then

sSql = "Insert Into [DataBase=" &ActiveWorkbook.Path &"\" &WN &"]." &TableName &" Select * From [" &ActiveSheet.Name &"$]"

conn.Execute sSql

MsgBox "成功把数据插入到“" &TableName &"”中！", , "http://excelba.com"

conn.Close

End If

Set conn = Nothing

End Sub

Sub 更新姓名库()

Dim cnn As ADODB.Connection

Dim MyConn

Dim rst As ADODB.Recordset

Dim I As Long, j As Long

Dim Rw As Long

Dim rng, tt, N%, Ta()

Call 数据库路径

'激活所需要的工作表,获取已使用的行数

rng = Sheets("考号系统").UsedRange

tt = Array("准考证号", "统考号", "姓名", "班", "考场1", "座位1", "拼音首字")

N = 0

ReDim Ta(0 To 6)

For N = 0 To 6

Call 查找(tt(N))

'If rng(1, I) = tt(N) Then

Ta(N) = dxcol

'End If

Next

'创建对数据库的连接

Set cnn = New ADODB.Connection

MyConn = pathi

With cnn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Open MyConn

End With

'创建记录集

Set rst = New ADODB.Recordset

rst.CursorLocation = adUseServer

rst.Open "delete * from baseinfo", cnn, 1, 3

rst.Open Source:="baseinfo", ActiveConnection:=cnn, _

CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _

Options:=adCmdTable

Do While Not rst.BOF And Not rst.EOF

rst.Delete

rst.Update

Loop

'将Excel中所有的记录载入Access.

For I = 2 To UBound(rng)

rst.AddNew

'For j = 1 To 7

If Left(rng(2, 1), 4) = 1323 Then

rst("考号") = rng(I, Ta(1))

Else

rst("考号") = rng(I, Ta(0))

End If

rst("班级代码") = rng(I, Ta(3))

rst("班级") = rng(I, Ta(3))

rst("姓名") = rng(I, Ta(2))

rst("试室号") = rng(I, Ta(4))

rst("座位号") = rng(I, Ta(5))

rst("拼音首字") = rng(I, Ta(6))

'Next j

rst.Update

Next I

' 关闭连接并清理内存

rst.Close

cnn.Close

Set rst = Nothing

Set cnn = Nothing

Erase rng

MsgBox "姓名库更新完毕", vbOKOnly, " *** 作成功"

End Sub

---