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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Musoguy

Thread Starter
Joined
Apr 28, 2009
Messages
6
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:

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

Code:
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:
Code:
cd c:\program files\adobe\acrobat 9.0\acrobat
and
Code:
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
 

Musoguy

Thread Starter
Joined
Apr 28, 2009
Messages
6
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
 
Joined
Mar 21, 2009
Messages
116
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

Thread Starter
Joined
Apr 28, 2009
Messages
6
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.
 
Joined
Mar 21, 2009
Messages
116
Hi,

Its the command that you need to shell, like below:
Code:
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

Thread Starter
Joined
Apr 28, 2009
Messages
6
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.

Code:
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top