Generate PDF document from Excel - VBA

Welcome to Logically Proven blog.

This post demonstrates how to generate a PDF document from Excel using VBA (Visual Basic for Applications).

Sometimes it is required to generate a PDF document from the Excel workbook or worksheet. This post gives you a basic idea how to generate the PDF document from a workbook or a worksheet.

We have to follow these two points in  order to generate a PDF document.

1. Page set up
2. Export as PDF

Page set up:

It is very important to set up the page of a worksheet before exporting as a PDF document.
The following line will set the paper size of a worksheet to A4.

    'set worksheet paper size to A4
    'Report is the worksheet name
    Report.PageSetup.PaperSize = xlPaperA4

If you want to see the available paper sizes please follow this link -
https://msdn.microsoft.com/en-us/library/office/ff834612.aspx

If you want to set the paper size of a complete workbook iterate through each worksheet in a workbook.

Export as PDF:

The second step is exporting the worksheet/workbook as PDF document. The following example exports the "Report" worksheet to PDF document.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

Sub GeneratePDF() 'Declare variables Dim strFileName As String Dim ReportNum As Long Dim strPath As String Dim strFilePath As String 'error handling On Error GoTo err_handler 'Set report name strFileName = "ReportName" 'Save the pdf document on desktop 'set the path if you want to save the pdf document in a different folder strPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator 'full path strFilePath = strPath & strFileName 'set worksheet paper size to A4 'Report is the worksheet name Report.PageSetup.PaperSize = xlPaperA4 'Replace Report with ThisWorkbook if you want to export the complete workbook 'Export the sheet Report in pdf Report.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:=strFilePath, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False Exit Sub err_handler: MsgBox Err.Description, vbOKOnly, "Error" End Sub

In line 17: we are getting the user's desktop path. This line works only if have a reference to the "Microsoft Shell Controls and Automation" library. Please add a reference to this library.

For adding, in code window
Select Tools -> Select References -> Search for the library in the list -> Check the button "Microsoft Shell Controls and Automation" -> Press OK.

In line 29: This code exports only one worksheet. If you want to export the entire workbook, replace the Report in line 29 with ThisWorkbook.

In line 30: We are defining the type of document to export. In our case PDF.

In line 31: Mention full path. you can give directly the file path including file name in double-quotes (e.g. "C:\ReportName").

In line 32: Mention the quality of the document. (e.g. images) The other available quality is xlQualityMinimum if you reduce the memory size of the exported document (e.g. if your report has more images).

In line 35: If you change the value to "True", the PDF document opens immediately after generating.

This way you can export the workbook/worksheet to PDF.


Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.


Logically Proven
Learn, Teach, Share

Read More

Difference between worksheet display names and code names in Excel (VBA)

Welcome to Logically Proven blog.

This post demonstrates the difference between  worksheet code names and worksheet display name. And also explains which name is better to use in the VBA (Visual Basic for Applications) code.

In an excel workbook, every worksheet has two names - display name and code name.

Display name:

Display name is the name visible to every user and every user is allowed to edit the display name of the worksheet by default (if workbook is not protected).

Code name:

Code name is the another name of a worksheet which is not visible to every user. The code name is visible only in the code window of workbook application. Press Alt+F11 to view the code names of worksheets in the code window.

The another way to open the code window is go to Developer tab in the excel ribbon, click on Visual Basic as shown in the picture below.

If you don't see the developer tab, follow this (Microsoft Excel 2010)- select excel file menu (office symbol on the top left) -> select Excel Options -> Select Popular Section -> check the button "Show Developer tab in the Ribbon" and press OK as shown in the picture below -

Once you press the Alt+F11, the following code window opens where you will find the code names of the worksheets.

To view the properties window press F4 or in the code window select View menu and click on Properties Window.

Then select any one of the sheets, you get all the properties of the sheet and you can edit the code name of the sheet in the (Name) column. This name is only visible in the code window but not to the every user.

Press Alt+F11 again to switch between code window and excel workbook.

Understand why it is important to use code names in your code?

Consider you are writing VBA code to perform some tasks with the worksheets in a workbook.
In your code you are referring to a worksheet with a display name as shown below. Consider the code name and display name of worksheet is Sheet1.

