流失率公式excel函数公式

将多个文件合并到一个工作簿
Sub Com()
Dim MyPath, MyName, AWbName
Dim WbAs Workbook, WbN As String
Dim G As Long
Dim NumAs Long
Dim BOX As String
Updating = False
MyPath =
MyName = Dir(MyPath&
AWbName =
Num = 0
Do While MyName<>
If MyName<>AWbName Then
Set Wb = (MyPath&
&MyName)
Num = Num + 1
With Workbooks(1).ActiveSheet
.Cells(.Range(
1) = Left(MyName, Len(MyName) - 4)
For G = 1
(G). .Cells(.R
ange(
Next
WbN = WbN&Chr(13) &
False
End With
End If
MyName = Dir
Loop
Range(
Updating = True
MsgBox 共合并了个工作薄下的全部工作表。
如下：提示
End Sub
将工作簿拆分为多个文件
Private Sub 分拆工作表()
Dim sht As Worksheet
Dim MyBookAs Workbook
Set MyBook = ActiveWorkbook
For Each sht In


Filename:=& &,
FileFormat:=xlNormal '将工作簿另存为EXCEL
默认格式

Next
MsgBox 文件已经被分拆完毕!
End Sub

合并文件到不同工作表
Sub 工作薄间工作表合并()
Dim FileOpen
Dim X As Integer
Updating = False
FileOpen =
合并多个工作表到一个工作表
Sub 合并当前工作簿下的所有工作表()
Updating = False
For j = 1 To
If Sheets(j).Name <> Then
X = Range(
nFilename(FileFilter:=Excel
文件(*.xls),*.xls 合并工作薄
X = 1
While X <= UBound(FileOpen)
Filename:=FileOpen(X)
Sheets().Move
After:=()
X = X + 1
Wend
ExitHandler:
Updating = True
Exit Sub
errhadler:
ption
End Sub


























Sheets(j). Cells(X, 1)
End If
Next
Range(
Updating = True
MsgBox 当前工作簿下的全部工作表已经合并完毕！
vbInformation, 提示
End Sub



将多个文件合并到一个工作簿（2）
Sub Macro1()
Dim MyPath$$, MyName$$, sh As Worksheet, sht As
返回A1：A10区域的最后一个非空单元格内容
=LOOKUP(1,0(A1:A10<>


Worksheet, m&
Set sh = ActiveSheet
MyPath = &
MyName = Dir(MyPath&
Updating = False
ontents
Do While MyName<>
If MyName<> Then
With GetObject(MyPath&MyName)
For Each sht In .Sheets
If IsSheetEmpty = IsEmpty(nge) Then
m = m + 1
If m = 1 Then
sht.[a1]. sh.[a1]
Else
sht.[a1].(1).Copy
sh.[a65536].End(xlUp).Offset(1)
End If
End If
Next
.Close False
End With
End If
MyName = Dir
Loop
Updating = True
End Sub

















破解受保护工作表密码
Option Explicit
Public Sub AllInternalPasswords()
' Breaks worksheet and workbook structure
passwords. Bob McCormick
' probably originator of base code algorithm
modified for coverage
' of workbook structure windows passwords and
for multiple passwords
'
' Norman Harker and JE McGimpsey 27-Dec-2002
(Version 1.1)
' Modified 2003-Apr-04 by JEM: All msgs to
constants, and
' eliminate one Exit Sub (Version 1.1.1)
' Reveals hashed passwords NOT original
passwords
Const DBLSPACE As String =
vbNewLine&vbNewLine
Const AUTHORS As String = DBLSPACE
&vbNewLine& _
Bob McCormick base code by
Norman Harker and JE McGimpsey
Const HEADER As String =
User Message
Const VERSION As String = DBLSPACE &

Const MSGNOPWORDS1 As String =
no passwords on

1.1.1 2003-Apr-04

http:ndpc
Const REPBACK As String = DBLSPACE &
report failure

newsgroup.
Const ALLCLEAR As String = DBLSPACE &
workbook should

sure you:
DBLSPACE &
also
DBLSPACE & BACKUP!, BACKUP!!, BACKUP!!!
DBLSPACE &
was

formulas

data
AUTHORS & VERSION
Const MSGNOPWORDS2 As String =
no protection to

_

VERSION
Const MSGTAKETIME As String =
OK button this

time

& _

DBLSPACE & _

& VERSION
Const MSGPWORDFOUND1 As String =
a Worksheet

DBLSPACE & _

ALLCLEAR & AUTHORS & VERSION & REPBACK
Dim w1 As Worksheet, w2 As Worksheet
DBLSPACE & _

workbooks by

DBLSPACE & _

AUTHORS & VERSION
Const MSGPWORDFOUND2 As String =
a Worksheet

found was:
DBLSPACE &
for potential

who

and clear

Const MSGONLYONE As String = Only structure
windows

found.
Dim i As Integer, j As Integer, k As Integer, l As
Integer
Dim m As Integer, n As Integer, i1 As Integer, i2 As
Integer
Dim i3 As Integer, i4 As Integer, i5 As Integer, i6 As
Integer
Dim PWord1 As String
Dim ShTag As Boolean, WinTag As Boolean
Updating = False
With ActiveWorkbook
WinTag = .ProtectStructure Or .ProtectWindows
End With
ShTag = False
For Each w1 In Worksheets
ShTag = ShTag Or tContents
Next w1
If Not ShTag And Not WinTag Then
MsgBox MSGNOPWORDS1,
vbInformation, HEADER
Exit Sub
End If
MsgBox MSGTAKETIME, vbInformation, HEADER
If Not WinTag Then
MsgBox MSGNOPWORDS2, vbInformation,
HEADER
Else
On Error Resume Next
Do 'dummy do loop
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To
66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To
66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To
126
With ActiveWorkbook
.Unprotect Chr(i) &Chr(j) &Chr(k) & _
Chr(l) &Chr(m) &Chr(i1) &Chr(i2) & _
Chr(i3) &Chr(i4) &Chr(i5) &Chr(i6) &Chr(n)
If .ProtectStructure = False And _
.ProtectWindows = False Then
PWord1 = Chr(i) &Chr(j) &Chr(k) &Chr(l) & _
Chr(m) &Chr(i1) &Chr(i2) &Chr(i3) & _
Chr(i4) &Chr(i5) &Chr(i6) &Chr(n)
tute(MSGPWORDFOUN
D1, _

Exit Do 'Bypass all for...nexts
End If
End With
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
IfWinTag And Not ShTag Then
MsgBox MSGONLYONE, vbInformation, HEADER
Exit Sub
End If
On Error Resume Next
For Each w1 In Worksheets
'Attempt clearance with PWord1
ect PWord1
Next w1
On Error GoTo 0
ShTag = False
For Each w1 In Worksheets
'Checks for all clear ShTag triggered to 1 if not.
ShTag = ShTag Or tContents
Next w1
If ShTag Then
For Each w1 In Worksheets
With w1
If .ProtectContents Then
On Error Resume Next
Do 'Dummy do loop
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To
66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To
66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To
126
.Unprotect Chr(i) &Chr(j) &Chr(k) & _
Chr(l) &Chr(m) &Chr(i1) &Chr(i2) &Chr(i3) & _
Chr(i4) &Chr(i5) &Chr(i6) &Chr(n)
If Not .ProtectContents Then
PWord1 = Chr(i) &Chr(j) &Chr(k) &Chr(l) & _
Chr(m) &Chr(i1) &Chr(i2) &Chr(i3) & _
Chr(i4) &Chr(i5) &Chr(i6) &Chr(n)
tute(MSGPWORDFOUN
D2, _

'leverage finding Pword by trying on other sheets
For Each w2 In Worksheets
ect PWord1
Next w2
Exit Do 'Bypass all for...nexts
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
End With
Next w1
End If
MsgBox ALLCLEAR & AUTHORS & VERSION &
REPBACK, vbInformation, HEADER
End Sub