Signature Time Stamp in Excel cells

[babar2019]

Hi All,

I have the code below which converts the activesheet and the sheet called 'total' into pdf.

I would like to capture the current username who is logged in and convert that to their full name and capture the date/time stamp that the macro was run while converting to pdf and insert into two different cells of the active sheet.

Example: If I'm logged in and my computer name is jsmith, I want the code to insert "John Smith" into A1 cell and date/timestamp in A2 cell of the active sheet and convert it to pdf. I don't want the macro to insert any name & date/timestamp in the 'total' sheet.

Sub SaveasPDF()

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet

Sheets(Array("total", wsA.Name)).Select

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & ""

strName = "WORK AS OF" & " " & Format(Date, "MM-DD-YYYY")

'create default name for saving file
strFile = strName & ".pdf"
strPathFile = strPath & strFile

'Create DAily folder under Today's dte
MkDir ("P:\INFORMATION TECHNOLOGY\Non-Public\Applications" & Format(Date, "MM-DD-YYYY"))

'export to PDF in current folder
wsA.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strPathFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& strPathFile

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

Thank you

 

[babar2019]

I have modified but what this is doing is creating the folder '1 folder back' from where the actual excel was launched from.

Example: if I launch it from P:\INFORMATION TECHNOLOGY\Non-Public\Applications\Test, it's creating under P:\INFORMATION TECHNOLOGY\Non-Public\Applications

 

[babar2019]

I have modified it.

What this is doing is creating the folder '1 folder back' from wherever the excel is launched from.

example: If I launch it from P:\INFORMATION TECHNOLOGY\Non-Public\Applications\Test\ it is creating the folder under P:\INFORMATION TECHNOLOGY\Non-Public\Applications

 

[Trevor_S]

Following on from this thread, you've asked me by direct message if it's possible to incorporate an actual signature image. Unfortunately your mailbox is full, so won't accept direct replies.

I've never done it, but the code in this link should work:
https://techcommunity.microsoft.com/...le/td-p/189094

But are you sure you want to do this ? You would need to store scanned images of everyone's signatures on the server. In theory these could then be pasted on any document without the individual even knowing - sounds like a security risk?

 

[Atiqul Haque]

Exactly. In the line: Set wbA = ActiveWorkbook you are defining wbA is being the active spreadsheet. Therefore the coding wbA.Path would return the path of the active workbook, wherever it was launched from.

So instead of:
strPath = "P:\INFORMATION TECHNOLOGY\Non-Public\Applications" & Format(Date, "MM-DD-YYYY")
try:
strPath = wbA.Path & Format(Date, "MM-DD-YYYY")

My pdf file saved like "123_Name_Age_SheetName_Date & Time" ("C9_C10_I10_SheetName_Date & Time).


Sub createPDF()

'saves the file as PDF and adds a date parameter to the name of the file
Dim fileN As String
fileN = Range("C9") & "_" & Range("C10") & "_" & Range("I10") & "_" & "SheetName" & "_" _
& Format(CStr(Now), "dd-mm-yyyy_hh-mm")
Application.EnableEvents = False
Sheet2.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\User\Desktop\Save File As PDF\" & fileN & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
Application.EnableEvents = True

End Sub

 

[mohadin]

Try

fileN = Range("C9") & "_" & Range("C10") & "_" & Range("I10") & "_" & Sheet2.Name & "_" & Format(CStr(Now), "dd-mm-yyyy_hh-mm")