Shell command without the application path

[hojkoff]

I'm trying to open .pdf documents with VBA currently I am using the shell command as such:

test = "APPLICATION PATH FOR ACROBAT 9, PDF FILE PATH)
procID = shell(test, vbNormalFocus)

which works fine, but if the path for acrobat changes or the version changes, which it will inevitably, the code won't work anymore without the application path being changed.

I'm looking for a way to scrap the application path and just tell access to open the pdf but use the computers default application to open it and thus eliminating the need to have the path inscribed in the coding.

The alternitave and slightly longer way round would be to have a line of code to work out what the application path is (a bit like the Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")) line to generate the path that the Access database is) and use that in the test string. I’m not sure how possible this is.

Any thoughts?

 

[DCrake]

Place the code below in the main declarations of your form

'This API is used to shell to the selected file in its native software.
Private Declare Function ShellExecute Lib "shell32.dll" _
   Alias "ShellExecuteA" _
   (ByVal hWnd As Long, ByVal lpszOp As String, _
    ByVal lpszFile As String, ByVal lpszParams As String, _
    ByVal LpszDir As String, ByVal FsShowCmd As Long) _
    As Long
Private Declare Function GetDesktopWindow Lib "User32" () As Long

Const SW_SHOWNORMAL = 1

Dim oPath As String
Dim oFile As String
Dim strFile As String

oPath = CurrentProject.Path
strFile = oPath & "\" & "Test.pdf"

Then on the double click of the control that holds the path and name of the pdf file place the following code

nDT = GetDesktopWindow()
nApp = ShellExecute(nDT, "Open", strFile, "", "C:\", SW_SHOWNORMAL)

When I employ the above code my file names are usually already populated in a list box and I simply double click on a item in the list box. This works for any type of file, you do not need to specify the type of file it will automatically open the file in its native software.


Here is a link to a sample demo that uses this functionality

Link

David

 

[hojkoff]

I'm so confused by all that code I'm a bit of a beginner when it comes to all this, my current VBA looks like this,

[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Function myFolder()[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    myFolder = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Function[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Private Sub OpenBoxDig()[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]acrobatApp = "C:\Program Files\Adobe\Reader 9.0\Reader\AcroRd32 " + myFolder[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]extend = "Drawings\1244-2\1244-2 sheet "[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]file = acrobatApp + extend[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  Set TheDatabase = DBEngine.Workspaces(0).Databases(0)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  Set thisQuery = TheDatabase.QueryDefs("BoxQ")[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]      docName = Inset![Box Dwg]    'From the box ref table[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]      cmdLine = file + docName[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]      procID = Shell(cmdLine, vbNormalFocus)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]

I have no idea how to tailor the code that you've put up to my database.

 

[DCrake]

First thing put the first block of code in the declarations section of your form. Then forget about it.

Now we need 2 items of information

1 Where on the computer is the file that we need to open?

If it is in the same folder as the Access front end then we can use the

CurrentProject.Path to determine where the application resides

2 Next we need to know what the name of the pdf file is that we want to open

This may be stored in a table or appears in the known folder. Lets say in this case its called "DWG123.pdf"

oPath = CurrentProject.Path
stFile = oPath & "\Drawings\" &  "DWG123.pdf"

So stFile looks like

"C:\Program Files\Database\Drawings\DWG123.pdf"

finally we employ the third bit of code

nDT = GetDesktopWindow()
nApp = ShellExecute(nDT, "Open", strFile, "", "C:\", SW_SHOWNORMAL)

This is similar to the Shell command you are using but this ShellExecute does not need to know where Acrobat is installed as long as it exists on the machine. If the file was an Excel file then this would launch Excel. It looks at the file extension to decide which application to launch.

Hope that clears it up a bit.

David

 

[hojkoff]

Deleted my last post, got round that problem,

I've typed all that into the code so it looks like this,

Private Sub OpenDeckPlateDocument(DeckPlateNumber As Integer)
  Dim Inset As Variant
  Dim TheDatabase As Database
  Dim thisQuery As QueryDef
  Dim docName As String
  Dim acrobatApp As String
  Dim cmdLine As String
  Dim extend As String
  Dim file As String
  
oPath = CurrentProject.Path
stFile = oPath & "\Drawings\"
 
  Set TheDatabase = DBEngine.Workspaces(0).Databases(0)
  Set thisQuery = TheDatabase.QueryDefs("DeckPlateDocQ")
  
  thisQuery.Parameters(0) = [Forms]![Form1]![BoxDrop]
  thisQuery.Parameters(1) = DeckPlateNumber
  Set Inset = thisQuery.OpenRecordset()
  If Inset.RecordCount > 0 Then
    On Error GoTo InsetErrorHandler
      Inset.MoveFirst
      docName = Inset![Dwg File]
      cmdLine = file + docName
      nDT = GetDesktopWindow()
      nApp = ShellExecute(nDT, "Open", strFile, "", "C:\", SW_SHOWNORMAL)
      GoTo exitSub
InsetErrorHandler:
      MsgBox "No Document for this Box Number & associated Plate Number"
  Else
    MsgBox "No Document for this Box Number & associated Plate Number"
  End If
exitSub:
  Inset.Close
End Sub

But when I click on the box to get the pdf to open, nothing at all hapens.

 

[DCrake]

This code needs to be ABOVE any subs or functions, you will know this as the left hand combo box will say Declarations.

I think you have placed it with a sub or a function. If it still does not work can you send a snippet of your mdb for testing.

David

 

[hojkoff]

Yeah I realised that I had it in the wrong place just after I'd posted. Sorry about that.

I've had a play around with things, can you tell me what this line here does?

nApp = ShellExecute(nDT, "Open", strFile, "", "C:\", SW_SHOWNORMAL)

what does the part between the comas where C:\ is do?

Rather than having,

stFile = oPath & "\Drawings\" & "DWG123.pdf"

is it ok to have,

stFile = oPath & "\Drawings\1124\1244-" & docName

where docName is a variable which contains the rest of the path found in the query ie. 54b.pdf?

My code looks like this just now

[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Private Sub OpenDeckPlateDocument(DeckPlateNumber As Integer)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]oPath = CurrentProject.Path[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]stFile = oPath & "\Drawings\1244-"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  Set TheDatabase = DBEngine.Workspaces(0).Databases(0)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  Set thisQuery = TheDatabase.QueryDefs("DeckPlateDocQ")[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  thisQuery.Parameters(0) = [Forms]![Form1]![BoxDrop][/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  thisQuery.Parameters(1) = DeckPlateNumber[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  Set Inset = thisQuery.OpenRecordset()[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  If Inset.RecordCount > 0 Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    On Error GoTo InsetErrorHandler[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]      Inset.MoveFirst[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]      docName = Inset![Dwg File][/FONT][/COLOR]
[COLOR=black][FONT=Verdana]      cmdLine = stFile + docName[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]      nDT = GetDesktopWindow()[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]      nApp = ShellExecute(nDT, "Open", strFile, "", cmdLine, SW_SHOWNORMAL)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]      GoTo exitSub[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]InsetErrorHandler:[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]      MsgBox "No Document for this Box Number & associated Plate Number"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  Else[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    MsgBox "No Document for this Box Number & associated Plate Number"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]exitSub:[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  Inset.Close[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]

All this does is open C:\

 

[DCrake]

Ok lets add a bit of validataion. After you have set stFile

stFile = oPath & "\Drawings\1244-"

Add the following line

If Dir(stFile) = "" Then
   MsgBox "File does not exist"
   Exit Sub
End If

Also how are you calling OpenDeckPlateDocument

If you place a breakpoint on the function you can step through it using F8.

David

 

[hojkoff]

I'm calling the OpenDeckPlate Document using,

Private Sub D1_Click()
    OpenDeckPlateDocument (1)
End Sub

I'm still not sure about what to do with,

      nApp = ShellExecute(nDT, "Open", strFile, "", cmdLine, SW_SHOWNORMAL)

 

[hojkoff]

Right I've managed to push this forward but I've hit other problems, I can get the file to open if I just type the full path into here:

stFile = oPath & "\Drawings\1124\1244-63b.pdf"

but my problem now is that the path doesn't always end with 63b.pdf. The end part is derived from a query. So what I need to do now is to have something like this,

stFile = oPath & "\Drawings\1124\1244-"
 
  Set TheDatabase = DBEngine.Workspaces(0).Databases(0)
  Set thisQuery = TheDatabase.QueryDefs("DeckPlateDocQ")
 
  thisQuery.Parameters(0) = [Forms]![Form1]![BoxDrop]
  thisQuery.Parameters(1) = DeckPlateNumber
  Set Inset = thisQuery.OpenRecordset()
      Inset.MoveFirst
      docName = Inset![Dwg File]
      cmdLine = stFile + docName
      nDT = GetDesktopWindow()
      nApp = ShellExecute(nDT, "Open", cmdLine, "", "C:\", SW_SHOWNORMAL)

Here the docName, taken from the Dwg File field on the DeckPlateDocQ query is the end of the path that needs to be plugged onto the end of the oPath to make the cmdLine which is then launched in the Shell Execute. I'm just not sure how to do the plugging on bit.

It's so close to working now!

 

[DCrake]

  Set TheDatabase = DBEngine.Workspaces(0).Databases(0)
  Set thisQuery = TheDatabase.QueryDefs("DeckPlateDocQ")
 
  thisQuery.Parameters(0) = [Forms]![Form1]![BoxDrop]
  thisQuery.Parameters(1) = DeckPlateNumber
  Set Inset = thisQuery.OpenRecordset()
      Inset.MoveFirst

This bit of code seems to be a bit overdone, in my experience anyway. My method would be

Dim Rs As DAO.RecordSet
Set Rs = CurrentDb.OpenRecordset("DeckPlateDocQ")

If Not Rs.EOF And Not Rs.BOF Then
  docName = Rs("Dwg File")
  Rs.Close
Else
   Exit Sub
End If

Set Rs = Nothing

FileToOpen =  stFile & docName

      nDT = GetDesktopWindow()
      nApp = ShellExecute(nDT, "Open", FileToOpen, "", "C:\", SW_SHOWNORMAL)

This assumes the the query DeckPlateDocQ only contains one record and this contains the name of the document in the field [Dwg File]

David

 

[hojkoff]

This assumes the the query DeckPlateDocQ only contains one record and this contains the name of the document in the field [Dwg File]

David

The query has 8 records in it for the deckplate query. Other sections of the box have 4 some 2 and so on.

I'm really just looking for a way now to slot the end of the path contained in the query field onto the end of the longer path under oPath

When I do it like this,

      cmdLine = stFile & docName
      nDT = GetDesktopWindow()
      nApp = ShellExecute(nDT, "Open", cmdLine, "", "C:\", SW_SHOWNORMAL)

it doesn't seem to find the file

 

[DCrake]

To reduce the filter change the line to

Set Rs = CurrentDb.OpenRecordset("Select * From DeckPlateDocQ Where [FieldName] = " & Me.DropBox & "'")

This leave you with just the row that contains the file name. Or you could even use a DLookup() to get the file name, because thats all we need to generate the file open procedure.

David