PDA

View Full Version : PDF into Exce. KB doc



sswcharlie
04-01-2012, 10:15 PM
HI

In the KB doc below there is an option for PDF into Excel.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=341 (http://www.vbaexpress.com/forum/../kb/getarticle.php?kb_id=341)


I have trtied to use the PDF one and no luck, have tried several different PDF but message always says unable to open this document.

I am usingthe Excel workbook on the KB and changing address to suit. I have just removed the address and replaced with c:/desktop/data.pdf

Note that I have used back slashes not forward as here. Some one has changed the keyboard and I have not found the back slash yet.

How does this vba convert the pdf to Excel ?

Any comments welcome

Thanks

Charlie

mohanvijay
04-01-2012, 11:43 PM
if you have Adobe Acrobat then try below KB

http://www.vbaexpress.com/kb/getarticle.php?kb_id=1101

sswcharlie
04-02-2012, 06:04 PM
Hi

Thanks for your post re PDF.

Using the file downloaded from KB I get an error message-
*******
Compile Error: user defined type not definedPrivate Sub cmd_pdf_Click()
********

And highlites 'Dlg_File as FileDialog' in code below

****
Private Sub cmd_pdf_Click()

Dim Dlg_File As FileDialog
Set Dlg_File = Application.FileDialog(msoFileDialogFilePicker)

txt_pdf.Text = ""

****


What do I need to do to fix this.
Thanks and regards
Charlie

Kenneth Hobs
04-02-2012, 07:38 PM
In the VBE, did you look under Tools > References to see if you have any missing objects? Most likely you are not using Excel 2010. Reset the Office object to your version.

sswcharlie
04-02-2012, 09:01 PM
Hi Kenneth

I am using Win7 Excel2000

Have checked as you suggest, the settings are:

MS Excel 9.0 Object Library
MS Office 9.0 Object Library

I am not conversant with Object Libraries, are these the correct for XL2000 ?

Thankyou

Charlie Harris

Kenneth Hobs
04-03-2012, 05:51 AM
Excel is the main object that is already defined for you. The FileDialog is coming from the Office object. I think that 2003 was when it was added. So, the 2000 Office object won't help.

You can use another method to get the filename to process though. Modify it to get the name passed to it or modify the routine to use a method like GetOpenFilename. In the routine below, change it and adapt it to show the PDF files to pick from.

Sub test()
Dim filespec As Variant, i As Integer
'filespec = "c:\myfiles\test.xls"
On Error GoTo EndNow
filespec = Application.GetOpenFileName(FileFilter:="microsoft excel files (*.xls), *.xls", Title:="Get File", MultiSelect:=True)
'MsgBox GetFileName(filespec)
For i = 1 To UBound(filespec)
MsgBox filespec(i), , GetFileName(CStr(filespec(i)))
Next i
EndNow:
End Sub

' MsgBox GetFileName(filespec(0))
Function GetFileName(filespec As String)
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
GetFileName = fso.GetFileName(filespec)
End Function