Signature Time Stamp in Excel cells

babar2019

Board Regular
Joined
Jun 21, 2019
Messages
93
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
 
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
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
Try
VBA Code:
fileN = Range("C9") & "_" & Range("C10") & "_" & Range("I10") & "_" & Sheet2.Name & "_" & Format(CStr(Now), "dd-mm-yyyy_hh-mm")
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top