Rename excel sheet using VBScript

VBSCript code to rename excel sheet;Rename Excel Sheet VBScript;Excel sheet rename using VBScript;Excel sheet rename programmatically VBScript

How to rename Excel worksheet from VBScript

1) The following code renames an excel worksheet in VBScript.

To rename an excel sheet we need to open the excel file, rename the sheets, save and close it. Now we can start coding to open and rename the first excel sheet from "Sheet1" to "NewName".

1. Create a file ExcelRename.vbs and paste the below VBScript  Code

':: How to rename Excel worksheet from VBScript
'----------------------------------------------

'create the excel object
	Set objExcel = CreateObject("Excel.Application") 

'view the excel program and file, set to false to hide the whole process
	objExcel.Visible = True

'turn off screen alerts
        objExcel.DisplayAlerts = False

'open an excel file (make sure to change the location) .xls for 2003 or earlier
	Set objWorkbook = objExcel.Workbooks.Open("C:\Viji\Test.xlsx")

'Use objWorkbook.Worksheets.count to get the count of sheets
'Get the first sheet
        Set objWorksheet = objWorkbook.Worksheets(1) 
'Rename the sheet
       objWorksheet.Name = "NewName"

'save the existing excel file. use SaveAs to save it as something else
	objWorkbook.Save

'close the workbook
	objWorkbook.Close 

'exit the excel program
	objExcel.Quit

'release objects
	Set objExcel = Nothing
	Set objWorkbook = Nothing

2. Double click ExcelRename.vbs to execute. The sheet has been renamed from "Sheet1" to "NewName"

After rename

It's so simple !!!! Happy Coding.

Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine

Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine

Error:

The error message Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine is thrown when I tried accessing the Excel file using OLEDB connection.

The issue I described in my scenario occurred basically due to the incompatibility of the Microsoft.Jet.OLEDB.4.0 driver in 64 bit OS.

Solution:

So if we are using Microsoft.Jet.OLEDB.4.0 driver in a 64 bit server, we have to force our application to build in in 32 bit mode(change the build property of the project to X86) and that causes other part of my code to break.

Fortunately, now Microsoft has released a 64 bit compatible 2010 Office System Driver which can be used as replacement for the traditional Microsoft.Jet.OLEDB.4.0 driver. It works both in 32 bit as well as 64 bit servers. I have used it for Excel file manipulation and it worked fine for me in both the environments.

You can download this driver from Microsoft Access Database Engine 2010 Redistributable - 64-bit compatible 2010 office system driver

If the file extension is xls and OS is 32 bit then only you can use "Microsoft.Jet.OLEDB.4.0". Microsoft has not released 64 bit version of this driver.

If file extension is xlsx or OS is 64 bit then you must have to use "Microsoft.ACE.OLEDB.12.0". The application compiled in 32/64 bit mode does not impact the selection of driver.

Always install the 64 bit driver of Microsoft.ACE.OLEDB.12.0 on OS 64 bit. If you have already installed Office 32 bit then you need to run driver from cmd with /passive argument. This hack works till Office 2013 only, Microsoft stopped this workaround from Office 2016 for Microsoft.ACE.OLEDB.16.0 drivers.

private void ProcessFile(string path)
{
    string connString = string.Empty;

    if (Path.GetExtension(path).ToLower().Trim() == ".xls" && Environment.Is64BitOperatingSystem == false)
        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
    else
        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}

There is indeed no 64 bit version of Jet - and no plans (apparently) to produce one.

Ubuntu Mount Error: unknown filesystem type 'exfat'

Problem mounting exFAT disk on Ubuntu;How to mount exFAT drive on Ubuntu Linux;How to solve disk mount error in Ubuntu#

Ubuntu Mount Error: unknown filesystem type 'exfat'

The other day, I tried to use an external USB key formatted in exFAT format that contained a file of around 10 GB in size. As soon as I plugged the USB key, my Ubuntu 16.04 throw an error complaining that it cannot mount unknown filesystem type ‘exfat’.

Reason:

The reason behind this exFAT mount error is, Microsoft’s favorite FAT file system is limited to files up to 4GB in size. You cannot transfer a file bigger than 4 GB in size to a FAT drive. To overcome the limitations of the FAT filesystem, Microsoft introduced exFAT file system in 2006.

As most of the Microsoft related stuff are proprietary, exFAT file format is no exception to that. Ubuntu and many other Linux distributions don’t provide the proprietary exFAT file support by default. This is the reason why you see the mount error with exFAT files.

Solution:
The solution to this problem is simple. All you need to do is to enable exFAT support.

Installing the below packages only will auto-mounts your exFAT formatted drives ,

sudo apt-get install exfat-fuse exfat-utils

Once you have installed these packages, go to file manager and click on the USB disk again to mount it. There is no need to replug the USB. It should be mounted straightaway.

Rename the Excel Sheet Name in C#

Rename Excel Sheet C#;Excel sheet rename using C#;Excel sheet rename programmatically c#

Rename Excel Sheet C# Code

1) The following code renames an excel worksheet in C#.

To rename an excel sheet we need to open the excel file, rename the sheets, save and close it.

