Free Microsoft Excel 2013 Quick Reference

Open pdf file with vba Results

Esteemed VBA Geeks!

I am looking to open a known PDF file, (i.e. I know what name the PDF file is), from VBA. The problem is that I do not know where the Adobe Reader program will reside on the customer computer.

My wrong guess was to:

[mc] Shell "C:Bob.pdf"

But within VBA that does not work.

What does work is:

[mc] t = "C:Program FilesAdobeReader
[mc] 8.0ReaderAcroRd32.exe"
[mc] Shell """" + t + """" + "C:Bob.pdf"

Except for my computer, I will not know where the Adobe Reader is on
the customer computer that will be running the application code!

From a command prompt:

[mc] C:Bob.pdf

Does open the designated PDF file, (Bob) I am looking for a command in VBA that does the same thing.

As always your help is greatly appreciated!

Thank You

Choppork

0940 CST
1/11/2008

Hello,

Is it possible to open a pdf file with VBA Code and count the opened pdf, and the count should listed in the Excel, After all close the pdf file.

Is it possible, I know how to open a Pdf file with Excel.

Waiting for your Reply,
Krish

Hello

I'd like to create PDF documents with VBA. For this I downloaded the PDF995
writer. I created the following macro:

Sub Print_PDF()
ActiveSheet.PrintOut _
ActivePrinter:="PDF995 on Ne03:", _
PrintToFile:=True, _
PrToFilename:="C:TempTest.pdf"
End Sub

The pdf file was created but I'm not able to open it with the Adobe Acrobat
Reader. The error message 'unknown format' appears. What am I doing wromg?

Tom

Hello,
I am using the code below to open the site which contains PDF files with a date last updated. Can the code be modified to save the last updated to my C:PDF Download folder?

Mike

----------------------------------------------------
Sub Open_FTP()

Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
'Go to login page
.Navigate "ftp:// " ' Substitute with FTP Path
Application.Wait Now + TimeValue("00:00:02")

' Loop until the page is fully loaded
Do Until .ReadyState = 4
DoEvents
Loop

End With

End Sub

I'm writing a macro in Excel which finds other files (not Excel files)
with a given name and then opens them. I'm using the Windows registry
to find the programs associated with the files and then using the Shell
command to open the files with the given program. My problem is that
for some programs the Shell command opens a new instance of the program
for each additional file, i.e. each AutoCAD file opens into its own
instance of AutoCAD even if AutoCAD is already running.

So my question is, is there a switch for the Shell command that tells
it to check to see if an instance of a program is already running
before starting a new one, and, if the program is already running,
opens the file with the existing instance? Or is there another way of
doing this without getting into the API of each program in question?

Here's a snippet of my code:

strCmd = appWord.System.PrivateProfileString("", _
"HKEY_CLASSES_ROOT" & regType & "shellOpencommand", _
"")

