Run-time error '1004': PasteSpecial method of Range Class failed (Not able to paste notepad data in Excel)

[Gaurangg]

Hi Folks,

With lot of efforts I am able to copy the data of notepad which I want to paste it in excel. However I face a error that PasteSpecial method of Range Class failed. I guess, after copying the data from notepad, I am not able to activate the excel file, because when I manually select the file and paste the data as text, then the data got pasted. Anyone please can help me in this.

Below is my code which I have written

Sub ImportBoldDump()
Dim ws As Worksheet, strFile As String
    Sheets("Bold Add").Visible = True
    Sheet7.Select
    Range("A:DZ").Select
    Selection.EntireColumn.Delete
    Range("A1").Select
Set ws = ActiveWorkbook.Sheets("Bold Add") 'set to current worksheet name
If Sheet1.Range("CA2").Value = "" Then
MsgBox "There is no any Madallia NPS Dump file selected. Kindly click on 'Select Madallia NPS Dump' button to select the file. Thank you!", vbOKOnly + vbInformation, "NPS Dashboard"
Exit Sub
Else
strFile = ActiveWorkbook.Sheets("Add Data").Range("CA1").Value
If strFile = "" Then
MsgBox "There is no Bold NPS Dump file selected. Kindly click on 'Select Bold NPS Dump' button to select the file and then Click on 'Run'. Thank You!", vbOKOnly + vbInformation, "NPS Dashboard"
Exit Sub
Else

Sheet7.Select

Range("A1").Value = "Check1"
Range("A2").Value = "Check2"
Range("A3").Value = "Check3"
Range("A4").Value = "Check4"
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 2), TrailingMinusNumbers:=True
    Columns("A:A").Select
    Selection.NumberFormat = "@"

'This coding will help me to get the data from notepad to excel without changing the unique ids
    
Dim Owb As Workbook
'Dim VPID As Variant
'TxFile = ActiveWorkbook.Sheets("Add Data").Range("L1").Value
Set Owb = ThisWorkbook

'Ntxname = TxPath & TxName & "*(csv).csv"

'VBA.Shell "C:\windows\notepad.exe " & TxFile, vbNormalFvbNormalFocusocus
Owb.Activate
Sheet7.Select
Range("A1").Select
VPID = Shell("C:\WINDOWS\notepad.exe" & " " & strFile, vbNormalFocus)
SendKeys "^a"
SendKeys "^c"


Call Shell("TaskKill /F /PID " & CStr(VPID), vbHide)

Application.ThisWorkbook.Activate  

Sheets("Bold Add").Select
ThisWorkbook.Sheets("Bold Add").Range("A1").Select

    'Selection.PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Paste  
    'Special xlPasteValues  ''Format:="Text", Link:=False, DisplayAsIcon:=False
    'Used both the paste method but getting error here

(Also would like to clear that I have already tried to get the data from notepad by importing it, opening it as excel, other different coding but I am not getting the data in the way I need. It contains unique id in first column which is 18 - 19 digit and when I open it in excel, last 4 digits converted into 0. While importing it, some data of single row breaks and coaming into next row of excel.)

 

[Norie]

Have you tried moving this down so it executes after you've pasted to Excel?

 

[Gaurangg]

I am unable to get you. As I am unable to paste the data in excel. Can you please help me to understand your suggestion with some brief.

 

[Norie]

Oops, forgot to post the code I was suggesting you move.

This closes down Notepad and it could also be affecting the clipboard which could be why you are having problems pasting.

Call Shell("TaskKill /F /PID " & CStr(VPID), vbHide)

So I'm suggesting you do the pasting before executing this code.

 

[Gaurangg]

Yes, I have tried that too. But then cannot switch to Excel also. owb.Activate / Thisworkbook.Activate doesn't work. hence I need to close the notepad. The problem is even after closing the notepad, when I select the excel manually, and paste special text then the data got pasted. If you can try to test the code. I am mentioning my entire code below.

Sub ImportBoldDump()
Dim ws As Worksheet, strFile As String
    Sheets("Bold Add").Visible = True
    Sheet7.Select
    Range("A:DZ").Select
    Selection.EntireColumn.Delete
    Range("A1").Select
Set ws = ActiveWorkbook.Sheets("Bold Add") 'set to current worksheet name

If Sheet1.Range("CA2").Value = "" Then

MsgBox "There is no any Madallia NPS Dump file selected. Kindly click on 'Select Madallia NPS Dump' button to select the file. Thank you!", vbOKOnly + vbInformation, "NPS Dashboard"
Exit Sub

Else

strFile = ActiveWorkbook.Sheets("Add Data").Range("CA1").Value

If strFile = "" Then

MsgBox "There is no Bold NPS Dump file selected. Kindly click on 'Select Bold NPS Dump' button to select the file and then Click on 'Run'. Thank You!", vbOKOnly + vbInformation, "NPS Dashboard"
Exit Sub

Else

Sheet7.Select

Range("A1").Value = "Check1"
Range("A2").Value = "Check2"
Range("A3").Value = "Check3"
Range("A4").Value = "Check4"
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 2), TrailingMinusNumbers:=True
    Columns("A:A").Select
    Selection.NumberFormat = "@"
    
Dim Owb As Workbook
'Dim VPID As Variant
'TxFile = ActiveWorkbook.Sheets("Add Data").Range("L1").Value
Set Owb = ThisWorkbook

'Ntxname = TxPath & TxName & "*(csv).csv"

'VBA.Shell "C:\windows\notepad.exe " & TxFile, vbNormalFvbNormalFocusocus
Owb.Activate
Sheet7.Select
Range("A1").Select
VPID = Shell("C:\WINDOWS\notepad.exe" & " " & strFile, vbNormalFocus)
SendKeys "^a"
SendKeys "^c"

Call Shell("TaskKill /F /PID " & CStr(VPID), vbHide)
Application.Workbooks("NPS Dashboard.xlsb").Activate   '''changes done by dipesh
'Application.ThisWorkbook.Activate  '' changes done by dipesh

Sheets("Bold Add").Select

ThisWorkbook.Sheets("Bold Add").Range("A1").Select
'Selection.PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Paste
    'Special xlPasteValues  ''Format:="Text", Link:=False, DisplayAsIcon:=False
    
'Call Shell("TaskKill /F /PID " & CStr(VPID), vbHide)

ThisWorkbook.Activate
Sheet7.Select
    'ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
        False
    Columns("A:A").Select
    Selection.Columns.AutoFit
End If

End Sub