Sub test()
    
    ThisWorkbook.Sheets("Sheet1").Range("A1").Value = "Hello,"
    ThisWorkbook.Sheets("Sheet1").Range("B1").Value = "this"
    ThisWorkbook.Sheets("Sheet1").Range("C1").Value = "is a"
    ThisWorkbook.Sheets("Sheet1").Range("D1").Value = "test code."
    
End Sub

This code works very well until unless user is not changing the display name of a worksheet.

What happens if user changes the display name of a worksheet. You will get the following error message because the application is unable to find the display name of a worksheet. (considering without error handling for better explanation)

In this case, it is an extra work to search all your code and replace the display names of the sheets. This is a bad solution to replace because if user changes again the display name. Thus it is never ending process.

The permanent solution is not to use display names of a worksheet in the code. The optimal solution is to use code names or getting the index of a worksheet by searching the code names as shown below.

Using code names directly in the code:

Sub test()
    
    Sheet1.Range("A1").Value = "Hello,"
    Sheet1.Range("B1").Value = "this"
    Sheet1.Range("C1").Value = "is a"
    Sheet1.Range("D1").Value = "test code."
    
End Sub

This code works fine even the user renames the display name of a worksheet. But it is better not to refer the worksheet so many times. Unnecessarily the memory size of application grows. If you are using just one or two times in method/function it is okay. If you want to refer more times initialize an worksheet object as shown below.

Using index of a worksheet by searching code names:

Sub test()
    
    'declaring the variable of type worksheet
    Dim wksInput As Worksheet
         
    'initialize the object
    Set wksInput = ThisWorkbook.Sheets(GetWorksheetIndex("Sheet1"))
    
    'with keyword can be used as below
    With wksInput
        .Range("A1").Value = "Hello,"
        .Range("B1").Value = "this"
        .Range("C1").Value = "is a"
        .Range("D1").Value = "test code."
    End With
  
  'release the object before exit
  Set wksInput = Nothing
    
End Sub

Here we are getting the index of a worksheet by searching the code name of a worksheet "GetWorksheetIndex". And we are using worksheet object wksInput for speeding the application. Never forget to release the worksheet object if you don't require any more to speed up the application.

GetWorksheetIndex function:

Function GetWorksheetIndex(strSheetName As String) As Integer

    Dim intCounter As Integer
    Dim intIndex As Integer
    Dim wkb As Workbook

    On Error GoTo err_handler
    Set wkb = ThisWorkbook

    intIndex = 0
    
      'iterate through all worksheets in a workbook
       For intCounter = 1 To wkb.Worksheets.Count
          If UCase(wkb.Worksheets(intCounter).CodeName) = UCase(strSheetName) Then
             intIndex = intCounter
             Exit For
          End If
       Next intCounter

    GetWorksheetIndex = intIndex

endthis:
    Set wkb = Nothing
    Exit Function

err_handler:
    MsgBox Err.Description, vbOKOnly, "Error in GetWorksheetIndex"
    Resume endthis
End Function

Even user changes the display name of a worksheet, we never run into errors because we are using code name of a worksheet. In case if you want to change the code name of a worksheet, it is required to change the code names in the code else you will run into runtime errors. Please use error handlers for the smooth running of your application as shown in the GetWorksheetIndex function.


Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.


Logically Proven
Learn, Teach, Share

Read More

Export Excel Worksheet(s) to a new workbook in Excel - VBA

Welcome to Logically Proven blog.

This post demonstrates how to export a particular sheet or all worksheets to a new workbook using excel VBA (Visual Basic for Applications).

In certain situations, it is required to export a current workbook data to a new workbook or do you want to save a backup of some worksheets of a workbook. In either of these cases, you can use the following VBA code to accomplish this task.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Sub CreateCopy() Dim w As Worksheet, nb As Workbook, s As String For Each w In ThisWorkbook.Worksheets If w.Name <> "Approvals" And w.Name <> "Calcs" Then If nb Is Nothing Then w.Copy 'creates new workbook and copies sheet to it Set nb = ActiveWorkbook Else w.Copy after:=nb.Sheets(nb.Sheets.Count) End If End If Next w s = ThisWorkbook.FullName s = Left(s, Len(s) - 4) & "2.xls" nb.SaveAs s End Sub

In this example,

nb is the new workbook where we are exporting the worksheets.

