在所有现有的Excel工作表之后如何添加工作表？ [英] How do I add a worksheet after all existing Excel worksheets?

问题描述

我需要在当前工作表编号3的末尾添加一个Excel工作表。然而，当我运行下面的程序时，我会在第一个位置获取工作表。如何解决这个问题？

I need to add an Excel sheet at the end of the current sheet number 3. When I run the below program, however, I get a sheet at the first position. How can I resolve this?

该程序基本上将数据从一个Excel工作簿复制到另一个工作簿，由多张工作表组成。

The program basically copies data from one Excel workbook to another workbook, consisting of multiple sheets.

代码：

Dim objXL,objWrkBk,objWrkSht,a,n
Set objfso=CreateObject("Scripting.FileSystemObject")
Set objXL=CreateObject("Excel.Application")
Set objWrkBk=objXL.Workbooks.Open("C:\learning\demo.xlsx")
m=objWrkBk.Worksheets.count
msgbox m
For n=1 to m
Set objWrkBk=objXL.Workbooks.Open("C:\learning\demo.xlsx")
Set objWrkSht=objWrkBk.Worksheets("Sheet"&n)
columncount = objWrkSht.usedrange.columns.count
rowcount = objWrkSht.usedrange.rows.count
For i=1 to rowcount
For j=1 to columncount
If   objWrkSht.cells(i,j).value <> "" Then
    a= objWrkSht.cells(i,j).value& "   "
End If
'Next
'Next
check=objfso.FileExists("C:\learning\demo1.xlsx")
If not check Then
    objXL.Workbooks.Add
    objXL.ActiveWorkbook.SaveAs("C:\learning\demo1.xlsx")
End If
Set objWrkBk1=objXL.Workbooks.Open("C:\learning\demo1.xlsx")
If n<=3 Then
    Set objWrkSht1=objWrkBk1.Worksheets("Sheet"&n)
End If
If n>3 Then
    objXL.Worksheets.add
    Set objWrkSht1=objWrkBk1.Worksheets("Sheet"&n)
End If

If   objWrkSht.cells(i,j).value <> "" Then
objWrkSht1.cells(i,j).value=a
objWrkBk1.Save
End If
Next
Next
Set objWrkSht=Nothing
Set objWrkBk=Nothing
Set objWrkSht1=Nothing
Set objWrkBk1=Nothing

Next
objXL.Quit
Set objXL=Nothing

推荐答案

您可以在之后添加工作表位置，即该代码在最后一个工作表之后添加工作表（由 objWrkBk.Sheets（objWrkBk.Sheets.Count）提供）其中 objWrkBk.Sheets.Count）是添加之前的工作表数量。

You can add sheets in the After position, ie this code adds the sheet after the last worksheet (provided by objWrkBk.Sheets(objWrkBk.Sheets.Count)) where objWrkBk.Sheets.Count) is the number of sheets prior to the addition.

Set objXL = CreateObject("Excel.Application")
Set objWrkBk = objXL.Workbooks.Add
Set objWrkSht = objWrkBk.Sheets.Add(, objWrkBk.Sheets(objWrkBk.Sheets.Count))

这篇关于在所有现有的Excel工作表之后如何添加工作表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！