I am having trouble with the Range.PasteSpecial() method in VB.net-CodePudding

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.

CodePudding user response：

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