In line 3: We are iterating through all the available worksheets in the currently working workbook.

In line 4: In case if you don't want to export some sheets, exclude those sheets. In this example I am excluding the sheets "Approvals" and "Calcs". If you want to export all the worksheets, remove this condition.

In line 5,6: If new workbook is not created, it creates a new workbook and copies worksheet.

In line 9: Copies the worksheet at the end (after last sheet) in the new workbook. You can modify this line if you want to change the order of the worksheets in the new workbook.

In line 13: Getting the name of the current workbook including the path. (.FullName fetches the name of the workbook as well as the path of the workbook)

In line 14: Setting the new workbook name. Consider the currently working workbook name is "ExportData.xlsm", this line sets the new workbook name as "ExportData2.xls".

In line 15: Saving the workbook.

Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.


Logically Proven
Learn, Teach, Share

Read More

Dictionary object in Excel - VBA

Welcome to Logically Proven blog.

This post demonstrates dictionary object in Excel VBA (Visual Basic for Applications).

A dictionary class is a data structure that represents a collection of keys and values pair. The key is unique (identical) in a key-value pair and it can have at most one value in the dictionary, but a value can be associated with many different keys.

A dictionary class contains following members -

Add(key, item) - add a new key and item to the dictionary.
CompareMode - set or get the string comparison method.
Count ()- get the number of items in the dictionary. it is read-only.
Exists(Key) -  determine if a given key is in the dictionary.
Item(Key) - get or set the item for a given key.
Items() - get an array containing all items in the dictionary.
Key(Key) - change a key to a different key.
Keys() - get an array containing all keys in the dictionary.
Remove(Key) - remove a given key from the dictionary.
RemoveAll() -  remove all information from the dictionary.

The dictionary is available in the library Microsoft Scripting Runtime. Add a reference to the "Microsoft Scripting Runtime" library as shown below -

In the code window (Press Alt+F11 to switch between windows) -> Select Tools -> Select References -> From the list find "Microsoft Scripting Runtime" -> check it and Press "Ok".

The following example illustrates the dictionary class and the members of dictionary.

'Mandatory to declare variables explicitly
Option Explicit

'declaring global variable
Dim dict As Dictionary

Sub DictionaryExample()
Dim keyArray, itemArray, element

'Initilalize a dictionary object
Set dict = New Dictionary

'dictionary object
With dict
   'set compare mode
   .CompareMode = BinaryCompare
   
   'Other compare modes
   '.CompareMode = DatabaseCompare
   '.CompareMode = TextCompare
   
   'add item using named arguments
   .Add Key:="mike", Item:=22
    'keys are case-sensitive
    'Mike and mike are different
   '.Add Key:="Mike", Item:=22 'this line gives compiler error
   'add item without named arguments
   .Add "joe", 33
            
   'case sensitivity and Exists method
   'does MIKE exist?
   Debug.Print "MIKE exists = " & .Exists("MIKE")
   'change key value
   .Key("mike") = "MIKE"
   'does MIKE exist?
   Debug.Print "MIKE exists = " & .Exists("MIKE")

   'extract keys into variant array
   Debug.Print "Array of Keys"
   keyArray = .Keys
   For Each element In keyArray
      Debug.Print element
   Next

   'extract items into variant array
   Debug.Print "Array of Items"
   itemArray = .Items
   For Each element In itemArray
      Debug.Print element
   Next
    
    'Items in dictionary
    Debug.Print dict.Count & " Items in Dictionary"
   'empty the dictionary
   .RemoveAll
   Debug.Print dict.Count & " Items in Dictionary"

End With
//release the dictionary object
Set dict = Nothing
End Sub


'output:
'MIKE Exists = False
'MIKE Exists = True
'Array of Keys
'MIKE
'joe
'Array of Items
'22
'33
'2 Items in Dictionary
'0 Items in Dictionary

There are three comparison modes available. By default the comparison mode is BinaryCompare. The other two are DatabaseCompare and TextCompare.

To understand the difference between these comparison please follow this link - https://msdn.microsoft.com/en-us/library/8t3khw5f.aspx

Binary comparison is case-sensitive. So 'Mike' and 'mike' are different in the given example. If you try to add the same key twice, you will encounter the following error message.

Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.


Logically Proven
Learn, Teach, Share

Read More