PDF to Excel as flat file, via macro

Hi All......

I can record a macro and do this completely as I want to,.......that is,
open the PDF file from within Excel, get Acrobat Reader to then open it, and
copy and paste all text (as a flat column A text) into my Excel
sheet............only problem is, the Copy and Paste steps do not
"record".......

If anyone knows how to do this, I would dearly appreciate the help.

TIA
Vaya con Dios,
Chuck, CABGx3

> sheet............only problem is, the Copy and Paste steps do not
> "record".......

Try to enter the senkey-method to type CTRL+C for you where this should
happen in your recorded macro. I am not sure if this will work but it's
worth a try. You can find details on sendkey in vba-help.

arno

Thanks amo........that's what I'm in the process of now, but not having much
luck...it appears the SendKeys thing is not very user friendly <g>

Vaya con Dios,
Chuck, CABGx3



"arno" wrote:

> > sheet............only problem is, the Copy and Paste steps do not
> > "record".......
>
> Try to enter the senkey-method to type CTRL+C for you where this should
> happen in your recorded macro. I am not sure if this will work but it's
> worth a try. You can find details on sendkey in vba-help.
>
> arno
>
>

> luck...it appears the SendKeys thing is not very user friendly <g>

what you need is:
Application.SendKeys ("^c")

arno

Ohhhh, that looks interesting...............I have to stop off for Blood
Test this morning but will be anxious to try it as soon as I get to
work...........

Many thanks arno...............

Vaya con Dios,
Chuck, CABGx3


"arno" <schoblochr@azoppoth.at> wrote in message
news:uaDEac$EFHA.3824@TK2MSFTNGP10.phx.gbl...
> > luck...it appears the SendKeys thing is not very user friendly <g>
>
> what you need is:
> Application.SendKeys ("^c")
>
> arno

Still no joy in PDFland...........

I've tried
SendKeys "^(c)"
Application.SendKeys ("^c")
before, after, before and after, and in-between my recorded "open the PDF
file" code, and still no luck........once the Reader is open, it just does
not respond to any of my "SendKeys" code, but of course it will respond to
typed Control-C, etc..........

Here's the code I use to open the file, and it works fine........

ActiveSheet.OLEObjects.Add(FileName:= _
"D:\MyPath\Monthly Variance Package 2005-01.pdf" _
, Link:=True, DisplayAsIcon:=False).Select
Selection.Verb

What I really want to do after opening the file is, Ctrl-A to "select all",
Ctrl-C to "copy", Ctrl_q to "quit the application", then paste into A1 in my
sheet............

Any further suggestions would be much appreciated........

Vaya con Dios,
Chuck, CABGx3


"arno" <schoblochr@azoppoth.at> wrote in message
news:uaDEac$EFHA.3824@TK2MSFTNGP10.phx.gbl...
> > luck...it appears the SendKeys thing is not very user friendly <g>
>
> what you need is:
> Application.SendKeys ("^c")
>
> arno

Hi,

write down exactly what you are typing when you do it manually,
including all ALT+TAB etc. Try to to use ALT-TAB to switch between
applications and do not close the reader.

Then use sendkeys with all the commands you have written and run it. I
thing this would be ALT+TAB, CTRL+c, ALT+TAB and then paste. Attention:
CTRL+C (which is actually CTRL+SHIFT+c) is different from CTRL+c. Eg. I
reload an Access query from Excel with:

Sub asAccess()
'ALT+TAB, Shift+F9 and ALT+TAB
Application.SendKeys ("%{TAB}")
Application.SendKeys ("+{F9}")
Application.SendKeys ("%{TAB}")
End Sub

Read vba help on Sendkeys for further information, there's everything
you need.

arno

