Question about Copy and paste DataObject in excel worksheet

[zetus]

Hello all,
I have tried to copy PDF file data and using get.DataObject to paste in a excel file. The retrieve function from PDF is working properly. I tried used get.DataObject . But the result is all the data only paste in one cell.
I would like to do the copied data can paste in a excel file that is by order one by one in each cells.


for example,
pdf:

name. Sex. Address
adc. F. *********
adb. M. *********x
tgfd. M. *********

and I want an excel worksheet can show as above format. How can I do this? Thank you so much.

[mana]

Option Explicit

Sub test()
Dim s

With ActiveCell
s = Split(.Value, vbLf)
.Resize(UBound(s) + 1).Value = WorksheetFunction.Transpose(s)
End With

End Sub

[mdmackillop]

I would suggest using an online tool to convert the PDF to Word or Excel (Adobe Export PDF costs 12.70 GBP annually). I find conversions to Word work better, unless the PDF layout is very simple.

[zetus]

thanks so much, test tomorrow

[zetus]

I would suggest using an online tool to convert the PDF to Word or Excel (Adobe Export PDF costs 12.70 GBP annually). I find conversions to Word work better, unless the PDF layout is very simple.

thanks suggestion. the file cannot post to online tool since confidential.

[zetus]

I have tried the script 'Mana' taught.

I post my script to you and please help me to take a look of it. what is the problem of mine and share to me the solution. Thanks




Sub Update()
Dim acrobatID
Dim acrobatInvokeCmd As String
Dim acrobatLocation As String

Dim strClip As String

Dim R As String
Dim s As String
Dim sSplit As Variant
Dim shift As Variant
Dim count As String
Dim total As Long
Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
R = ""
count = 1
temp = ""

strDocument = Application.GetOpenFilename("PDF Files,*.pdf,All Files,*.*", 1, "Open File", , False) ' get pdf document name
If Len(strDocument) < 6 Then Exit Sub

acrobatLocation = "C:\Program Files\Adobe\Reader 11.0\Reader\AcroRd32.exe"
acrobatInvokeCmd = acrobatLocation & " " & strDocument

acrobatID = Shell(acrobatInvokeCmd, 1)

AppActivate acrobatID
For i = 1 To 11
Application.Wait Time + TimeSerial(0, 0, 1)
SendKeys "^a", True
Application.Wait Time + TimeSerial(0, 0, 1)
SendKeys "^c", True

DataObj.GetFromClipboard

If Not R = DataObj.GetText Then
count = count + 1
R = DataObj.GetText()
s = DataObj.GetText()
sSplit = Split(s, vbLf)
For j = LBound(sSplit) To UBound(sSplit)
If Mid(sSplit(j), 1, 3) = "Name" Then
total = total + 1
Cells(total + 3, 3).Value = Mid(sSplit(j), InStr(sSplit(j), " ") + 1, InStr(sSplit(j), " Name ") - 2) 'Name on column C
Cells(total + 3, 1).Value = total 'Serial on column A
End If
Next j
End If
Next
End Sub