VBA issue

[bdw1980]

I have this macro I made that converts the excel page to .pdf and then saves it to a folder I assigned. the macro works and the file is saved in the right folder but after the macro runs it displays the message "could not create PDF file" which is the message that is supposed to be displayed if there were any issues. I really don't think I need the errhandler parts but I'm not totally sure on that part. However if I delete the errhandler stuff no message would be displayed at all. I also wanted to add a piece of code that when the user clicks the ok button after the message is displayed the excel workbook closes without saving but that part is not as important. See VBA below. Any help would be appreciated.

Option Explicit

Sub PDFActiveSheet()

Dim wsa As Workbook
Dim Key As Variant
Dim MyFile As Variant
Set Key = ActiveWorkbook.Names("Key_Primary").RefersToRange
On Error GoTo errHandler

ActiveSheet.ExportAsFixedFormat xlTypePDF, "C:\Users\brian_williams\Desktop\KPI Project\Submitted Audits" & Key & ".pdf", , , False

If MyFile <> "False" Then
wsa.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=MyFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
MsgBox " Audit has been Submitted and you can close this form. Thank You!"" _
& vbCrLf _
& MyFile
End If

exitHandler:
Exit Sub
errHandler:
MsgBox "Could not create PDF file"
Resume exitHandler

End Sub

 

[jimrward]

Tell me what you think is in variable myfile looks empty or null to me

 

[bdw1980]

Tell me what you think is in variable myfile looks empty or null to me

So what I take from this is you are telling me that something needs to be between the <> if I am not mistaken.

If the file is going to be named based on Key = Key_Primary Variant then it will be different everytime. So what should I put between the <> ? Should I just put <"">?

 

[jimrward]

Code appears to produce a pdf to your desktop using the key variable which is probably working following this you test the value of myfile which has not been assigned a value so will pass your logic test of not being equal to "False"
As myfile has no value the second creation of a pdf will fail as it has no file name

 

[bdw1980]

It actually produces a file to C:\Users\brian_williams\Desktop\KPI Project\Submitted Audits
So if I change the logic test from "False" to "True" should that fix it? Or can I just get rid of the IF MyFile statement all together?

Should I Make it look like this below instead? I just removed the MyFile Variant and errHandler along with the code that was associated with it. I left the & vbCrLf _ part of the code at the bottom because Im not sure what that does but I removed the & MyFile

I am just getting use to VBA coding so I just tinker with the coding till things happen but this one is giving me small issues

Option Explicit

Sub PDFActiveSheet()

Dim wsa As Workbook
Dim Key As Variant
Set Key = ActiveWorkbook.Names("Key_Primary").RefersToRange

ActiveSheet.ExportAsFixedFormat xlTypePDF, "C:\Users\brian_williams\Desktop\KPI Project\Submitted Audits" & Key & ".pdf", , , False

wsa.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=MyFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
MsgBox "PDF file has been created: " _
& vbCrLf _

End Sub

 

[jimrward]

Get rid of the white or second bit from wsa.exportasfixedformat to just before end sub

 

[bdw1980]

I got it to work with the code below. Thanks for your help Jim. As far as the second part of my question on if it is possible to make the workbook close when the user clicks OK when the message pops up stating the PDF file has been created?

Option Explicit

Sub PDFActiveSheet()

Dim wsa As Workbook
Dim Key As Variant
Set Key = ActiveWorkbook.Names("Key_Primary").RefersToRange

ActiveSheet.ExportAsFixedFormat xlTypePDF, "C:\Users\brian_williams\Desktop\KPI Project\Submitted Audits" & Key & ".pdf", , , False

MsgBox "PDF file has been created"
End Sub