Forum Discussion

Gertdj's avatar
Gertdj
Copper Contributor
Oct 11, 2021

VBA Copy table from email to Excel - Cannot paste the data - 1004

Hi,

I am trying to copy a table from email and paste it into Excel with VBA for further manipulation.

I found code on the internet and all seems to work 100%, except the final part where I need to paste the table into the new worksheet.

 

I get the following error: "Runtime error 1004 - Microsoft Excel cannot paste the data".

If I switch to the open workbook I can manually paste using Ctrl + V and the it works perfectly.

 

Please help - I have spend so much time on this and is unable to find a solution.

 

 

Macros and VBA

9 Replies

    • Gertdj's avatar
      Gertdj
      Copper Contributor
      Oct 12, 2021
      Yea_So
      Thank you for the suggestion - I will definitely investigate further.
      I am grabbing a table from the body of an email though and then sending it to Excel for further manipulation - not sure if Power Query is the best tool for this... disclaimer - I haven't worked with Power Query much before...
      • Yea_So's avatar
        Yea_So
        Bronze Contributor
        Oct 12, 2021

        Gertdj 

         

        I only suggested it because your code mentioned pdf, as pdf can only be an attachment unless it is an embedded object.

         

        Sub PDF_To_Excel()

         

        Dim setting_sh As Worksheet
        Set setting_sh = ThisWorkbook.Sheets("Setting")

        Dim pdf_path As String
        Dim excel_path As String

        pdf_path = setting_sh.Range("E11").Value
        excel_path = setting_sh.Range("E12").Value


        Dim fso As New FileSystemObject
        Dim fo As Folder
        Dim f As File

        Set fo = fso.GetFolder(pdf_path)

        Dim wa As Object
        Dim doc As Object
        Dim wr As Object

        Set wa = CreateObject("word.application")

        wa.Visible = True

        Dim nwb As Workbook
        Dim nsh As Worksheet

        For Each f In fo.Files
        Set doc = wa.documents.Open(f.Path, False, Format:="PDF Files")
        Set wr = doc.Paragraphs(1).Range
        wr.WholeStory

        Set nwb = Workbooks.Add
        Set nsh = nwb.Sheets(1)
        wr.Copy

        nsh.Paste
        nwb.SaveAs (excel_path & "\" & Replace(f.Name, ".pdf", ".xlsx"))

        'Clipboard 123
        doc.Close False
        nwb.Close False

        Next

        wa.Quit

        MsgBox "Done"

         

        End Sub

Resources