Oct 11 2021 03:09 AM - edited Oct 12 2021 01:29 AM
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.
Oct 12 2021 12:34 AM
Oct 12 2021 12:45 AM
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
Oct 12 2021 01:33 AM
Oct 12 2021 01:36 AM
its a good thing I pointed it out, now the real vba affecionados can go at it. vba is not my forte
Oct 12 2021 01:39 AM
Oct 18 2021 05:30 AM
Oct 18 2021 06:00 AM
Oct 18 2021 03:49 PM - edited Oct 18 2021 04:01 PM
I found this link, maybe it will be a stepping stone towards your solution go check it out:
import data from outlook email body into access (microsoft.com)
actual vba code: