Problem in Copying the data one Excel to another Excel using VBSCript

Question

Hi All,

I have an excel sheet which contains around 1000 lines of data, i have copy these data to another sheet which satisfy the condition. In order to achieve this i have written a script,

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

The execution of the script goes well up to certian limit say 350 rows the next row was getting copied fine but the entire row was highlight in Red Color after few minutes, I am getting an error " An EXCEL Encountered an error " then it closes the workbook and opens a new sheet without any data...

Any help to resolve this issue is much appreciated.

With Regards, Ramesh.T

Answer 1

I have, on occasion, found it simpler to deal with Excel data through an OLEDB interface. Then you simply treat two sheets as two tables, with standard DataTable operations instead of the more fickle Automation operations. I only have a ready example for reading data, but hopefully you can extrapolate writing operations as well:

    /// <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;
    }

Answer 2

It's my experience that the .Paste doesn't work that well, so I'd recommend that you change

wsb.paste 

to

wsb.pastespecial

Do you still have problems if you change that?

UPDATE:

I'm not sure if this will make any difference in the execution, but I think the middle section is more complicated than it needs to be - does it work if you replace the middle section of the loop with this code:

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

That way, you also won't need to set the CutCopyMode = False until you're totally done with the loops, so it should be faster.

Answer 3

Try combining the copy and pasting into a single step. Replace all this code

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

with

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