Scribd
Upload
39 views2 pages

Vba Code

The document contains two VBA subroutines for generating invoice numbers and saving sheets as PDF files in Excel. The 'GenerateInvoiceNumber' subroutine creates a new invoice number based on a prefix and increments it, while the 'SaveAsPDF' subroutine constructs a filename from the invoice number and customer name, checks for the existence of a specified folder, and exports the active sheet as a PDF. Error handling is included to manage potential issues during execution.

Uploaded by

netmirror272
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
39 views2 pages

Vba Code

The document contains two VBA subroutines for generating invoice numbers and saving sheets as PDF files in Excel. The 'GenerateInvoiceNumber' subroutine creates a new invoice number based on a prefix and increments it, while the 'SaveAsPDF' subroutine constructs a filename from the invoice number and customer name, checks for the existence of a specified folder, and exports the active sheet as a PDF. Error handling is included to manage potential issues during execution.

Uploaded by

netmirror272
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

Sub GenerateInvoiceNumber()

Dim prefix As String


Dim invoiceNumber As Long
Dim lastInvoiceNumber As String
Dim newInvoiceNumber As String
Dim ws As Worksheet

On Error GoTo ErrorHandler ' Start error handling

' Set your worksheet (adjust the name if needed)


Set ws = ThisWorkbook.Sheets("DELIVERY CHALAN") ' Change "Sheet1" to your sheet
name

' Set the prefix for the invoice number


prefix = "EBN/DGP/"

' Get the last invoice number from cell F3 (if it exists)
lastInvoiceNumber = ws.Range("F3").Value

' If there's already an invoice number in cell F3, extract the number part
If lastInvoiceNumber <> "" Then
' Check if the number is valid
If IsNumeric(Mid(lastInvoiceNumber, Len(prefix) + 1)) Then
' Extract the numeric part and increment it
invoiceNumber = CLng(Mid(lastInvoiceNumber, Len(prefix) + 1))
Else
MsgBox "Invalid invoice number format in F3!", vbExclamation
Exit Sub
End If
Else
' If it's the first time, start at 1
invoiceNumber = 0
End If

' Increment the invoice number by 1


invoiceNumber = invoiceNumber + 1

' Format the new invoice number (adjust to 3 digits, like 001, 002, etc.)
newInvoiceNumber = prefix & Format(invoiceNumber, "000")

' Set the new invoice number in cell F3 (DO NOT clear other cells!)
ws.Range("F3").Value = newInvoiceNumber
Range("E7,F6,F5,M7,I10:M19,D26:H31,M26:M31").ClearContents

Exit Sub

ErrorHandler:
MsgBox "An error occurred: " & Err.Description, vbCritical
End Sub

Sub SaveAsPDF()
Dim invoiceNo As String
Dim customerName As String
Dim fname As String
Dim Path As String

' Get invoice number and customer name from specific cells
invoiceNo = Range("F3").Value
customerName = Range("C10").Value

' Construct file name and remove invalid characters


fname = invoiceNo & " - " & customerName & ".pdf"
fname = Replace(fname, "/", "_")
fname = Replace(fname, "\", "_")
fname = Replace(fname, ":", "_")
fname = Replace(fname, "*", "_")
fname = Replace(fname, "?", "_")
fname = Replace(fname, """", "_")
fname = Replace(fname, "<", "_")
fname = Replace(fname, ">", "_")
fname = Replace(fname, "|", "_")

' Specify file path


Path = "C:\Users\EBN EDWARD TECNOLOGY\Desktop\BACKUP DC\"

' Check if the folder exists


If Dir(Path, vbDirectory) = "" Then
MsgBox "The specified folder does not exist: " & Path, vbExclamation
Exit Sub
End If

' Check if ActiveSheet exists


If ActiveSheet Is Nothing Then
MsgBox "No active sheet found.", vbExclamation
Exit Sub
End If

' Export active sheet as PDF


On Error Resume Next
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:=Path & fname, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
If Err.Number <> 0 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
Exit Sub
End If
On Error GoTo 0

' Display confirmation message


MsgBox "PDF file saved successfully: " & Path & fname, vbInformation
End Sub

You might also like