|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
是在看不出以下这段代码中WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Worksheets.Count)出错在哪里,请大神们帮忙看看。
要实现的功能是:自动打开某个文件夹中的excel文件,并将其中的某些工作表复制到当前工作簿里
- Sub main_DownloadOtherSheets()
- Dim dataExcel As Object, WB As Workbook, WS
- Dim path$, mFilename$, FPath$
- path = ThisWorkbook.Sheets("Config").[B1] '获取母数据文件的路径,一般存于’Mengting Jiang‘文件夹中
- mFilename = "Sourcing_FC_V7.7.xlsm" '母文件名
- FPath = path & "" & mFilename '母文件全路径
- Set dataExcel = CreateObject("Excel.application")
- Set WB = dataExcel.Workbooks.Open(FPath, ReadOnly:=True) '括号中path为你要操作文件的路径
- Application.DisplayAlerts = False
- For Each WS In WB.Worksheets
- If WS.Name <> "Note and Config" And WS.Name <> "DataSource" And WS.Name <> "INSTRUCTION" Then '除<Note and Config>,<DataSource>等外,其余工作表均下载
- Debug.Print WS.Name
- WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Worksheets.Count)
- ActiveSheet.Name = WS.Name
- End If
- Next
- Application.DisplayAlerts = True
- Set WS = Nothing
- WB.Close savechanges:=False '关闭文件并不保存
- Set WB = Nothing
复制代码
|
|