If Len(strCmd) > 0 Then
strCmd = Replace(strCmd, "%1", fileWithPath) 'for pdf & dwg files
strCmd = Replace(strCmd, "/dde", "/one " & """" & fileWithPath &
"""") 'for solidworks files
Shell strCmd, vbNormalFocus
Else
MsgBox prompt:="Could not find an application" & vbCr & _
"registered to display file.", _
Buttons:=vbCritical + vbOKOnly, _
Title:="Not Registered"
End If

Thanks!
Eric

I am looking for some help with creating a macro button in excel that would be used to print out a specific range of pdf documents via hyperlinks.

The macro steps would be as follows:

1. Open pdf file by selecting hyperlink
2. Print open file to specific printer
3. Close open pdf file
4. Repeat steps 1 to 3 for remaining specified files

I've tried recording the process in Excel but I am only able to get the 1st pdf file open (which I could have done by cliking on it!)

If anyone has any suggestions or tips then please get in touch. I can upload example files if required.

My knowledge of macros & VBA is very limited...

Regards,

Bryan

Hi all,

I am opening a PDF document fromm VBA excel. After opening document it should save the document as excel file or text file in one folder.
I am trying to do this with some code but not able to , pls help me thatnks.


	VB:
	
 
Dim AcroApp As Acrobat.CAcroApp 
Dim Part1Document As Acrobat.CAcroPDDoc 
Set Part1Document = CreateObject("AcroExch.PDDoc") 
Part1Document.Open ("Z:EG MI InformationMISRequestsReq_156NO.1.pdf") 
 
 
 
 
Dim app As Object, avdoc As Object, pageview As Object 
Set app = CreateObject("AcroExch.App") 
Set avdoc = app.GetActiveDoc 
app.MenuItemExecute ("SaveAs") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
thanks
Abhijeet

Hi.

I've been playing with Excel for years but never used Macros and VBA. I always had found a solution using excel functions.

Now I had the need (and probably lack of knowledge) to use vba.

Google is our friend and I was able to learn enough from online examples.

Currently I have a network share with several dirs(ex: 046-10) and files using a specific naming.

I identified a range where dir numbers must be placed. Those are then used to check inside the each identified dir for files.

My request to the forum:
I only want process the files which contain at least one cell with a string ex:"AQUISIO" in a range E22:E54.

The purpose is to fill in a table in the file were the macro is going to be run.

I got enough info to build this code (which works fine, even there are errors/wrong coding in it, specially because I didn't investigate the variables declaration):


	VB:
	
 Macro4() 
     '
     ' Macro4 Macro
     '
     ' Atalho por teclado: Ctrl+q
     '
    Dim tamanho As Variant 
    Dim l 
    Dim folder As Variant 
    Dim d_ot As Range 
    Dim i_ot      As Range 
    Dim req As Range 
    Dim caminho 
    Dim file   As Variant 
    Dim custo_est As Range 
    Const partilha = "F:RQ" 
     
     
    With CreateObject("Scripting.FileSystemObject") 
         
        Set req = ActiveSheet.Cells(16, 1) 
        Set d_ot = ActiveSheet.Cells(16, 2) 
        Set i_ot = ActiveSheet.Cells(16, 3) 
        Set custo_est = ActiveSheet.Cells(16, 4) 
        Set c = ActiveSheet.Cells(10, 7) 
        tamanho = Range("J65536").End(xlUp).Row 
        For l = 12 To tamanho 
             
            folder = ActiveSheet.Cells(l, 10) 
             'faltam os 2 if para limitar a seleco aos req com pdf !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
             'I will filter the files by extention(xls) and also to the one that have a pdf file with the saame name ( ex:
a.xls and a.pdf). I already know how to do this part.
             
            caminho = partilha & folder 
             
            For Each file In .GetFolder(caminho).Files 
                 
                req.Formula = "='" & file.ParentFolder & "[" & file.Name & "]Folha1'!$A$8" 
                Set req = req.Offset(1) 
                d_ot.Formula = "='" & file.ParentFolder & "[" & file.Name & "]Folha1'!$B$13" 
                Set d_ot = d_ot.Offset(1) 
                i_ot.Formula = "='" & file.ParentFolder & "[" & file.Name & "]Folha1'!$B$14" 
                Set i_ot = i_ot.Offset(1) 
                custo_est.Formula = "='" & file.ParentFolder & "[" & file.Name & "]Folha1'!$F$55" 
                Set custo_est = custo_est.Offset(1) 
            Next 
             
        Next l 
         
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As stated before, this works fine for all files in the previously identified dirs from row J starting in cell 12 of the current opened file ( this file is in a different dir and maybe even different PC .
I need to limit this analysis to condition mentioned above.

I will also limit the files to be checked using this code:


	VB:
	
 
    If Right(.GetBaseName(file), 3) = "REQ" Then 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And will adapt this one to limit the files to the ones with a pdf file with the same name(on the same dir):


	VB:
	
 
Dim fileNameExt As String 
Dim fileName As String 
Dim fileNameAdd As String 
 
With CreateObject("Scripting.FileSystemObject") 
     
    path = Application.ActiveWorkbook.path 
    fileNameExt = Application.ActiveWorkbook.Name 
    fileName = (.GetBaseName(fileNameExt)) 
    fileNameAdd = path + "" + fileName + ".pdf" 
     
     'If pdf file exists
    If Len(Dir(fileNameAdd))  0 Then 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Cheers

Hi

I am trying to get excel to open a file via hyperlink using VBA. I can get it to do this - I have a problem:

The files I am hyperlinking to are pdf files and they are numbered (12571.pdf), however, some are numbered with a range (12571-12590.pdf) and I want it to open that file if the user enters a number within the range.

Or if that isn't possible, then if an error comes back on the single number (file not found) then I want it to try the number below (if 12572 was the user choice then it would change number to 12571 and open file starting with).

Hopefully that made sense! Any help would be greatly appreciated!

Aaron

Hi,
I created userform for RMA now I have been asked to add a way to add an attachment
with VBA, to attache file, is there away to open a browser or what is the best way to do it?
Many thanks
Assaf

Hey all,

I've created an userform interface for an "engineering drawing" database at my job that can be used to add, search, edit and delete drawings to/from the database. Nothing too fancy, the database is in the same workbook as the userform and it collects 6 different criteria of information (i.e. Drawing #, Date, Customer, etc..).

More importantly it can also be used to open up user selected pdf engineering drawing files that are located in a network folder. Basically, the user searches for a drawing number in the excel workbook (i.e. - 11425) and then has the option to open up the corresponding engineering drawing pdf file that's located in a network folder.

The issue I'm having is that sometimes a drawing has a few revisions in the network folder and can be labeled as such: 11425, 11425-R1, 11425-R2, etc... I want to be able to open all files containing the root drawing number (11425) so if there are four drawing revisions plus the original, I want all 5 drawings to be opened.

The code I have right now to open an exact match is (with some other things going on):

Private Declare Function ShellExecute Lib "shell32.dll" _
    Alias "ShellExecuteA" (ByVal hWnd As Long, _
    ByVal lpOperation As String, ByVal lpFile As String, _
    ByVal lpParameters As String, ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long
    
    Dim Matches As New Collection
    Dim i As Integer, Addrs As Long
    Dim ws As Worksheet
_______________________________________________________________________________________
Private Sub OpenDwg_Click()

    Dim FileName As String, DwgNum As String
    Dim Result As Long
    
    For b = 0 To SearchResultsBox.ListCount - 1
            If SearchResultsBox.Selected(b) Then
            k = k + 1
        Else
        End If
    Next b
'   error handling------------------------------------------------------------------------
    If k = 0 Then
        MsgBox "Please select a DWG to open.", vbExclamation, "Selection"
        GoTo error1
    ElseIf k > 1 Then
        MsgBox "Only one DWG can be opened at a time.", vbExclamation, "Selection"
        GoTo error1
    Else
    End If
' end error handling-------------------------------------------------------------------

    For j = 0 To (i - 1)
            If SearchResultsBox.Selected(j) = True Then
                Addrs = Matches(j + 1)
                DwgNum = ws.Range("A" & Addrs)
            Else
            End If
        Next j
   
    
    FileName = "FS2CaddProjects" & DwgNum & ".pdf"
    Result = ShellExecute(0&, vbNullString, FileName, vbNullString, vbNullString, vbNormalFocus)
    If Result < 32 Then MsgBox "DWG #: " & DwgNum & " not found", vbInformation, "Not
Found"
    
error1:
End Sub
I'm new to VBA programming and have been referencing this book: http://="http://www.amazon.com/Excel...dp/0470044012", which is where I got the "ShellExecute" technique from, otherwise I would've never have known to do that.

I tried putting an asterisk in front of .pdf for the filename as some sort of wildcard, but that didn't work. I'm assuming I'm also going to need to add some sort of while loop if there is a drawing with multiple revisions.

Any help would be fantastic!

-Tom

I am currently trying to write a macro which will open an embedded pdf file in from excel, open it in acrobat reader, automatically save a copy of it into a predesignated folder, and return to excel to repeat the process. I can't seem to retain control over acrobat after I open the embedded file. Does anyone have any ideas? Code is posted below.

Sub ExcelCopySaveExistingPdf()
    Dim MyDir As String
    Dim strPath As String
    Dim PDApp As AcroApp
    Dim PDDoc As AcroPDDoc
    
    MyDir = ActiveWorkbook.Path
    strPath = MyDir & "Sample Touchpoint"
    
    On Error Resume Next
    
    ActiveSheet.Shapes("Object 23").Select
    Set PDApp = GetObject("Object 23", "Acrobat.AcroApp")
    Selection.Verb Verb:=xlOpen
    'Set PDApp = Selection
    If PDApp Is Nothing Then
        ' PowerPoint is not running, create new instance
        Set PDApp = CreateObject("Acrobat.AcroApp")
        
        Set PDDoc = PDApp.GetActiveDoc
    End If
    On Error GoTo 0
    'GoTo Line25
    On Error Resume Next
    If PDApp.Show = True Then
        ' There is at least one open Acro-doc
        ' Use existing Acro-doc
        Set PDDoc = AcroApp.ActiveDoc
        
        
    Else
        ' There are no Docs
        ' Create new one
        Set PDDoc = AcroPDDoc.Add
        
    End If
    On Error GoTo 0

    
    

Line25:
    With PDDoc
       
    '.Save (strPath & "TESTPRES.pdf")
    '.Save
    .Close
    End With
    PDApp.Exit

    ' Clean up
    Set PDDoc = Nothing
    Set PDApp = Nothing

End Sub


I have a client who developed a very simple MS Access application where he loads data for about 250 stores in a table and prints a report for each store. He wanted to print 250 different reports in pdf form, one for each store, and put the reports in the same folder with a different name for each store. I solved the problem by using VBA code to open the report in design mode, changing the Report caption to the desired report file name, and saving the report. Then I opened the report for print (he had set his PDF printer as the default printer) and applied the filter to limit the report to the desired store. I looped through all the stores till they were all "printed".

Now, I have another department with a similar request, however, his "database" is in Excel. Does anyone know how I can "print" the selected Print Area to the default printer with a specific file name.

Thanks, Eddie

Hi,

How can I control non-Microsoft applications using VBA?

For example, how can I open a pdf file, print it and then close Acrobat Reader?

Thanks in advance for your answers!

Orlando Mezquita

Hi

Give below is the code I used to send email and attache attachement to
the same email. Only success I had till now is in sending email and
attaching same sheet as attachement. I am not able to attache a pdf
and send it through email.

Can some one please advsie me how can I attache a pdf file and send it
as attachment to the email.

--------------------------------------…


	VB:
	
 
    String, emailATTACHMENT As String) 
     
     
     ' setting up various objects
    Dim Maildb As Object 
    Dim UserName As String 
    Dim MailDbName As String 
    Dim MailDoc As Object 
    Dim attachME As Object 
    Dim Session As Object 
    Dim EmbedObj1 As Object 
     
     
    Dim obAttachment As Object 
     
     
     ' creating a notes session
    Set Session = CreateObject("Notes.NotesSession") 
    UserName = Session.UserName 
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) 
    - InStr(1, UserName, " "))) & ".nsf" 
    Set Maildb = Session.GETDATABASE("", MailDbName) 
     
     
    If Maildb.IsOpen  True Then[/INDENT][INDENT=2]On Error Resume Next 
    Maildb.OPENMAIL[/INDENT][INDENT]End If[/INDENT][INDENT] 
     
    Set MailDoc = Maildb.CreateDocument 
    MailDoc.Form = "Memo" 
     
     
     ' loading the lotus notes e-mail with the inputed data
    With MailDoc[/INDENT][INDENT=2].SendTo = recipientTO 
        .copyto = recipientCC 
        .blindcopyto = recipientBCC 
        .Subject = emailSUBJECT 
        .Body = emailBODY 
        .attachment.Add "emailATTACHMENT"[/INDENT][INDENT]End With[/INDENT][INDENT] 
         
         ' saving message
        MailDoc.SaveMessageOnSend = True 
         
         
         'Set up the embedded object and attachment and attach it
         
         
        ActiveWorkbook.Save 
         
         
        If emailATTACHMENT  "" Then[/INDENT][INDENT=2]Set EmbedObj1 = attachME.EMBEDOBJECT(1454, "",
"I:UsersHarsimranProjectNotestest.xls", "Attachment") 
        MailDoc.CREATERICHTEXTITEM ("emailATTACHEMT") 
        On Error Resume Next[/INDENT][INDENT]End If[/INDENT][INDENT] 
         
         ' send e-mail !!!!
        MailDoc.PostedDate = Now() 'Gets the mail to appear in the
        sent items 
         
         
         ' if error in attachment or name of recipients
        On Error Goto errorHandler1 
         
         
        MailDoc.Send 0, recipient 
         
         
        Set Maildb = Nothing 
        Set MailDoc = Nothing 
        Set attachME = Nothing 
        Set Session = Nothing 
        Set EmbedObj1 = Nothing 
         
         
         'Unload Me
        Exit Function 
         ' setting up the error message
         
         
