Worksheet and Workbook with VB and .NET - Part 2

Worksheet and Workbook with VB and .NET - Part 2

In the previous part of this series, we had an overview of Worksheet and Workbook, as well as about many different methods in VBA. In this section, we will discuss the remaining standard methods, highlighting the advantages and disadvantages of the .xlsb. file format and how to convert Workbook to Image.

Method SaveCopyAs

  • With the SaveCopyAs method of the Workbook object in VBA, you can save a copy of the open Workbook.
  • Why do we need to use the SaveCopyAS method? Sometimes you want to save the Workbook with changes, and you don't want to modify the opened Workbook. So all you need is the SaveCopyAs method of the Workbook object.
  • Syntax: Workbooks(“Workbook Name”).SaveCopyAs([Filename])
  • For example: SaveCopyAs Workbook in VBA
        Sub WorkbookSaveCopyAsExample()
        ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & "ver1_" & ThisWorkbook.Name
        End Sub

Properties of the Workbook object

The following are common properties of Workbook objects in VBA:

Properties Description
Path To retrieve the absolute path of the Workbook.
FullName To get the full name of the Workbook.
Sheets Retrieve a collection of Charts and Worksheets
Worksheets To retrieve all Worksheets of Workbook.

Advantages of saving Excel file format to Excel Workbook Binary (.xlsb)

  • Many users have not realized the many benefits and advantages of .xlsb. Excel Workbook Binary Format.
  • The Excel Workbook Binary file stores information in binary form instead of the XML format as with most other Excel files. Since .xlsb. files are binary, they can be read and written to faster, making them extremely useful for massive spreadsheets. This idea also implies that calculating .xlsb. files will be much quicker.
  • .xlsx takes 4 times longer to download .xlsb, 2 times slower and 1.5 times for a larger file. It is known that a user from Stackoverflow test forum on a spreadsheet was created with 10,000 rows * 1,000 columns = 10,000,000 (10 ^ 7) cells:
.xlsx .xlsb
Loading Time 165s 43s
Time to Save 115s 61s
File size 91Mb 65Mb
  • Also, from a security perspective, since the data is in binary format and not in XML, the .xlsb. file will display unreadable content if the user tries to change the extension of the .zip. file.
  • Other advantages of the .xlsb. file format include:
  • Storing in binary allows us to appropriately save the formulas if they are longer than the 8192 character limit
  • Macros and VBA codes are fully supported

The disadvantages and limitations of .xlsb files:

  • Do not allow changing the Ribbon in the .xlsb. format. You must convert back to xlsm, make changes to your Ribbon, and then back to .xlsb.
  • Lack of interoperability with OpenOffice
  • Not compatible with Excel 2003 and previous versions
  • Compatibility of using data with software that they want to have XML instead of binary (web server, for example). If you link lots of systems together, this can cause a problem with one of the classes.
  • Note:
  • xlsm and xlsb:
  • Each file format is a zip file (tack extends .zip to the end of the file name such as test.xlsm.zip and it opens as a compressed folder in the window when you double click on it)
  • xlsm is filled mostly with clear text XML files, which we can view with a text editor.
  • xlsb is full of files that we have converted to bin format (cannot be viewed in a text editor)
  • Changing Ribbon in xlsb format is not allowed. You must convert back to xlsm, make changes to your Ribbon, and then back to xlsb.
  • This is the design on the Ribbon as do the Menu, the Tab created itself on the Ribbon (in XML), then if saving files of this type, it does not allow the design, we have to keep with xlsx format such as.
  • Lack of interoperability with OpenOffice
  • There is a lack of potential for interoperability with OpenOffice and any versions before Excel 2003. These binary files are exclusive to Excel which means that if you want to share files with a user who doesn't have it installed If you have Excel installed, you will have to convert it or save it as a standard .xlsx or a .xlsm. file so that if they have any program that can read the spreadsheet. If you do not convert it back, you can still use the .xlsb. format to make it unreadable by these programs.

And that's all the extra information about the Workbook, Worksheet, and VBA. With the great benefits that it brings, you will want to get more choices in how to use Workbook. Converting Workbook to Image is a great way, and you might want to use it. In this section, we will show you how to convert Workbook to Image with Excel Office Component