For open an Excel 2007 workbook ,we have to add the Microsoft Excel 12.0 Object Library (For Excel 2007, but it may vary depends on the Excel version you are using) in you project.


From the following pictures to show how to add Excel reference library in your project.
Select Microsoft Excel 12.0 Object Library under COM Tab and click OK button

TEST.xlsx with sheet name "Sheet1"

Now we can start coding to open and rename the first excel sheet from "Sheet1" to "General".

C#  Code

1. Add a button in a windows form

2. Add the below using statement

using Excel = Microsoft.Office.Interop.Excel; 

3. Add the below function

private void RenameExcelSheet(string sFileName)
        {

            Excel.Application app = new Excel.Application();            

            Excel.Workbook excelWorkbook;

            Excel.Worksheet excelWorkSheet;

            try
            {                

                excelWorkbook = app.Workbooks.Open(sFileName);

                if (excelWorkbook.Sheets.Count > 0)
                {

                    excelWorkSheet = excelWorkbook.Sheets[1];
                    //Rename the sheet 
                    excelWorkSheet.Name = "General";
                }

                //Save the excel 
                excelWorkbook.Save();
                //Close the excel
                excelWorkbook.Close();

            }

            catch (Exception ex)
            {
                MessageBox.Show( "Export Excel Failed: " + ex.Message);
            }


            finally
            {
                //Clean up objects 

                app.Quit();
                app = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();

            }
        }

4. Double click the button and paste the code in Click event of btnClick.

private void btnClick_Click(object sender, EventArgs e)
        {
            RenameExcelSheet("C:\\Viji\\Test.xlsx");

        }

4. Execute the project and click the Rename Excel button.

After rename

It's so simple !!!!

Code Comments:

using Excel = Microsoft.Office.Interop.Excel; - we assign the excel reference to a variable Excel.

Import SQL Server data into Microsoft Excel using VBA

SQL Server Data Import to Excel using ADO;Import SQL Server data into Microsoft Excel using VBA;Excel-SQL Server Import-Export using VBA: ADO and QueryTabl

SQL Server Data Import to Excel using ADO

1) The function inserts SQL Server data to the target Excel range using ADO.

Function ImportSQLtoExcel(sheet As String, row As Long, column As Long, commandText As String) As Integer
     ' requires a reference to the object library "Microsoft ActiveX Data Objects 2.x Library" under Options > Tools > References... in the Visual Basic Editor.

    Dim rangesheet As String
    rangesheet = Sheets(sheet).Cells(row + 1, column).Address
    
    Dim database As String
    Dim UID As String
    Dim PWD As String
    
    database = Sheets("Configuration").Cells(2, Range("Configuration[[#All],[CONFIGURATION_DATABASE]]").column).Value
    UID = Sheets("Configuration").Cells(2, Range("Configuration[[#All],[UID]]").column).Value
    PWD = Sheets("Configuration").Cells(2, Range("Configuration[[#All],[PWD]]").column).Value
    
       
    Dim connectionsheet As String
    
    connectionsheet = OdbcConnectionStringSQLServer("SQL Server", Server, database, UID, PWD)
    
    Dim cnt As ADODB.Connection
    Set cnt = New ADODB.Connection
    cnt.ConnectionString = connectionsheet
    cnt.Open
    
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnt
    cmd.commandText = commandText
    cmd.CommandType = adCmdText
       
    ' Object type and CreateObject function are used instead of ADODB.RECORDSET,
    ' After late binding without reference to
    ' Microsoft ActiveX Data Objects 2.x Library
    Dim rs As Object
    Set rs = CreateObject("ADODB.RECORDSET")
    rs.ActiveConnection = cnt
    rs.Open commandText, cnt
  
'Deletes ListObjet or QueryTable if already exist

    If Sheets(sheet).ListObjects.Count > 0 Then 'created in Excel 2007 or higher
       
        For Each tbl In Sheets(sheet).ListObjects
            tbl.Delete
        Next tbl
    ElseIf Sheets(sheet).QueryTables.Count > 0 Then ' Created in Excel 2003
         For Each tbl In Sheets(sheet).QueryTables
            tbl.ResultRange.Clear
            tbl.Delete
        Next tbl
    End If
  

   With Sheets(sheet).ListObjects.Add(SourceType:=3, Source:=rs, Destination:=Range(Sheets(sheet).Cells(row, column).Address)).QueryTable
       
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCell
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .ListObject.Name = displayname
        .ListObject.ShowTotals = False
        .Refresh BackgroundQuery:=False
    End With
    
   Sheets(sheet).ListObjects(displayname).TableStyle = "TableStyleMedium9"
   ImportSQLtoExcel = 0
   
CloseRecordset:
    rs.Close
    Set rs = Nothing
CloseConnection:
    cnt.Close
    Set cnt = Nothing
       
End Function

Function OdbcConnectionStringSQLServer(ByVal Driver As String, ByVal Server As String, ByVal database As String, _
    ByVal Username As String, ByVal Password As String) As String

'SQL Server
  
     OdbcConnectionStringSQLServer = "Driver={" & Driver & "};Server=" & Server _
            & ";UID=" & Username & ";PWD=" & Password & ";Database=" & database
  

End Function