Hi everyone,
I'm new to these forums and to vba in general. I have run into an issue that I just can't seem to figure out. The first time I run this macro, it runs fine without issue. The next time I run it, I get an error of:
Run-Time error '462':
The Remote server machine does not exist or is unavailable
If I close word and re-open it, I can get the macro to kick in, but again only the first time I attempt to run the macro does it successfully work 100%. The rest of time it will intermittently spit that same error out to me.
If I hit debug on the code, the line that is red above is the line that is highlighted as the offending code by the debugger. There is a report called exactly "Master Client List" in MyDB.accdb, if I take it through the debugger I see sPathUser definitely has a valid value. The reason for the line
is because I saw a posting where another person suggested that the issue may be because G is a mapped drive and putting the extra slash in there would help. Unfortunately it made no appreciable difference in my case.
I am really stuck here. My assumption is that since it works the first time, something isn't getting closed properly, but that is simply a guess. I'm throwing this out here in the hopes that someone much smarter than me can help figure this out. Any help here would be greatly appreciated.
Thank you,
Chris
I'm new to these forums and to vba in general. I have run into an issue that I just can't seem to figure out. The first time I run this macro, it runs fine without issue. The next time I run it, I get an error of:
Run-Time error '462':
The Remote server machine does not exist or is unavailable
If I close word and re-open it, I can get the macro to kick in, but again only the first time I attempt to run the macro does it successfully work 100%. The rest of time it will intermittently spit that same error out to me.
Code:
Sub GenMCL()
'
' GenMCL Macro
' Generates Master Client List Report and Exports that to a PDF to a location of the users choice
'
'On Error GoTo ProcError
Dim objAccess As Object
'Dim objDB As Object
Dim sPathUser As String
Dim currentDate As String
Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String
GetFilePath:
currentDate = Format(Date, "mmddyyyy")
sPathUser = BrowseFolder(Caption:="Select A Folder To Output The Report To")
If Len(sPathUser) = 0 Then
QuestionToMessageBox = "Cancel button detected. Did you mean to quit this program?"
YesOrNoAnswerToMessageBox = MsgBox(QuestionToMessageBox, vbYesNo, "Quit Program")
If YesOrNoAnswerToMessageBox = vbNo Then
GoTo GetFilePath
Else
MsgBox "Ok, Goodbye."
Exit Sub
End If
End If
sPathUser = sPathUser & "\[" & currentDate & "] - Master Client List" & ".pdf"
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "G:[COLOR=lime]\\[/COLOR]Public\Access Data\MyDB.accdb"
[COLOR=red]DoCmd.OutputTo acOutputReport, "Master Client List", acFormatPDF, sPathUser, True[/COLOR]
objAccess.Quit
Set objAccess = Nothing
'MsgBox "Report Generated and Saved.", vbInformation
Exit Sub
ProcError:
MsgBox Err.Description, vbExclamation, "Generate Master Client List Report"
MsgBox Err.Number
Resume ProcExit
ProcExit:
Exit Sub
End Sub
If I hit debug on the code, the line that is red above is the line that is highlighted as the offending code by the debugger. There is a report called exactly "Master Client List" in MyDB.accdb, if I take it through the debugger I see sPathUser definitely has a valid value. The reason for the line
Code:
objAccess.OpenCurrentDatabase "G:\\Public\Access Data\MyDB.accdb"
is because I saw a posting where another person suggested that the issue may be because G is a mapped drive and putting the extra slash in there would help. Unfortunately it made no appreciable difference in my case.
I am really stuck here. My assumption is that since it works the first time, something isn't getting closed properly, but that is simply a guess. I'm throwing this out here in the hopes that someone much smarter than me can help figure this out. Any help here would be greatly appreciated.
Thank you,
Chris