Access Form Convert to PDF VBA Code not working

[Mackbear]

Hello everyone, hope you are all doing fine. I hope I can get some help on this code I found by googling. This always says I have Invalid folder path. I don't know which I causing the error. Here's the code:

Dim fileName As String, fldrPath As String, filePath As String, strusername As String

Dim answer As Integer:banghead:

strusername = Environ("UserName")
fileName = "Template-Please Rename" 'filename for PDF file*
fldrPath = "C:\documents and settings" & strusername & "\Desktop" 'folder path where pdf file will be saved *


filePath = fldrPath & "" & fileName & ".pdf"

'check if file already exists
If FileExist(filePath) Then
answer = MsgBox(prompt:="PDF file already exists: " & vbNewLine & filePath & vbNewLine & vbNewLine & _
"Would you like to replace existing file?", buttons:=vbYesNo, Title:="Existing PDF File")
If answer = vbNo Then Exit Sub
End If

On Error GoTo invalidFolderPath
DoCmd.OutputTo objecttype:=acOutputReport, objectName:=Me.Name, outputformat:=acFormatPDF, outputFile:=filePath

MsgBox prompt:="PDF File exported to: " & vbNewLine & filePath, buttons:=vbInformation, Title:="Report Exported as PDF"
Exit Sub

invalidFolderPath:
MsgBox prompt:="Error: Invalid folder path. Please update code.", buttons:=vbCritical

 

[theDBguy]

Hi. Do a Debug.Print filePath and check what it says when you get an error. Folder paths cannot have invalid characters.

 

[nhorton79]

After your line:
filepath = fldrPath & “” & fileName etc...

Try putting in:
Debug.Print filepath

Then when you run your code the value of filepath will be printed in the Immediate Window for you to check


Sent from my iPhone using Tapatalk

 

[Mackbear]

After your line:
filepath = fldrPath & “” & fileName etc...

Try putting in:
Debug.Print filepath

Then when you run your code the value of filepath will be printed in the Immediate Window for you to check


Sent from my iPhone using Tapatalk

Do I run it from the button that triggers the code? I didn't see anything printed

 

[theDBguy]

Do I run it from the button that triggers the code? I didn't see anything printed

Yes, put it within the same code as mentioned. If you don't see anything, then it means there is no value, which could mean you're getting an invalid filepath.

 

[Mackbear]

Yes, put it within the same code as mentioned. If you don't see anything, then it means there is no value, which could mean you're getting an invalid filepath.

Yes I didn't see anything, how do I get the correct path? When I tried the path in the explorer bar it works. I'm not sure what to look out for:banghead:

 

[Mackbear]

Hi. Do a Debug.Print filePath and check what it says when you get an error. Folder paths cannot have invalid characters.

When I try the path in the windows explorer bar, it works. What could be the problem on the path I have?

 

[Mackbear]

I would like to have it saved it the desktop

 

[theDBguy]

When I try the path in the windows explorer bar, it works. What could be the problem on the path I have?

Hi. Please post the path. Also, please repost the complete code showing where you put the Debug.Print line.

 

[nhorton79]

You should see something. Do you have the immediate window showing at the bottom?


Sent from my iPhone using Tapatalk

 

[nhorton79]

Before I learnt about debug.print I always used a msgbox so you could try that.

Msgbox filePath


Sent from my iPhone using Tapatalk

 

[Mackbear]

Before I learnt about debug.print I always used a msgbox so you could try that.

Msgbox filePath


Sent from my iPhone using Tapatalk

So this is what I got. I attached the image. It gives the path plus the file name. So why does it says I have invalid folder path?

 

[Mackbear]

Could it be a problem in other areas of the code?

 

[nhorton79]

I believe where you have a space in filePath it is creating the issue.
Try to add quotation marks at front and end?


Sent from my iPhone using Tapatalk

 

[Mackbear]

I believe where you have a space in filePath it is creating the issue.
Try to add quotation marks at front and end?


Sent from my iPhone using Tapatalk

This is what I have in the code:

strusername = Environ("UserName")
fileName = "Template-Please Rename" 'filename for PDF file*
fldrPath = "C:\Users" & strusername & "\Desktop" 'folder path where pdf file will be saved *


filePath = fldrPath & "" & fileName & ".pdf"
MsgBox filePath

What could be the problem here?

 

[nhorton79]

Ok. So I have just tried this on my computer and it works, I had to make some changes to fldrPath:

fldrPath = "C:\Users\" & strusername & "\Desktop\" 'folder path where pdf file will be saved *

so your might need to be:

fldrPath = "C:\documents and settings\" & strusername & "\Desktop\" 'folder path where pdf file will be saved *

I also changed your line to:

DoCmd.OutputTo acOutputReport, "rptTest", acFormatPDF, filePath

Where I explicitly defined the name of the report as a string. You might want to try this for testing.

Also, make sure you do have a FileExist function I wrote a very quick and dirty just for testing:

Public Function FileExist(FileName As String) As Boolean
     FileExist = (Dir(FileName) > "")
End Function

Try these and let me know how you go.

 

[nhorton79]

Any luck?


Sent from my iPhone using Tapatalk

 

[Mackbear]

I apologize for the late reply, so I have seen the problem, I was trying to save it to my desktop, and the user folder in my pc does not match the environ username that is why it does not come up with the correct path... the user folder has one letter in upper case and the other does not. So I have to resort to other ways. I just went in for a code that generates an email with attachment just so the users can get a copy of that file and just manually save their copy, instead of having it automatically saved to their desktop which what I originally wanted to do.... thanksss everyone for helping me out!

 

[Gasman]

Well if you look at the user desktop path, you would see a \ after Users ?
Folders need to be separated with a \ in Windows

so perhaps

fldrPath = "C:\Users\" & strusername & "\Desktop" 'folder path where pdf file will be saved *

If you MSGBOX fldrPath or Debug.Print fldrPath or even hover over the variable in the debugger, you will see what you have.

https://www.myonlinetraininghub.com/debugging-vba-code

 

[nhorton79]

Hi Mackbear,

You should still be able to save it to a desktop folder.

I wouldn’t have thought that a different case letter would stop it from being able to save to that folder.
So definitely try Gasman’s recommendation to debug.print or msgbox the fldrPath variable might help you identify the issue with the path.

You could also try changing the particular letter to uppercase.
If it’s the first letter try:

strusername = StrConv(strusername,3)

Before building your path variable.

Just a thought.


Sent from my iPhone using Tapatalk