PDA

View Full Version : Count number of rows in a pdf?



JKB
12-11-2015, 01:03 AM
Hi everybody!

Im in a bit of a hick up. I have two columns in a pdf, which by using VBA im copying into excel. My problem is that when im doing this it pastes the data in as an entire row, i.e. if this is my pdf with two columns X,Y:

X Y
X Y
X Y
X Y

When pasting this into excel i get

X
Y
X
Y
X
Y
X
Y

My problem is that i would like to assign the data from each column with a 0 or 1 depending on which column it came from, and if the two columns are not exactly the same length, i have a problem (Else it would ofc. just be every second value assigned with a 0 or 1)

I think i might be able to solve the problem, if i could somehow know how many observations i have in each column in the PDF. Does anybody have a better suggestion to solve the problem, or by any chance know how i can get the amount of rows in each (or just one) column?

Hope someone can help! Again it's not a problem to get the data into a sheet :)

Cheers!!!

snb
12-11-2015, 04:46 AM
Please show your VBA-code.

JKB
12-11-2015, 06:15 AM
This code loops through all PDF's in a folder, copies the individual PDF, closes it, and then it's supposed to manipulate this data (this part has not been written yet) and then do the same to the next PDF and so on
- Again my problem is that i cant determine which column the data is coming from..

Copying using ctrl+a, control+c, control v (or SendKeys ("^a"), SendKeys ("^c"), SendKeys "%{F4}", True

This way of doing it removes the format from the pdf when pasting into excel...



Sub Sheet2_Button1_Click()
Dim strPath As String
Dim MyFile As String
Dim Adobefile
Dim sep As String: sep = Application.PathSeparator
Dim shTemp As Worksheet: Set shTemp = Worksheets("Temp")
Dim shMain As Worksheet: Set shMain = Worksheets("Main")
Dim i As Integer
Dim iLastRow As Integer
Dim sTemp As String
Dim CreatedDate
strPath = "C:\Users\jkb\Desktop\PDF"
MyFile = Dir(strPath & sep & "*.pdf")
Do While MyFile <> ""
ActiveWorkbook.FollowHyperlink strPath & sep & MyFile
SendKeys ("^a")
SendKeys ("^c")
Application.Wait (Now + TimeValue("00:00:02"))
SendKeys "%{F4}", True
AppActivate "Microsoft Excel"
shTemp.Activate
ActiveSheet.Range("A1").PasteSpecial
'------------------------------------------------------------------------------------------------------------------
'Handling data
' Planning to manipulate the data in this section, but havent written code yet, since this part is not important if i cant specify which of the two columns the data came from :)
'Next file
NextFile:
MyFile = Dir()
Loop
End Sub