使用VBSCript将数据复制到另一个Excel的问题

我有一个excel表,其中包含大约1000行数据,我已将这些数据复制到满足条件的另一张表 . 为了实现这一点,我写了一个脚本,

For m = 1 To x2 'iterate single column
  For n = 1 To x4 'iterate PM_DUMP
    If InStr(PMSheet.cells(n,6).value, dupSingle.cells(m,1).value) > 0 Then
      ' For p = 1 To y4
      wsc.Activate
      wsc.Rows.Item(n).Select
      wsc.Application.Selection.Copy
      wsb.Activate
      wsb.Rows(m).Select
      wsb.paste 
      wsc.Application.CutCopyMode = False
      On Error Resume Next
      Exit For 
    End If
  Next
Next
GetExcel2.Save

脚本的执行顺利达到certian限制说350行,下一行被复制正常,但几分钟后整个行突出显示红色,我收到一个错误“一个EXCEL遇到错误”然后它关闭了工作簿并打开一个没有任何数据的新工作表...

我们非常感谢您解决此问题的任何帮助 .

问候,Ramesh.T

回答(3)

2 years ago

尝试将复制和粘贴合并为一个步骤 . 替换所有这些代码

wsc.Activate
wsc.Rows.Item(n).Select
wsc.Application.Selection.Copy
wsb.Activate
wsb.Rows(m).Select
wsb.paste 
wsc.Application.CutCopyMode = False
wsc.Application.CutCopyMode = False
On Error Resume Next

wsc.Rows(n).Copy wsb.Rows(m)

2 years ago

有时,我发现通过OLEDB接口处理Excel数据更简单 . 然后,您只需将两个工作表视为两个表,使用标准的DataTable操作,而不是更加变幻无常的自动化操作 . 我只有一个准备好的例子来读取数据,但希望你也可以推断出写作操作:

/// <summary>
    /// Reads an Excel spreadsheet into a new DataTable.
    /// </summary>
    /// <param name="xlsFile">The full file path of the Excel workbook to read from.</param>
    /// <param name="sheetName">The name of the sheet in the workbook to read.</param>
    /// <param name="tableName">The name to give the new DataTable that the spreadsheet is read into.</param>
    /// <param name="firstRowIsHeader">Indicates wheather or not the first row of the spreadsheet is a header row.</param>
    /// <returns></returns>
    internal static DataTable XlsToDataTable(string xlsFile, string sheetName, string tableName, bool firstRowIsHeader)
    {
        var dt = new DataTable(tableName);
        var yesNo = firstRowIsHeader ? "Yes" : "No";
        string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsFile + ";Extended Properties='Excel 8.0;HDR=" + yesNo + ";IMEX=1';";
        using (var ocn = new OleDbConnection(connStr ))
        {
            var cmd = new OleDbCommand("Select * from [" + sheetName + "$]", ocn);
            var datadapt = new OleDbDataAdapter {SelectCommand = cmd};
            datadapt.Fill(dt);
        }
        return dt;
    }

2 years ago

根据我的经验,.Paste不能很好地工作,所以我建议你改变

wsb.paste

wsb.pastespecial

如果改变它,你还有问题吗?

UPDATE:

我不确定这是否会对执行产生任何影响,但我认为中间部分比它需要的更复杂 - 如果用这段代码替换循环的中间部分,它是否有效:

wsc.Activate 
  wsc.Rows(m).Item.Copy 
  wsb.Activate 
  wsb.Rows(n).PasteSpecial

这样,在完成循环之前,您也不需要设置CutCopyMode = False,因此它应该更快 .