Workbook to Image with Excel Office Component

  • First, you will need a function that allows you to navigate to the ready-made spreadsheet:
  • C# Version:
        try
        {
            OpenFileDialog dlg = new OpenFileDialog();
            dlg.Title = "Select an existing spreadsheet file";
            dlg.FileName = txtFileName.Text;
            dlg.Filter = "All files|*.*";
            dlg.FilterIndex = 1;
            if (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                txtFileName.Text = dlg.FileName;
            }
        }
        catch (Exception exc)
        {
            MessageBox.Show(exc.Message, "Error");
        }
  • VB Version:
	Try
	    Dim dlg As OpenFileDialog = New OpenFileDialog()
	    dlg.Title = "Select an existing spreadsheet file"
	    dlg.FileName = txtFileName.Text
	    dlg.Filter = "All files|*.*"
	    dlg.FilterIndex = 1

	    If dlg.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
	        txtFileName.Text = dlg.FileName
	    End If

	Catch exc As Exception
	    MessageBox.Show(exc.Message, "Error")
	End Try
  • That is all for the preparation step. Make sure you have imported the Excel Office Component library.
  • C# Version: using OfficeComponent.Excel;
  • VB Version: Imports OfficeComponent.Excel
  • For the execute function, to be able to convert Workbook to Image, first, you need to create yourself an object of WorkbookManager class.
  • C# Version: WorkbookManager manager = new WorkbookManager();
  • VB Version: Dim manager As New WorkbookManager()
  • This object will act as a manager for all of your Workbooks.
  • And the first thing you need is to create a new workbook variable. This variable is a Workbook type, which we will use to store an Excel workbook. We can create this kind of Excel Workbook with the Open method - a sub-method of the workbook.
  • C# Version: Workbook workbook = manager.Workbooks.Open(txtFileName.Text, ExcelOpenType.Automatic);
  • VB Version: Dim workbook As Workbook = manager.Workbooks.Open(txtFileName.Text, ExcelOpenType.Automatic)
  • As stated in theory, each Excel Workbook will include many different sheets. So, you need to create a new sheet to add to the newly created workbook. This task can be quickly achieved by initializing a variable of the new Workbook data type, then casting the type to the Worksheet data type, as shown below.
  • C# Version: Worksheet sheet = (Worksheet)workbook.Worksheets[0];
  • VB Version: Dim sheet As Worksheet = CType(workbook.Worksheets(0), Worksheet)
  • Regarding the output, first of all, you need to create an img variable with the Image data type.
  • C# Version: Image img;
  • VB Version: Dim img As Image
  • We can use this img variable to store the output after converting from Workbook to Image. Here, we take the advance of the ConvertToImage method called from the sheet.
  • C# Version: Image img = sheet.ConvertToImage(1, 1, sheet.LastRow, sheet.LastColumn);
  • VB Version: Dim img As Image = sheet.ConvertToImage(1, 1, sheet.LastRow, sheet.LastColumn)
  • And all stages are completed. With the resulting image captured from the Workbook, you might want to save it:
  • C# Version: img.Save(OutputFile, System.Drawing.Imaging.ImageFormat.Jpeg);
  • VB Version: img.Save(OutputFile, System.Drawing.Imaging.ImageFormat.Jpeg)
  • And here are full source codes in .NET and VB for the above example:
  • C# Version:
        try
        {
            // Create a new WorkbookManager class's instance.
            WorkbookManager manager = new WorkbookManager();

            Workbook workbook = manager.Workbooks.Open(txtFileName.Text, ExcelOpenType.Automatic);

            Worksheet sheet = (Worksheet)workbook.Worksheets[0];

            string OutputFile = OutputDir + @"\Table.jpg";

            Image img = sheet.ConvertToImage(1, 1, sheet.LastRow, sheet.LastColumn);
            img.Save(OutputFile, System.Drawing.Imaging.ImageFormat.Jpeg);

            // Open the generated file.
            AskAndOpen("Worksheet converted successfully. Do you want to open the JPEG file?", OutputFile);
        }
        catch (Exception exc)
        {
            MessageBox.Show(exc.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
  • VB Version:
        Try
            ' Create a new WorkbookManager class's instance.
            Dim manager As New WorkbookManager()

            Dim workbook As Workbook = manager.Workbooks.Open(txtFileName.Text, ExcelOpenType.Automatic)

            Dim sheet As Worksheet = CType(workbook.Worksheets(0), Worksheet)

            Dim OutputFile As String = OutputDir & "\Table.jpg"

            Dim img As Image = sheet.ConvertToImage(1, 1, sheet.LastRow, sheet.LastColumn)
            img.Save(OutputFile, System.Drawing.Imaging.ImageFormat.Jpeg)

            ' Ask the user whether he/she wants to open the generated file.
            AskAndOpen("Worksheet converted successfully. Do you want to open the JPEG file?", OutputFile)
        Catch exc As Exception
            MessageBox.Show(exc.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

The full source code of this example is also available in our Excel package. If you also need Excel functionality, check out our Excel online demos.

45-Day Money Back Guarantee

We will refund your full money in 45 days
if you are not satisfied with our products

Buy Now
You have successfully subcribed to our mailing list.
Dont miss out Get update on new articles and other opportunities Subscribe