# Solved: VBA Macro that opens the Command Prompt and executes a command



## Musoguy (Apr 28, 2009)

I am writing a VBA macro in Excel and have hit a wall. I am trying to get excel to open a command prompt and make it execute a command. The problem I am having is if I try to do anything but open another program, it throws an error back at me.

I can open the command prompt using:


```
Call Shell("cmd.exe" & dosCmd, vbNormalFocus)
```
I can also open Acrobat which is the program I need using:


```
Shell "C:\program files\adobe\acrobat 9.0\acrobat\acrobat.exe", vbNormalFocus
```
However if I simply wanted to open a PDF file, I have the code to that without opening a command prompt.

I need to open the PDF document on a specific page, and to my knowledge the only way to do this (apart from manually) is to use the command prompt. However I have no idea how to get VBA to put the command in the command prompt, hit enter and then close the prompt.

There are 2 commands that need to be executed in DOS (I'm not sure if there is a way to combine the commands)

They are:

```
cd c:\program files\adobe\acrobat 9.0\acrobat
```
and

```
start acrobat.exe /A "page=10=Open Actions" "M:\Full Scores\allegro.pdf
```
These open the pdf document called _allegro_ on page 10.

I am completely stuck. Out of ideas. I've searched the length and breadth of the internet and can find nothing to point me in the right direction. So I was hoping one of you fine people could. Thank you so much, James


----------



## Rollin_Again (Sep 4, 2003)

Have you tried putting the commands into a batch file and then calling the batch using VBA? You could also programatically create the batch file prior to calling it using VBA. If you prefer not to use a batch file then check out the link below which explains Command Line from VBA

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

Regards,
Rollin


----------



## Musoguy (Apr 28, 2009)

Thanks Rollin, that link is just what I'm looking for. Thank you. Can't create a batch file, as the macro is a little more complicated than I explained, and the command it sends to the command prompt depends on user feedback, so will not be the same every time. Thanks again for the link 

James


----------



## Fluffmatic (Mar 21, 2009)

This sounds fairly easy, try:

start "c:\program files\adobe\acrobat 9.0\acrobat\acrobat.exe" /A "page=10=Open Actions" "M:\Full Scores\allegro.pdf


----------



## Musoguy (Apr 28, 2009)

Thanks for your reply Fluffmatic. I'm not quite sure where the code you sent is meant to go? I tried butting it a VBA macro and it didn't work. I also thought maybe it was a bat file, but that didn't work either.


----------



## Fluffmatic (Mar 21, 2009)

Hi,

Its the command that you need to shell, like below:

```
Command="start """c:\program files\adobe\acrobat 9.0\acrobat\acrobat.exe""" /A """page=10=Open Actions""" """M:\Full Scores\allegro.pdf"""

Call Shell("cmd.exe " & dosCmd, vbNormalFocus)
```
You may need to modify the quotes to make it work, I've not got a Windows PC handy to test it on.


----------



## Musoguy (Apr 28, 2009)

Thanks for the help! However I now have a solution. I'll add it below in hope it helps someone else:

My Worksheet is set up as follows:

Column A is the music book name
Column B is the Page Number
Column C is The Song name

So for example:

Column A, Row 1 = Music Book 1
Column B, Row 1 = 1
Column C, Row 1 = Song 1

Column A, Row 2 = Music Book 1
Column B, Row 2 = 6
Column C, Row 2 = Song 2

Column A, Row 3 = Music Book 1
Column B, Row 3 = 15
Column C, Row 3 = Song 3

etc.

The macro asks the user for a song name. It then finds the song in Column C, and uses the information in Column's A and B of that row to open a PDF called Music Book 1 (in this example) on the correct page.


```
Sub FindSong()

Dim Answer As String
Dim answer2 As String
Dim RetVal As Long
Dim strPDFFile As String
Dim strPrgm As String
Dim strParam1 As String
Dim strParam2 As String
Dim strName As String
Dim strEXT As String
Dim strPage As String

On Error GoTo NoFind

'Asks the user for the song name they wish to open
Answer = InputBox("Type the name of the song you are looking for")
    If Trim(Answer) = "" Then Exit Sub
    If Trim(Answer) <> "" Then
    
'Finds the song name in Column C
Worksheets("Page References").Activate
Columns("C:C").Select                   'change column as necessary
Selection.Find(What:=Answer, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Activate
    ActiveCell.Select
    
End If

    strName = (ActiveCell.Offset(0, -2).Value)    'records the value in column A
    strEXT = ".pdf"
    strPage = (ActiveCell.Offset(0, -1).Value)  'records the value in column B
    strParam1 = " /A page="
    strParam2 = "=OpenActions"
    strPrgm = "C:\Program Files\Adobe\Acrobat 9.0\Acrobat\Acrobat.exe" 'change as necessary to the path of Acrobat
    strPDFFile = " M:\PDFs\" (Change as necessary to the file where your PDF files are located


'Calls a Command Prompt to open Adobe Acrobat and then open the book specified at the specified page number.

RetVal = Shell(strPrgm & strParam1 & strPage & strParam2 & strPDFFile & strName & strEXT, 1)


    
Exit Sub

'Error trapping
NoFind:
    Range("$A$1").Select
    answer2 = MsgBox("This song has not yet been catalogued has been spelt it incorrectly. Do you want to try again?", vbYesNo)
    If answer2 = vbNo Then Exit Sub
    If answer2 = vbYes Then Application.Run "FindSong"
    
End Sub
```
James


----------