errorHandler1: 
         
         
        MsgBox "Incorrect name supplied or the attachment has not attached," & _ 
        "or your Lotus Notes has not opened correctly. Recommend you open 
        up Lotus Notes" & _ 
        "to ensure the application runs correctly and that a vaild 
        connection exists" 
         
         
        Set Maildb = Nothing 
        Set MailDoc = Nothing 
        Set attachME = Nothing 
        Set Session = Nothing 
        Set EmbedObj1 = Nothing[/INDENT]End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
--------------------------------------…

First, I have an Excel file with hyperlinks to PDF files and other web pages. When I save the file as a .htm and publish to our companies intranet the hyperlinks to the PDF files and web pages work properly. My issue is that I also have a hyperlink that using some VBA code which will email me when the hyperlink is clicked. It works well when the Excel file is open, but when I publish/save the file as a .htm the hyperlink does not work. I tried copying the VBA code into the .htm file, but that did not work.
Any ideas would be greatly appreciated!

I need to open about 2000 documents one at a time, and save them with a different name.

they are a mix of word documents, PDFs and .jpg pictures.

some VBA like:

Code:
 
Open.file strPath & nextfile
where strpath = C:Temp

and nextfile = picture01.jpg

What should the line be? or is it more complex than a single line ?

Hello everybody.

