作者：乔山办公网日期：
返回目录：excel表格制作
excel 中间view菜单肯定会有工具箱，你就可以选择了，选择完后，你双击添加的控件就进入代码编写了
Private Const DirPath$ = "C:\" '指向查找的路径
Sub aa()
Dim Filename$
Dim i&
Dim Wb As Workbook, Rng As Range
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
With Worksheets("总表")
For i = 1 To .[B65536].End(3).Row
If Len(.Cells(i, 2)) <> 0 Then
Filename = DirPath & "" & .Cells(i, "B") & ".xls"
If Len(Dir(Filename)) <> 0 Then
Set Wb = GetObject(Filename)
With Wb.Worksheets("材料")
If Err.Number <> 0 Then GoTo l
Set Rng = .[B:B].Find(what:="水泥7a64e58685e5aeb9363砖")
If Not Rng Is Nothing Then
Worksheets("总表").Cells(i, "C") = Rng.Offset(0, 1).Value
End If
End With
l:
Wb.Close False
End If
End If
Next
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
1
2
3
4
5
6
7
Sub Macro1()
dim years as string
years ="2000"
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.cells.AutoFilter Field:=4, Criteria1:=years
End Sub
以上知代码定于years 变量，可根据设置年份
然后选择第一道行，生专成高级筛选，筛选条件是第四列属，筛选内容是变量years
在EXCEL中录制一个宏，并指定快捷键（例如Ctrl+g），修改该宏的VBA模块代码如下：
1、声明段：
Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, _
ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Const SW_SHOWNORMAL = 1
2、代码段（宏名不e68a84e8a2ade799bee5baa6361需要更改，只需要粘贴过程内的代码）
Sub Macro1()
'
' Macro1 Macro
'
' 快捷键: Ctrl+g
'
Const FILE_PATH = "D:\001-1\"
Const FILE_EXT = ".jpg"
Dim page As String
Dim filename As String
Dim Ret As Long
page = Trim(InputBox("请输入图片编码：", "输入", 1))
If Len(page) > 0 Then
filename = FILE_PATH & page & FILE_EXT
If Dir(filename) = "" Then
MsgBox "指定的文件" & filename & "不存在！"
Else
Ret = ShellExecute(0, "open", filename, vbNullString, Environ("windir"), SW_SHOWNORMAL)
End If
End If
End Sub