"CLR" <croberts@tampabay.rr.com> schrieb im Newsbeitrag
news:%23h0stJIFFHA.2232@TK2MSFTNGP14.phx.gbl...
> Still no joy in PDFland...........
>
> I've tried
> SendKeys "^(c)"
> Application.SendKeys ("^c")
> before, after, before and after, and in-between my recorded "open the
PDF
> file" code, and still no luck........once the Reader is open, it just
does
> not respond to any of my "SendKeys" code, but of course it will
respond to
> typed Control-C, etc..........
>
> Here's the code I use to open the file, and it works fine........
>
> ActiveSheet.OLEObjects.Add(FileName:= _
> "D:\MyPath\Monthly Variance Package 2005-01.pdf" _
> , Link:=True, DisplayAsIcon:=False).Select
> Selection.Verb
>
> What I really want to do after opening the file is, Ctrl-A to "select
all",
> Ctrl-C to "copy", Ctrl_q to "quit the application", then paste into
A1 in my
> sheet............
>
> Any further suggestions would be much appreciated........
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "arno" <schoblochr@azoppoth.at> wrote in message
> news:uaDEac$EFHA.3824@TK2MSFTNGP10.phx.gbl...
> > > luck...it appears the SendKeys thing is not very user friendly
<g>
> >
> > what you need is:
> > Application.SendKeys ("^c")
> >
> > arno
>
>

Okey doke arno, I'll give it a try.............thanks

Vaya con Dios,
Chuck, CABGx3


"arno" <schoblochr@azoppoth.at> wrote in message
news:#Z2FPMMFFHA.228@TK2MSFTNGP15.phx.gbl...
> Hi,
>
> write down exactly what you are typing when you do it manually,
> including all ALT+TAB etc. Try to to use ALT-TAB to switch between
> applications and do not close the reader.
>
> Then use sendkeys with all the commands you have written and run it. I
> thing this would be ALT+TAB, CTRL+c, ALT+TAB and then paste. Attention:
> CTRL+C (which is actually CTRL+SHIFT+c) is different from CTRL+c. Eg. I
> reload an Access query from Excel with:
>
> Sub asAccess()
> 'ALT+TAB, Shift+F9 and ALT+TAB
> Application.SendKeys ("%{TAB}")
> Application.SendKeys ("+{F9}")
> Application.SendKeys ("%{TAB}")
> End Sub
>
> Read vba help on Sendkeys for further information, there's everything
> you need.
>
> arno
>
> "CLR" <croberts@tampabay.rr.com> schrieb im Newsbeitrag
> news:%23h0stJIFFHA.2232@TK2MSFTNGP14.phx.gbl...
> > Still no joy in PDFland...........
> >
> > I've tried
> > SendKeys "^(c)"
> > Application.SendKeys ("^c")
> > before, after, before and after, and in-between my recorded "open the
> PDF
> > file" code, and still no luck........once the Reader is open, it just
> does
> > not respond to any of my "SendKeys" code, but of course it will
> respond to
> > typed Control-C, etc..........
> >
> > Here's the code I use to open the file, and it works fine........
> >
> > ActiveSheet.OLEObjects.Add(FileName:= _
> > "D:\MyPath\Monthly Variance Package 2005-01.pdf" _
> > , Link:=True, DisplayAsIcon:=False).Select
> > Selection.Verb
> >
> > What I really want to do after opening the file is, Ctrl-A to "select
> all",
> > Ctrl-C to "copy", Ctrl_q to "quit the application", then paste into
> A1 in my
> > sheet............
> >
> > Any further suggestions would be much appreciated........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "arno" <schoblochr@azoppoth.at> wrote in message
> > news:uaDEac$EFHA.3824@TK2MSFTNGP10.phx.gbl...
> > > > luck...it appears the SendKeys thing is not very user friendly
> <g>
> > >
> > > what you need is:
> > > Application.SendKeys ("^c")
> > >
> > > arno
> >
> >
>

On Mon, 14 Feb 2005 07:35:03 -0800, CLR <CLR@discussions.microsoft.com>
wrote in microsoft.public.excel.programming:

>I can record a macro and do this completely as I want to,.......that is,
>open the PDF file from within Excel, get Acrobat Reader to then open it, and
>copy and paste all text (as a flat column A text) into my Excel
>sheet............only problem is, the Copy and Paste steps do not
>"record".......
>
>If anyone knows how to do this, I would dearly appreciate the help.

