Originally Posted by
nathandavies
Thanks Paul,
When would i call this function in to action??
Nathan
Just like any other function
Sub PrintAdviceNote()
ProjectRootFolder = Sheets("RootFolder").Range("B5").Value
JobNumber = Sheets("Summary").Range("CJobNumber").Value
CSiteName = Sheets("Summary").Range("CSiteName").Value
CCompanyName = Sheets("Summary").Range("CCompanyName").Value
AdviceNote = Sheets("AdviceNote").Range("DelNote").Value
filenametosave = ProjectRootFolder & Application.PathSeparator
filenametosave = filenametosave & CCompanyName & Application.PathSeparator
filenametosave = filenametosave & JobNumber & " " & CSiteName & Application.PathSeparator
filenametosave = filenametosave & AdviceNote & "_" & Format(Date, "dd-mm-yyyy") & ".pdf"
MsgBox filenametosave
filenametosave = cleanfilename(filenametosave)
MsgBox filenametosave
Sheets("AdviceNote").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
With Sheets("AdviceNote")
.Visible = True
.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=filenametosave, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
.Visible = True
End With
Sheets("Summary").Select
ActiveWorkbook.Save
End Sub
Function fileCleanName(s As String, Optional ThisIsAFolder As Boolean = False) As String
Dim s1 As String, s2 As String
Dim i As Long
s1 = vbNullString
s2 = Trim(s)
If Not ThisIsAFolder Then
For i = 1 To Len(s2)
Select Case Mid(s2, i, 1)
Case Chr(0) To Chr(32), "<", ">", ":", """", "/", "\", "|", "?", "*"
s1 = s1 & "_"
Case Else
s1 = s1 & Mid(s2, i, 1)
End Select
Next i
'paths can have / and \
Else
For i = 1 To Len(s2)
Select Case Mid(s2, i, 1)
Case Chr(0) To Chr(32), "<", ">", ":", """", "|", "?", "*"
s1 = s1 & "_"
Case Else
s1 = s1 & Mid(s2, i, 1)
End Select
Next i
End If
Do While InStr(s1, "__") > 0
s1 = Replace(s1, "__", "_")
Loop
fileCleanName = s1
End Function