Copy from excel to word and saveas PDF


Problem : An HR in a startup who had this excel to generate salaries and then convert them into pdf manually. As people increased this became a tedious task.

Solution:  Automation :)

Here is the code that helped me achieve that:

1. Created a button in excel that copies data from the main sheet to the desired format in another sheet.
2. Pick the template to generate pdf
3. Copy the template and open the new word document
4. Copy generated text from excel into the new word document
5. Save as pdf, All set!!


Copy Excel details from one sheet to other -> Copy the text from generated sheet into word template ->
Save the copied word document as PDF


Sub Button1_Click()


Dim LSearchRow As Long
Dim LCopyToRow As Long
Dim wksInput As Worksheet
Dim wksOutput As Worksheet
On Error GoTo Err_Execute

'The wksInput variable will hold a reference to the worksheet
'that needs to be searched
Set wksInput = ThisWorkbook.Worksheets("SalarySheet")
Set gnrt = ThisWorkbook.Worksheets("Payslip")
empname = gnrt.Cells(7, 2)

'Start copying data to row 2 in Sheet2 (row counter variable)
'Loop through all the rows that contain data in the worksheet
'Start search in row 4
For LSearchRow = 2 To wksInput.UsedRange.Rows.Count
'If value in column A = "", copy entire row to wksOutput

If wksInput.Cells(LSearchRow, 1) = empname Then
' One line copy/paste

 wksInput.Cells(LSearchRow, 2).Copy gnrt.Cells(8, 2)
 wksInput.Cells(LSearchRow, 4).Copy gnrt.Cells(11, 2)
 wksInput.Cells(LSearchRow, 5).Copy gnrt.Cells(12, 2)
 wksInput.Cells(LSearchRow, 6).Copy gnrt.Cells(13, 2)

 'Increment the output row
 'LCopyToRow = LCopyToRow + 1
End If
Next LSearchRow

gnrt.Activate
FName = "C:\Salary slip.docx"

'To save the file with this name
SaveDoc = "C:\" & gnrt.Range("B7").Text & "_SalarySlip.pdf"

'CREATE WORD OBJECT
On Error Resume Next
Set wrdApp = CreateObject("Word.Application")

'DISPLAY WORD APPLICATION
On Error Resume Next
wrdApp.Visible = True
wrdApp.Activate

On Error GoTo 0
'OPEN THE (TEMPLATE) FILE
wrdApp.Documents.Open (FName)

'SET A VARIABLE TO REFERENCE ACTIVE DOCUMENT (TEMPLATE)
Set tempDoc = wrdApp.ActiveDocument
'DUPLICATE THE DOCUMENT
wrdApp.Documents.Add wrdApp.ActiveDocument.FullName
'SET A VARIABLE TO REFERENCE THE NEW VERSION OF DOCUMENT
Set mrgDoc = wrdApp.ActiveDocument

'CLOSE THE ORIGINAL (TEMPLATE) VERSION OF DOCUMENT
tempDoc.Close SaveChanges:=False

'ACTIVATE THE NEW DOCUMENT
mrgDoc.Activate
wrdApp.Visible = True

'Copy the data
ThisWorkbook.Sheets("Payslip").Range("a6:c22").Copy
'wrdApp.Selection.PasteExcelTable False, False, False
mrgDoc.Paragraphs(mrgDoc.Paragraphs.Count).Range.InsertParagraphBefore
mrgDoc.Paragraphs(mrgDoc.Paragraphs.Count).Range.Paste
'mrgDoc.SaveAs Filename:=SaveDoc, FileFormat:=wdFormatPDF
mrgDoc.ExportAsFixedFormat SaveDoc, 17, OpenAfterExport:=True

wrdApp.Visible = False
wrdApp.Quit False

Set mrgDoc = Nothing
Set wrdApp = Nothing

MsgBox "All matching data has been copied and pdf generated!"

Exit Sub
Err_Execute:
MsgBox "An error occurred. Number: " & Err.Number & " Description: " & Err.Description
End Sub

Comments

Popular posts from this blog

Software Testing @ Microsoft

Trim / Remove spaces in Xpath?