i have
office 2007
adobe reader 11
i need
vba excel to get page size of a non standard pdf document
what i tried gave me a 429 error code
can somebody help me
sorry for my english
Printable View
i have
office 2007
adobe reader 11
i need
vba excel to get page size of a non standard pdf document
what i tried gave me a 429 error code
can somebody help me
sorry for my english
that does not tell us much, post the code you triedQuote:
what i tried gave me a 429 error code
you may need to have adobe acrobat (not reader) to do this
there is an example at http://www.vbaexpress.com/forum/show...-PDF-page-size
i already tried this . not working . error code = 429
thank for your suggestion.
still waiting !
probably because you do not have adobe acrobatQuote:
error code = 429
i try with adobe acrobat DC too.
Same result.
Again,
Thank you for answer and time.
What I have to do ?
I need to open a pdf , colect 7 information and put them in a excel table .
2 of information are dimension of page .
if i do that automaticly i ' ll gain time .
i am still study.
again, sorry for my english
as i do not have adobe acrobat, i can not try the code
you can test this to see if it works for you
change pdf file name to suitCode:Open "C:\Users\PC\Downloads\Excel Services Troubleshooting (2).pdf" For Binary As 1
sfile = Input(LOF(1), 1)
Close 1
pos = InStr(sfile, "/MediaBox") + 10
nd = InStr(pos, sfile, "]")
mb = Mid(sfile, pos, nd - pos)
a = Split(mb)
MsgBox "page size = " & a(3) / 72 & " x " & a(4) / 72
in mb i have the solution.
but i have those 40-50 second delay for input that make me impossible to use it
you may be able to reduce the delay by changing the input method to only read the first part of the file
can you help me again with a code ?
with small files this will make no difference, but with large files it will only read the file upto the part required, co may be faster, i have no files large enough to make a significant difference so you will have to testCode:Open "C:\Documents and Settings\pete\My Documents\printouts\motel near Essendon VIC - Google Maps.pdf" For Binary As 1
Do Until EOF(1)
Line Input #1, sfile
pos = InStr(sfile, "/MediaBox") + 10
If pos > 10 Then Exit Do
Loop
Close 1
nd = InStr(pos, sfile, "]")
mb = Mid(sfile, pos, nd - pos)
a = Split(mb)
MsgBox "page size = " & a(2) / 72 & " x " & a(3) / 72
if the mediabox is always at the same location GET might be even better, but o would assume that the data before that point can vary a lot
also note: the array returned from mediabox may vary in the number of elements so might have to test for valid values
it seem to be worse . aprox 3 min.
Until i have a better solution
i will use your initial code to collect info - when i am not using the computer let him rename pdf with the info ( not doing a new table in excel)
and
modify my code to collect info from the name of pdf or collect manually if the pdf don' have info
I don't have and i don't find knowledge to use INPUT, /MediaBox , LineInput as you do.
any reference to find it?
About GET
I don't know position of MediaBox for my file
and , i think, that is possible too to return to error code ( create objects ... 429 ) ?
Thank you.
Still waiting.
dunno, don't have adobe acrobatQuote:
i think, that is possible too to return to error code ( create objects ... 429 )
if you test with multiple pdf files you may be able to calculate the approximate position of mediabox within all files comparing the value of pos to the lof, obviously it will not always be a the same position, but may be able to determine a likely starting point back from the end of file
as i said , i don't have knowledge to use those instruction as you do .
what i understand is that is possible to have a shorter time to count from the back .
great idea but i don' t know how to do that .
can you help me with a code ?
my pdf is from 210 x 297 to 914 x 5000 (a few longer )
i have a first result using you initial code in my code : 14 files in 6-7 min.
soo the average is better that my initial testing .
i will test a new code ,if you send to me , and tell the result.
when i will have the solution the i will use , i will post it.
thank you for your time
i do not really have a great variety of pdf files to test with, i will run some results as a test later
i used this code to test
the code ran through 33 files in 6.3 secondsCode:myp = "C:\Documents and Settings\pete\My Documents\printouts\"
fname = Dir(myp & "*.pdf")
t = Timer
Do While Len(fname) > 0
Open myp & fname For Binary As 1
l = LOF(1)
sfile = Input(LOF(1), #1)
Close 1
pos = InStr(sfile, "/MediaBox")
Debug.Print fname, pos, l, pos / l
fname = Dir
Loop
Debug.Print "total time: "; Timer - t
while there is no useful results to find where the media box is in the file, i do find it difficult to understand while your tests are so slow unless you are working with very large filesQuote:
6316.pdf 1618 16765 0.096510587533552
6317.pdf 1739 16851 0.103198623227108
6329.pdf 1835 16963 0.108176619701704
6350.pdf 2125 17519 0.121296877675666
6355.pdf 1613 16825 9.58692421991085E-02
ANZ Rewards - Frequent Flyer.pdf 7113 51817 0.137271551807322
ANZ Rewards - Frequent Flyer2.pdf 7090 51875 0.136674698795181
ANZ Rewards - Redeem cart confirmation.pdf 8765 59327 0.147740489153337
atest.pdf 186 197095 9.43707349247825E-04
atest2 (1).pdf 155 2517 6.15812475168852E-02
atest2 (2).pdf 189 197108 9.58865190656899E-04
atest2.pdf 7359 14735 0.499423142178487
barcode.pdf 1837 13533 0.135742259661568
Book1.pdf 258 3722 6.93175711982805E-02
Booking.com Confirmation.pdf 13516 50552 0.267368254470644
Buy delivery on eBay.pdf 3616 18149 0.199239627527687
CASTAGNA Biodynamic Vineyard - Beechworth Victoria.pdf 29640 121135 0.244685681264705
castagna.pdf 19979 117083 0.170639631714254
gst_invoice1.pdf 1788 12422 0.143938174207052
hw.pdf 278 4114 6.75741370928537E-02
iBank.pdf 1946 21927 8.87490308751767E-02
lvp.pdf 189 16611 0.011378002528445
motel near Essendon VIC - Google Maps.pdf 1737 571305 3.04040748811931E-03
motel near Essendon VIC - Google Maps2.pdf 1738 378284 4.59443169682038E-03
MSDataReport.pdf 335 4824 6.94444444444444E-02
pportapp.pdf 34920 196880 0.177366924014628
Range-Rover-Maunual-Electrics.pdf 7067 157395 4.48997744528098E-02
res.pdf 5967 16195 0.368447051559123
vb.pdf 42295 60682 0.696994166309614
vb1.pdf 42279 60668 0.696891277114789
vb2.pdf 42149 60538 0.696240377944432
vb3.pdf 42153 60542 0.696260447292788
Xl0000073.pdf 137395 160588 0.855574513662291
total time: 6.34375 For 33 files
thank you
i never test with other pdf that mine .
i have same result as you.
it seem that my pdf have a specific touch .
the media box, too, is in a different position , but i can adjust this .(auto)
i'm study now you last code (it seem to be faster (5-15 %) --- that's very good to 45 second
i'll tell you the result .
i had integrated you code in my code, it was the weekend , i have a celebration in family... sorry for my late response !
If you or somebody else can improuve this please do !!! i’ m waiting
First code take file fron INITILAL rename with new with info about size and send them to FINAL
If a proper pdf have spaces in the name modify the name and send it to FINAL
When i want to work to them i move them to SCANARI and work in my vba who include second code
I use excel formula to get new name and format because i allready have made this
FOR WHO WILL TRY !!!
!!! This is part of bigger code !!! You must use as idee and to modify to develop you needs !!!
I have 5 scaners with 4 different touch
I modifify my code . I hope i don’t forget any thing .
FIRST
’ find source of pdf
’ modifify name if have spaces
Sheets("DATA").Select
ActiveSheet.Unprotect
Range("A1") = "I’m working ! Wait !"
10 FNF = Dir("C:\FORMAT\INITIAL\*.pdf", vbDirectory)
If FNF <> "" Then
LL = FNF
Aa = Split(LL)
bposI1 = InStr(LL, " ")
MM = Right(LL, Len(LL) - bposI1)
bposI2 = InStr(MM, " ")
RR = Right(LL, Len(LL) - bposI1 - bposI2)
bposI3 = InStr(RR, " ")
NoNoRangeF = Right(LL, Len(LL) - bposI1 - bposI2 - bposI3)
If RR <> NoNoRangeF Then
' file with more then 2 space in name
For i = 1 To 5
Range("A1") = "!!! no range file !!!"
Application.Wait (Now + TimeValue("0:00:01"))
Range("A1") = ""
Application.Wait (Now + TimeValue("0:00:01"))
Next i
GoTo 5
End If
If bposI1 = 0 Then
' file with no space in name
Aa0 = Left(Aa(0), Len(Aa(0)) - 4)
’ send info to excel
Range("M2") = Aa0
Range("M3") = ""
Range("M4") = ""
’ take info from excel - new name
FNF = Range("N1")
GoTo 20
Else
If bposI2 = 0 Then
' file with 1 space in name
Aa1 = Left(Aa(1), Len(Aa(1)) - 4)
’ send info to excel
Range("M2") = Aa(0)
Range("M3") = Aa1
Range("M4") = ""
’ take info from excel - new name
rN = Range("N1")
Shell ("CMD.EXE /c MOVE c:\FORMAT\INITIAL""" & FNF & """ c:\FORMAT\INITIAL""" & rN & """")
Application.Wait (Now + TimeValue("0:00:03"))
FNF = Range("N1")
GoTo 20
Else
' file with two space
Aa2 = Left(Aa(2), Len(Aa(2)) - 4)
’ send info to excel
Range("M2") = Aa(0)
Range("M3") = Aa(1)
Range("M4") = Aa2
rN = Range("N1")
Shell ("CMD.EXE /c MOVE c:\FORMAT\INITIAL""" & FNF & """ c:\FORMAT\INITIAL""" & rN & """")
Application.Wait (Now + TimeValue("0:00:03"))
FNF = Range("N1")
GoTo 20
End If
End If
’ now find info from diferent scaner
20 Range("k1") = j
j = j + 1
pn = Left(FNF, 2)
Range("d1") = pn
If pn = "sc" Then
mbx = 0
Range("d2") = mbx
Else
If pn = "ky" Then
mbx = 10
Range("d2") = mbx
Else
If pn = "S2" Or pn = "S4" Then
mbx = 2
Range("d2") = mbx
Else
mbx = 1
Range("d2") = mbx
End If
End If
End If
nume_vechi = FNF
Open "C:\FORMAT\INITIAL" & FNF For Binary As 1
L = LOF(1)
sfile = Input(LOF(1), #1)
Close 1
pos = InStr(sfile, "/MediaBox") + 14 + mbx
nd = InStr(pos, sfile, "]")
mb = Mid(sfile, pos, nd - pos)
a = Split(mb)
Sheets("DATA").Select
Range("B2") = nume_vechi
Range("A4") = a(0)
Range("A5") = a(1)
Range("A6") = mb
nume_nou = Range("C2")
Shell ("CMD.EXE /c MOVE c:\FORMAT\INITIAL""" & nume_vechi & """ c:\FORMAT\FINAL""" & nume_nou & """.pdf")
Application.Wait (Now + TimeValue("0:00:03"))
GoTo 10
Else
For I = 1 To 4
Range("A1") = "no more file !"
Application.Wait (Now + TimeValue("0:00:04"))
Range("A1") = ""
Application.Wait (Now + TimeValue("0:00:01"))
Next I
Range("A1") = "ready to work !"
End If
5 Range("A7") = Timer – t
...
End If
SECOND
’ take info size from name file
xFN = Dir("C:\SCAN\*.pdf", vbDirectory)
If xFN <> "" Then
vechi = xFN
' ThisWorkbook.FollowHyperlink "C:\SCANARI" & xFN
' aici introducere format si dimensiune
L = xFN
a = Split(L)
bpos1 = InStr(L, " ")
M = Right(L, Len(L) - bpos1)
bpos2 = InStr(M, " ")
R = Right(L, Len(L) - bpos1 - bpos2)
bpos3 = InStr(R, " ")
NoRangeF = Right(L, Len(L) - bpos1 - bpos2 - bpos3)
If R <> NoRangeF Then
’ what to do if non range file
Else
End If
If bpos1 = 0 Then
’ what to do if must be with same name
Else
If bpos2 = 0 Then
' fisier A4 sau A3
a1 = Left(a(1), Len(a(1)) - 4)
’ send to excel first dimention
Range("J9") = a1
Else
’ send to excel first dimention
Range("J9") = a(1)
a2 = Left(a(2), Len(a(2)) - 4)
’ send to excel second dimention
Range("ad3") = a2
End If
End If