I do something similar by extracting the relevant information from the
PDF file with pdftotext from <http://www.foolabs.com/xpdf/> and
subsequent batch-editing. For that I use 4NT's string manipulation
features, but sed or similar would probably do it as well. The resulting
file can then be imported as a CSV file.

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

Much Joy exists..........I finally got there.......

Many thanks for all of your time and assistance arno

Vaya con Dios,
Chuck, CABGx3


"arno" wrote:

> Hi,
>
> write down exactly what you are typing when you do it manually,
> including all ALT+TAB etc. Try to to use ALT-TAB to switch between
> applications and do not close the reader.
>
> Then use sendkeys with all the commands you have written and run it. I
> thing this would be ALT+TAB, CTRL+c, ALT+TAB and then paste. Attention:
> CTRL+C (which is actually CTRL+SHIFT+c) is different from CTRL+c. Eg. I
> reload an Access query from Excel with:
>
> Sub asAccess()
> 'ALT+TAB, Shift+F9 and ALT+TAB
> Application.SendKeys ("%{TAB}")
> Application.SendKeys ("+{F9}")
> Application.SendKeys ("%{TAB}")
> End Sub
>
> Read vba help on Sendkeys for further information, there's everything
> you need.
>
> arno
>
> "CLR" <croberts@tampabay.rr.com> schrieb im Newsbeitrag
> news:%23h0stJIFFHA.2232@TK2MSFTNGP14.phx.gbl...
> > Still no joy in PDFland...........
> >
> > I've tried
> > SendKeys "^(c)"
> > Application.SendKeys ("^c")
> > before, after, before and after, and in-between my recorded "open the
> PDF
> > file" code, and still no luck........once the Reader is open, it just
> does
> > not respond to any of my "SendKeys" code, but of course it will
> respond to
> > typed Control-C, etc..........
> >
> > Here's the code I use to open the file, and it works fine........
> >
> > ActiveSheet.OLEObjects.Add(FileName:= _
> > "D:\MyPath\Monthly Variance Package 2005-01.pdf" _
> > , Link:=True, DisplayAsIcon:=False).Select
> > Selection.Verb
> >
> > What I really want to do after opening the file is, Ctrl-A to "select
> all",
> > Ctrl-C to "copy", Ctrl_q to "quit the application", then paste into
> A1 in my
> > sheet............
> >
> > Any further suggestions would be much appreciated........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "arno" <schoblochr@azoppoth.at> wrote in message
> > news:uaDEac$EFHA.3824@TK2MSFTNGP10.phx.gbl...
> > > > luck...it appears the SendKeys thing is not very user friendly
> <g>
> > >
> > > what you need is:
> > > Application.SendKeys ("^c")
> > >
> > > arno
> >
> >
>
>

I appreciate your information Michael, but that stuff is 'way over my
head......no matter tho, the problem was just solved......

Many thanks for your response....

Vaya con Dios,
Chuck, CABGx3


"Michael Bednarek" wrote:

> On Mon, 14 Feb 2005 07:35:03 -0800, CLR <CLR@discussions.microsoft.com>
> wrote in microsoft.public.excel.programming:
>
> >I can record a macro and do this completely as I want to,.......that is,
> >open the PDF file from within Excel, get Acrobat Reader to then open it, and
> >copy and paste all text (as a flat column A text) into my Excel
> >sheet............only problem is, the Copy and Paste steps do not
> >"record".......
> >
> >If anyone knows how to do this, I would dearly appreciate the help.
>
> I do something similar by extracting the relevant information from the
> PDF file with pdftotext from <http://www.foolabs.com/xpdf/> and
> subsequent batch-editing. For that I use 4NT's string manipulation
> features, but sed or similar would probably do it as well. The resulting
> file can then be imported as a CSV file.
>
> --
> Michael Bednarek http://mbednarek.com/ "POST NO BILLS"
>