1

I need to copy some data from one Excel workbook to another but I only need to copy values and formats. I don't know any other way of doing this but to use the PasteSpecial() method. The problem is that when I run the code I get this error message:

System.Runtime.InteropServices.COMException: 'Microsoft Excel cannot paste the data.'

This is the code I am using at the moment:

    Sub CopyData()

        Dim xlApp As New Excel.Application
        Dim xlWb As Excel.Workbook

        xlApp.DisplayAlerts = False

        Dim xlFileName As String
        Dim OpenFile As New OpenFileDialog

        If OpenFile.ShowDialog() <> DialogResult.Cancel Then
            xlFileName = OpenFile.FileName
            xlWb = xlApp.Workbooks.Open(xlFileName)
        Else
            xlApp.Quit()
            Exit Sub
        End If

        xlApp.Visible = True

        For Each xlWs As Worksheet In xlWb.Worksheets
            If xlWb.Worksheets.Count > 1 Then
                xlWs.Delete()
            End If
        Next

        Dim wsActvSh As Worksheet = Globals.ThisWorkbook.ActiveSheet
        
        For Each ws As Worksheet In Globals.ThisWorkbook.Worksheets
            If ws.Visible = True And Mid(ws.Name, 5, 2) = Mid(wsActvSh.Name, 11, 2) Then
                ws.Range("a:i").Copy()
                xlWb.Worksheets.Add().Name = Mid(ws.Name, 5)
                xlWb.ActiveSheet.Range("a1").PasteSpecial(XlPasteType.xlPasteValues)
            End If
        Next


    End Sub

I tried doing this:

For Each ws As Worksheet In Globals.ThisWorkbook.Worksheets
    If ws.Visible = True And Mid(ws.Name, 5, 2) = Mid(wsActvSh.Name, 11, 2) Then
        xlWb.Worksheets.Add().Name = Mid(ws.Name, 5)
        xlWb.ActiveSheet.Range("a:i").Value = ws.Range("a:i").Value
    End If
Next

But this doesn't work for me because even though the last code does copy the values onto the new worksheet, I am still missing the formats.

Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
Ricman1029
  • 21
  • 4
  • You may see if NuGet package [DocumentFormat.OpenXml](https://github.com/OfficeDev/Open-XML-SDK) meets your needs. If so, the following may also be useful: [Open XML SDK 2.5 Productivity Tool](https://github.com/OfficeDev/Open-XML-SDK#related-tools) and [DocumentFormat.OpenXml Namespace](https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml?view=openxml-2.8.1). – Tu deschizi eu inchid Nov 05 '22 at 23:57

1 Answers1

0

Today I solved the issue. The problem was not the PasteSpecial method itself but the fact that I was trying to paste the ranges in a pre-existing workbook via the OpenFileDialog.

I don't know why this happens since in VBA I was using a similar code opening a pre-existing workbook and never had any problems at all. Although now that I think of it, in VBA I wasn't using the file dialog box to open the workbook. Insted I was using Worbooks.Open() which was causing some problems because users would move the file I was trying to open to different locations in the PC.

Anyway, I am now creating a new Excel file and pasting the data there like this:

Sub CopyData()

    Dim xlApp As New Excel.Application
    Dim xlWb As Excel.Workbook
    Dim wsActvSh As Worksheet = Globals.ThisWorkbook.ActiveSheet
    Dim strLiquidacion As String = Globals.shTbClientes.Range("b:b").Find(Mid(wsActvSh.Name, 1, 3)).Offset(0, 1).Value
    Dim xlWbActSh As Worksheet

    xlApp.DisplayAlerts = False
    xlWb = xlApp.Workbooks.Add()
    xlApp.Visible = True

    For Each ws As Worksheet In Globals.ThisWorkbook.Worksheets
        If ws.Visible = True And Mid(ws.Name, 5, 2) = Mid(wsActvSh.Name, 11, 2) Then
            ws.Range("a:i").Copy()
            xlWb.Worksheets.Add().Name = Mid(ws.Name, 5)
            xlWbActSh = xlWb.ActiveSheet
            xlWbActSh.Range("a:i").PasteSpecial(XlPasteType.xlPasteValues)
            xlWbActSh.Range("a:i").PasteSpecial(XlPasteType.xlPasteFormats)
        End If
    Next

End Sub
Ricman1029
  • 21
  • 4