I am new to the forum and am looking for experts in the VBA field.

I am not a programmer nor understand it but have for the past week or so dealt with VBA more than I have did in 15 years fixing and repairing pcs.

What I want ot do is the following;

The code bellow prints (saves the spread sheet to PDF more like it) and opens it.


	VB:
	
 
    Dim oPrinterUtil As Object 
    Dim sFolder As String 
    Dim sCurrentPrinter As String 
    Dim xmldom As Object 
    Dim sProgId As String 
    Dim sPrintername As String 
    Dim sFullPrinterName As String 
     
    Rem -- Documentation of the used COM interface Is available at the link below. 
    Rem -- http://www.biopdf.com/guide/dotnet/chm/html/T_bioPDF_PdfWriter_PdfSettings.htm 
     
    Rem -- Create the objects To control the printer settings. 
    Rem -- Replace biopdf With bullzip If you have the bullzip printer installed instead 
    Rem -- of the biopdf printer. 
    Set oPrinterSettings = CreateObject("biopdf.PdfSettings") 
    Set oPrinterUtil = CreateObject("biopdf.PdfUtil") 
     
    Rem -- Get default printer name 
    sPrintername = oPrinterUtil.DefaultPrintername 
    oPrinterSettings.Printername = sPrintername 
     
    Rem -- Get the full name of the printer 
    sFullPrinterName = FindPrinter(sPrintername) 
    sFullPrinterName = GetFullNetworkPrinterName(sFullPrinterName) 
     
    Rem -- Prompt the user For a file name 
    sFolder = Environ("USERPROFILE") & "c:Invoices" 
    If sFileName = "" Then 
        sFileName = InputBox("Save PDF to desktop as:", "Sheet '" & _ 
        ActiveSheet.Name & "' to PDF...", ActiveSheet.Name) 
         
        Rem -- Abort the process If the user cancels the dialog 
        If sFileName = "" Then Exit Sub 
         
        sFileName = sFolder & sFileName 
    End If 
     
    Rem -- Make sure that the file name ends With .pdf 
    If LCase(Right(sFileName, 4))  ".pdf" Then 
        sFileName = sFileName & ".pdf" 
    End If 
     
    Rem -- Write the settings To the printer 
    Rem -- Settings are written To the runonce.ini 
    Rem -- This file Is deleted immediately after being used. 
    With oPrinterSettings 
        .SetValue "Output", sFileName 
        If confirmOverwrite Then 
            .SetValue "ConfirmOverwrite", "yes" 
        Else 
            .SetValue "ConfirmOverwrite", "no" 
        End If 
        .SetValue "ShowSettings", "never" 
        .SetValue "ShowPDF", "yes" 
        .WriteSettings True 
    End With 
     
    Rem -- Change To PDF printer 
    sCurrentPrinter = ActivePrinter 
    ActivePrinter = sFullPrinterName 
     
    Rem -- Print the active work sheet 
    ActiveSheet.PrintOut 
     
    Rem -- Restore the printer selection 
    ActivePrinter = sCurrentPrinter 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I want to do a few more things like;

