Opening files with VBA

[hojkoff]

Hi all, 1st post so I hope that I don't screw this up!

I'm trying to get access to open a related pdf file when you click on a lable in a forum, but I can't seem to get it to locate the file correctly.

So far I can get this to work,

Private Sub Text0_Click()
Dim acrobatApp As String

acrobatApp = "C:\Documents and Settings\***\Desktop\TEST.txt"
'test
Shell "winword.exe TEST.txt"
End Sub

Which works great to open Word and then the file TEST.txt however, as soon as I try to open a file with is not stored in the same location as the database it all goes pear shapped and it won't locate the file.

My ultiamte aim is to get the shell comand to open a pdf which is stored in the acrobatApp string.

I'm not even sure that the shell comand is the right comand to be using!

Any thoughts?

 

[DJkarl]

Hi all, 1st post so I hope that I don't screw this up!

I'm trying to get access to open a related pdf file when you click on a lable in a forum, but I can't seem to get it to locate the file correctly.

So far I can get this to work,

Private Sub Text0_Click()
Dim acrobatApp As String

acrobatApp = "C:\Documents and Settings\***\Desktop\TEST.txt"
'test
Shell "winword.exe TEST.txt"
End Sub

Which works great to open Word and then the file TEST.txt however, as soon as I try to open a file with is not stored in the same location as the database it all goes pear shapped and it won't locate the file.

My ultiamte aim is to get the shell comand to open a pdf which is stored in the acrobatApp string.

I'm not even sure that the shell comand is the right comand to be using!

Any thoughts?

2 things come to mind looking at your code.

1st: winword.exe works but you may come across a program that won't so make sure to include the full path i.e. C:\Program Files\***\winword.exe

2nd: Shell does not like spaces in the filepaths, if you have to use them make sure to enclose your string in double quotes

acrobatApp = chr$(34) &  "C:\Documents and Settings\***\Desktop\TEST.txt" & chr$(34)

The shell command will work fine to open the PDF in acrobat, but if you continue to have issues with it you look to see if there are any references you can add to your VBA project that would let you create an acrobat object to open files instead.

 

[hojkoff]

Thanks for your reply!

Just got a few things I don't understand about that,

1. what does the term chr$(34) mean?
2. I forgot to take that acrobatApp line out, this is only a test program that I'm getting to grips with just now,

Should probably be best to write it as

Private Sub Text0_Click()

Shell "winword.exe TEST.txt"

End Sub

My understanding of this is that you need to have:

Shell (program to open path, file to open with that program path)

Am I right there or is that totaly wrong?

This is what I've attempted with what you've written,


Private Sub Command0_Click()

acrobatApp = Chr$(34) & "C:\Documents and Settings\AndrewH.***\Desktop\*** Bridge Drawing Register\Drawings\1124\1244-30b" & Chr$(34)
Shell "acrobatApp, 1"
End Sub

Which expectedly.....doesn't work

...can you tell I don't really know what I'm doing...

 

[DJkarl]

34 is the ASCII character number for double quotes "

using chr$(34) will tell the computer to generate a " character

A quick test

Sub test()
Dim testSTR As String
testSTR = Chr$(34) & "C:\Documents and Settings\user\My Documents\Test.txt" & Chr$(34)
Shell "notepad.exe " & testSTR

End Sub

 

[hojkoff]

Cheers mate!

That is working for a .txt now but it's not locating the .pdf files correctly.

 

[DJkarl]

Well no...notepad.exe would not open the PDF files.

You would need to replace that with the path to whereever acrobat is installed on your system, or whatever program you are using to open PDF files with.

On my system it is

c:\Program Files\Adobe\Acrobat\Acrobat.exe

so to open a pdf it would be

testSTR = chr$(34) &"c:\Program Files\Adobe\Acrobat\Acrobat.exe" & chr$(34) & " " & chr$(34) & "C:\Documents and Settings\user\My Documents\my.pdf" & chr$(34)
shell testSTR

 

[hojkoff]

Got it working, not sure what I changed to get it to work but I don't care!

Thanks very much for your help!

 

[bulrush]

Wouldn't this work also?
docmd.execute "file.pdf"

This should run the program associated with the file.pdf and open file.pdf. My question is, does Docmd.Execute handle spaces in the path correctly, or at least better than Shell?

 

[DJkarl]

Wouldn't this work also?
docmd.execute "file.pdf"

This should run the program associated with the file.pdf and open file.pdf. My question is, does Docmd.Execute handle spaces in the path correctly, or at least better than Shell?

Docmd.Execute?

That is not a valid command, or at least not in access 2000-2003, I don't know about 2007.

 

[hojkoff]

A comand that would open the file with the relevent program is a much better way for me to do this. The program is being burnt to disc and then being sent round the company therefore making it hard for me to say where the path to adobe reader is on each individual computer.

I have come accross another problem with my program, it doesn't seem to execute the Insert.Movefirst line properly so I can't get it to open the path which is stored as "Dwg File" in the query "DeckPlateDocQ".

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

acrobatApp = "C:\Program Files\Adobe\Reader 9.0\Reader\AcroRd32 "

Set TheDatabase = DBEngine.Workspaces(0).Databases(0)
Set thisQuery = TheDatabase.QueryDefs("DeckPlateDocQ")

thisQuery.Parameters(0) = selectedBoxNumber


Set Inset = thisQuery.OpenRecordset()
If Inset.RecordCount > 0 Then
On Error GoTo InsetErrorHandler

Inset.MoveFirst
docName = Inset![Dwg File]
cmdLine = acrobatApp + docName
procID = Shell(cmdLine, vbNormalFocus)
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