Problem with opening a document thorugh Access with VBA 1

[jiminic]

Hello everyone.

I am having a problem with my Access VBA. Using a button on my database, I want to open a Word document that has been created and named using the auto number system in Access (eg, auto number 3448, Word document name 3448.doc). I am using the following code:

Private Sub RetreiveScreenDumps_Click()

On Error GoTo Err_RetreiveScreenDumps_Click

Dim stAppName As String

stAppName = "C:\Program Files\Microsoft Office\Office10\WINWORD.EXE N:\SMDiv\Insurance\MemberRelations\FileManagement\ SystemReferrals\" & Forms![SystemReferralActionFrm]![SystemReferralTbl.JobNumber] & ".doc"

Call Shell(stAppName, 1)

Exit_RetreiveScreenDumps_Click:
Exit Sub

Err_RetreiveScreenDumps_Click:
MsgBox Err.Description
Resume Exit_RetreiveScreenDumps_Click

End Sub

The problem is, when Word opens I get an error message saying "The document name or path is invalid". I know there is a simple fix to this, but can not figure out what to do.

Any help would be greatly appreciated.

 

[KKit]

I use Word/Excel files extensively through Access. Here is the code that I use that works everytime (yup..I found it on TekTips!). The bonus of it is that it already looks for an existing instance of Word/Excel running, and if it finds that it uses that. If not, then it opens up another instance. Also, it uses a variable (booClose) to determine if it was already open or not. The theory is that if you open something, then you should close it also.

If you want to write your own basic routine, try this one.

dim booCloseAPP as boolean 'if we open it, then we should close it
dim APP as word.application
Dim Doc As Word.Document

 booCloseAPP = False
    Set APP = GetObject(, "word.application")
    'declare the error default statement
    On Error GoTo PROC_ERR
    
    If TypeName(APP) = "Nothing" Then
    'Excel was not open -- create a new instance
        Set APP = CreateObject("word.Application")
        If APP Is Nothing Then
            'does not have word on their system
            MsgBox "This application does not exist on this system!", vbCritical, "ERROR"
            GoTo PROC_EXIT  'the exit routine
        End If
      
        booCloseAPP = True
            
    End If
    
    APP.Visible = False 'use false to hide your ap in the background, and true todisplay it.

 Set Doc = APP.Documents.Open(Filename:=MYFILENAME, ConfirmConversions:=False, _
                ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
                PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
                WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:="")

And then at the end, I have this closing routine

proc_exit:
    On Error Resume Next
    If booCloseAPP = True Then
       APP.Quit
    End If
    
    APP.Visible = True
    
    'release the objects
    Set APP = Nothing
    Set Doc = Nothing
         
    
    'show the users the screen is not busy
    DoCmd.Hourglass (False)
    Exit Sub

That being said, I also have a very nice piece of code that I had found somewhere. I put a command button on the form and it opens up a file in its native program. Depending on the way you are doing your application, either one will work to open up your files.

'Code Courtesy of
'Dev Ashish
'This will open any file that you have clicked on in it's native program

Function fHandleFile(stFile As String, lShowHow As Long)
Dim lRet As Long, varTaskID As Variant
Dim stRet As String
    'First try ShellExecute
    lRet = apiShellExecute(hWndAccessApp, vbNullString, _
            stFile, vbNullString, vbNullString, lShowHow)
            
    If lRet > ERROR_SUCCESS Then
        stRet = vbNullString
        lRet = -1
    Else
        Select Case lRet
            Case ERROR_NO_ASSOC:
                'Try the OpenWith dialog
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
                        & stFile, WIN_NORMAL)
                lRet = (varTaskID <> 0)
            Case ERROR_OUT_OF_MEM:
                stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
            Case ERROR_FILE_NOT_FOUND:
                stRet = "Error: File not found.  Couldn't Execute!"
            Case ERROR_PATH_NOT_FOUND:
                stRet = "Error: Path not found. Couldn't Execute!"
            Case ERROR_BAD_FORMAT:
                stRet = "Error:  Bad File Format. Couldn't Execute!"
            Case Else:
        End Select
    End If
    fHandleFile = lRet & _
                IIf(stRet = "", vbNullString, ", " & stRet)
End Function

 

[genomon]

Public Sub OpenDocument(strDocPath As String)

Dim G As Long
G = Shell("RUNDLL32.EXE URL.DLL,FileProtocolHandler " & strDocPath, vbNormalFocus)
    
End Sub

Opens just about any format your PC supports, where strDocPath is the path to the file...

Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes.

 

[jiminic]

Hello everyone, thank you for your help.

the problem is not getting word to open, I can do that. the problem is getting word to open the document that was previously saved. I have a button on a word document that when clicked, it will save the document into a folder using the auto number from Access as the name (eg: 1234.doc). When I then click on the retreive button in Access (using the code I pasted above) the error message saying "The document name or path is invalid" appears. I know that the name is valid, and I can open the file manually using the path, so there must be a small problem with the code, I just can't figure out what it is.

Thank you

 

[randy700]

What do you see in the immediate window if you insert a Debug.Print command?

stAppName = "C:\Program Files\Microsoft Office\Office10\WINWORD.EXE N:\SMDiv\Insurance\MemberRelations\FileManagement\ SystemReferrals\" & Forms![SystemReferralActionFrm]![SystemReferralTbl.JobNumber] & ".doc"

Debug.Print stAppName

Call Shell(stAppName, 1)


Randy