1 - Automaticly name the file being printed to PDF. From what I have learn from this code, the name is being extracted from the current sheet name. I want to also add a cell value to the end of that name. For example the document to be saved in pdf is to be Invoice(InvoiceNumber).pdf. Pop-up if file has already been printed/saved with ok button to close dialog.

2 - Change the location where I want these saved for example c:/Invoices,

3 - and print 3 copies of the current sheet.

Any comments and help are very much appreciated.

Cheers,
Albert

Hi,

I have a lot of long Word documents that needs to be linked to pdf files, and I figured the easiest way would be to write macro for it.
I've some knowledge about using VBA for Excel, but I have never used it for Word before, so that's why I ask for guidance here.
To explain the problem as easy as possible, I attach a .doc file, which has the typical layout of my word files.

All Words files are divided into Sections (1, 2, 3, 4 and so on).
Most Sections has Sub-sections (in the example Section 1 has subsections 1.1, 1.2)
The first sub-section is always Content. After the Content headline, there is always a list of the same format as in the example file. This list lists all of the Section’s (Section 1 for example) sub-sections (1.1, 1.2, 1.3, 1.4).
Note that while 1.1 and 1.2 refer to physical locations in the Word file, 1.3 and 1.4 are external .pdf files.

I want to create a macro that automatically links the content list to the right position. In other words – when you click “1 Content” in the section 1 content list, you get sent back one row to the headline, when you click “2 Misc” you get sent to sub-section 1.2 in the same Word document. When you click on”3 External document title” the .pdf file with adress /pdf/1.3.pdf opens (all .pdf files are kept in the same folder with names according to the Content list numbers, for example 1.3.pdf, 1.4.pdf, 3.4.pdf).

As I see it there are some main problems to solve in the macro:
1 – Identify if a position in the content list refer to a local place in the Word document or to a .pdf file. To help with this problem the macro can use the sub-sections headline numbers. Since all local places always are listed before the external links by identifying that there are 2 sub-section headlines in the word document for Section 1, we know that the 2 first links will be local, the rest external. How to code this is currently beyond me and this is where I need help.
2 – Coding for the iterative linking procedure, help needed

Thank you in advance!

// Isen

Hi,
I've excel workbook containing vba, this workbook does some complex
calculations when some data is send to it (or paste) these calculations
are based on vba as well as functions.

My problem is that i've to run this on web server (as COM component)
send some data to it and extract data.

I'm thinking on these lines, open excel template file (visibility
should be hidden) send some data to it, perform calculations, extract
data from it and generate word and pdf reports based on extracted data.
Now i know using word and excel as COM is problem on server as it may
hang in memory and not easy to clean efficiently.

I need expert advise from you guys to :
1. Summarize the process in efficient manner (maybe alternate solution)
2. Risk associated and to bypass it
3. and overall feasibility of the solution

I'm using ASP.NET on server

regards,
Shattar