Excel多个工作簿中的工作表合并到一个工作簿中

Excel多个工作簿中的工作表合并到一个工作簿中 file:///C|/Users/sunqi/Desktop/Ex cel多个工作簿中的工作表合并到一个工作簿中.htm[2015-03-13 11:28:50] Excel 多个工作簿中的工作表合并到一个工作簿中收藏人：dfzhuce Excel 多个工作簿中的工作表合并到一个工作簿中转 LHY：方法2比较好，是我需要的Excel 多个工作簿中的工作表合并到一个工作簿中！^_^ '有时，需要将多个Excel工作簿中的工作表合并到一个工作簿中。有多种合并工作簿的情形，下面先给出一种合并多个工作簿的VBA范例，供参考。方法1 Sub CombineWorkbooks() Dim wk As Workbook Dim sh As Worksheet Dim strFileName As String Dim strFileDir As String Dim nm As String nm = ThisWorkb ook.Name strFileDi r = ThisWorkb ook.path & "\" Application.ScreenUpdating = False strFileNa me = Dir(strFileDir & "*.xls") Do While strFileN ame <> vbNullString If strFileName <> nm Then MsgBox strFileName Set wk = Workbooks.Open(Filename:=strFileDir & strFileName, ReadOnly:=True) strFileNa me = Left(Left(strFileN ame, Len(strFileN ame) - 4), 29) '取主文件名,除掉.XLS For Each sh In wk.Sheets sh.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) '工作表命名,以工作表所在文件名为类 If wk.Sheets.C ount > 1 Then ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = strFileName & sh.Name Else ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = strFileName End If Next wk.Close SaveChanges:=False End If strFileNa me = Dir Loop Application.ScreenUpdating = True End Sub 方法2 Sub UnWorksheets() Application.ScreenUpdating = False Dim lj As String Dim dirname As String Dim nm As String Dim sname As String 易车打屁屁作文室内装修报价表英超家庭装修报价单石斛种植基质最新文章 SONY China Service 爸爸存在的理由 ——— 写给女儿（特感... [ 转载] 白发变黑 —— 按摩小指头有些穴位千万不能随意按敲酒的100种妙用化验单正常值更多热门文章计算机/ 台式机组装维护教程【完美设置win7系统定时关机的方法】蓝牙功能小应用【把书上的字快速弄到电脑上】超级技... 让人防不胜防中秋快乐！动态图片一些可以打发无聊时间的超级好玩的小... 安卓系统的50个实用技巧 QQ聊天精美图片，太牛了！我收藏了！~ 可用于首页的海量精美MV.MP3可互换播... 教你如何在不允许使用右键的网站上使... 推荐几个去掉图片上的文字的技巧!!（... 更多>> 2012-08 -15 | 阅： | 分享转： | 首页阅览室馆友我的图书馆 b~ŽƒSË

Upload
alicia-hayes
Category

Documents
view
30
download
0

Embed Size (px):

description

Transcript of Excel多个工作簿中的工作表合并到一个工作簿中

Excel

file:///C|/Users/sunqi/Desktop/Excel.htm[2015-03-13 11:28:50]

Excel

dfzhuce

Excel LHY2Excel^_^

'Excel

VBA

1

Sub CombineWorkbooks()

Dim wk As Workbook

Dim sh As Worksheet

Dim strFileName As String

Dim strFileDir As String

Dim nm As String

nm = ThisWorkbook.Name

strFileDir = ThisWorkbook.path & "\"

Application.ScreenUpdating = False

strFileName = Dir(strFileDir & "*.xls")

Do While strFileName vbNullString

If strFileName nm Then

MsgBox strFileName

Set wk = Workbooks.Open(Filename:=strFileDir & strFileName, ReadOnly:=True)

strFileName = Left(Left(strFileName, Len(strFileName) - 4), 29) ',.XLS

For Each sh In wk.Sheets

sh.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

',

If wk.Sheets.Count > 1 Then

ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = strFileName & sh.Name

Else

ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = strFileName

End If

Next

wk.Close SaveChanges:=False

End If

strFileName = Dir

Loop

Application.ScreenUpdating = True

End Sub

2

Sub UnWorksheets()

Application.ScreenUpdating = False

Dim lj As String

Dim dirname As String

Dim nm As String

Dim sname As String

SONY China Service...[] 100

/win7......50QQ~MV.MP3......!!...

>>

2012-08-15 | | |
Excel

file:///C|/Users/sunqi/Desktop/Excel.htm[2015-03-13 11:28:50]

dfzhuce >

Dim i As Integer, ii As Integer

lj = ActiveWorkbook.path

nm = ActiveWorkbook.Name

dirname = Dir(lj & "\*.xls") '

Do While dirname ""

If dirname nm Then

Workbooks.Open Filename:=lj & "\" & dirname '

ii = ActiveWorkbook.Sheets.Count '

'(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))

For i = 1 To ii

Workbooks(dirname).Sheets(i).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

Next

Workbooks(dirname).Close False

End If

dirname = Dir

Loop

End Sub

'

'?

Sub UnionWorksheets()

Application.ScreenUpdating = False

Dim lj As String

Dim dirname As String

Dim nm As String

Dim i As Integer, ii As Integer

lj = ActiveWorkbook.path

nm = ActiveWorkbook.Name

dirname = Dir(lj & "\*.xls")

Cells.Clear

Do While dirname ""

If dirname nm Then

Workbooks.Open Filename:=lj & "\" & dirname

ii = ActiveWorkbook.Sheets.Count

Workbooks(nm).Activate

'

For i = 1 To ii

Workbooks(dirname).Sheets(i).UsedRange.Copy _

Range("a65536").End(xlUp).Offset(2, 0)

Next

Workbooks(dirname).Close False

End If

dirname = Dir

Loop

End Sub

01

02

03

04

05

06
Excel

file:///C|/Users/sunqi/Desktop/Excel.htm[2015-03-13 11:28:50]

(0)+1

....VBAExcel_

...1

...excelvba

....excelvba

_VBA....Excel StartUp.xls,

....Excel?

EXCEL

...,5(

...1,

...:,

...

...

?...

Excel(,)

office []PPT

EXCEL...

word20

Excel

U3JUIwJUJGJUU0JUI4JUFELmh0bQA=: input2: SendRefTB: