Tuesday, June 4, 2019

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.

Monday, June 3, 2019

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.



Sunday, June 2, 2019

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


Monday, May 27, 2019

VBScript rename file names in folder

VBScript code rename files;find and replace file names windows;find and replace file names VBScript;VBScript Find and Replace in File Names










1) Create a file rename_script.vbs and paste the below code. VBScript can be written using Windows Notepad or any other plain text editor.

 Set objFso = CreateObject("Scripting.FileSystemObject")  
 Set Folder = objFSO.GetFolder("C:\Users\MyName\Documents\MyDirectory\")  
 For Each File In Folder.Files  
   sNewFile = File.Name  
   sNewFile = Replace(sNewFile,"find","_")  
 if (sNewFile<>File.Name) then  
   File.Move(File.ParentFolder+"\"+sNewFile)  
 end if  


2) Save the file and double click to execute.

Note: "Swim at your own risk", the code doesn't have extensive error handling. Just be sure to replace the "C:\Users\MyName\Documents\MyDirectory" with the path to your directory.

Sunday, May 26, 2019

How to display code snippets in blogger posts

How to insert code blocks in Blogger posts;code snippets blogspot;code snippets blogger;How to display code snippets in blogger posts;vba code display in blogger

1) First, we need to add the html code(syntax template) below on the head section of our blogger post.

<head>
<link href="https://alexgorbatchev.com/pub/sh/current/styles/shCore.css" rel="stylesheet" type="text/css"></link>
<link href="https://alexgorbatchev.com/pub/sh/current/styles/shThemeEclipse.css" rel="stylesheet" type="text/css"></link>
<script src="https://alexgorbatchev.com/pub/sh/current/scripts/shCore.js" type="text/javascript">
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushJava.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushXml.js' type='text/javascript'/>
<script language='javascript' type='text/javascript'>
SyntaxHighlighter.config.bloggerMode = true;
SyntaxHighlighter.all();
</script>
</head>

2) Next step is the code rendering. Basically, we need to HTML escape some characters like right angle brackets, e.g. all < must be replaced with <. To do so, you can use the following online tool: http://codeformatter.blogspot.in/2009/06/about-code-formatter.html

3) Once you have the code with escaped characters, you can copy it on your blogger post inside
<pre> tags.


3) That's it! the complete example is below:


 <head>  
 <link href="https://alexgorbatchev.com/pub/sh/current/styles/shCore.css" rel="stylesheet" type="text/css"></link>  
 <link href="https://alexgorbatchev.com/pub/sh/current/styles/shThemeEclipse.css" rel="stylesheet" type="text/css"></link>  
 <script src="https://alexgorbatchev.com/pub/sh/current/scripts/shCore.js" type="text/javascript">  
 <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushJava.js' type='text/javascript'/>  
 <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushXml.js' type='text/javascript'/>  
 <script language='javascript' type='text/javascript'>  
 SyntaxHighlighter.config.bloggerMode = true;  
 SyntaxHighlighter.all();  
 </script>  
 </head>  
4) Another handy online converter of code to html is hilite.me. Convert the piece of code which is desirable to insert as a code block using this converter and paste the html in the post.

Let’s say we have the following piece of code which is desirable to insert as a code block:

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

Below is the result after conversion. style I used: friendly, CSS I used: border:none;



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



VBA Copy Files Using FileSystemObject

VBA FileSystemObject;Copy VBA FileSystemObject;Copy Files Using VBA FileSystemObject;copies one file from one folder to another folder with the VBA FileSystemObject






VBA code to copy file from one folder to another folder using FileSystemObject:



 Sub CopyFile(SourceFilePath As String, DestPath As String, OverWrite As Boolean)  
 ' (1) copies one file from one folder to another folder with the VBA FileSystemObject  
 ' (2) contains extensive error handling (safeguards)  
 ' (3) requires a reference to the object library "Microsoft Scripting Runtime" under Options &gt; Tools &gt; References... in the Visual Basic Editor.  
 Dim blFileExists As Boolean, blSourceErr As Boolean  
 Dim strFileName As String, strSuccessMsg As String, strNewDestPath As String, strNewSourcePath As String  
 Dim FSO As Scripting.FileSystemObject  
 Dim strErrMsg As String  
 Set FSO = New Scripting.FileSystemObject  
 'Set FSO = VBA.CreateObject("Scripting.FileSystemObject")  
 With FSO  
 strNewDestPath = .BuildPath(.GetAbsolutePathName(DestPath), "\")  
 strFileName = .GetFileName(SourceFilePath)  
 'check if the source file exists  
 If Not .FileExists(SourceFilePath) Then  
 ' check if the root drive was specified  
 If .DriveExists(Left(SourceFilePath, 2)) Then  
 blSourceErr = True  
 ' the provided source path is incomplete  
 ' build new path and ask the user if he accepts the suggestion  
 Else  
 strNewSourcePath = .BuildPath(.GetAbsolutePathName(SourceFilePath), "")  
 If Not MsgBox("The source path " &amp; Chr(34) &amp; SourceFilePath &amp; Chr(34) &amp; _  
 " is incomplete. Will you accept the following suggestion: " _  
 &amp; Chr(34) &amp; strNewSourcePath &amp; Chr(34) &amp; "?", vbYesNo, "Confirm new source path") = vbYes Then _  
 blSourceErr = True  
 End If  
 ' error  
 If blSourceErr Then _  
 strErrMsg = "The source file," &amp; Chr(34) &amp; strFileName &amp; Chr(34) &amp; _  
 " does not exist, or the specified path to the file, " &amp; Chr(34) &amp; _  
 Replace(SourceFilePath, strFileName, "") &amp; Chr(34) &amp; " is incorrect."  
 ' check if the destination folder already exists  
 ElseIf Not .FolderExists(strNewDestPath) Then  
 ' prompt the user if the destination folder should be created  
 If MsgBox("The destination folder, " &amp; Chr(34) &amp; strNewDestPath &amp; Chr(34) &amp; ", does not exist. Do you want to create it?", vbYesNo, _  
 "Create new folder?") = vbYes Then  
 .CreateFolder (strNewDestPath)  
 Else  
 strErrMsg = "The destination folder could not be created."  
 End If  
 ' check if the file already exists in the destination folder  
 Else  
 blFileExists = .FileExists(strNewDestPath &amp; strFileName)  
 If Not OverWrite Then  
 If blFileExists Then _  
 strErrMsg = "The file, " &amp; Chr(34) &amp; strFileName &amp; Chr(34) &amp; _  
 ", already exists in the destination folder, " &amp; Chr(34) &amp; _  
 strNewDestPath &amp; Chr(34) &amp; "."  
 End If  
 End If  
 ' attempt to copy file  
 If strErrMsg = vbNullString Then  
 On Error Resume Next  
 If strNewSourcePath = vbNullString Then strNewSourcePath = SourceFilePath  
 Call .CopyFile(strNewSourcePath, strNewDestPath, OverWrite)  
 If Err.Number &lt;&gt; 0 Then strErrMsg = "Run-time error " &amp; Err.Number &amp; Chr(10) &amp; Err.Description  
 On Error GoTo 0  
 End If  
 ' succesful copy  
 If strErrMsg = vbNullString Then  
 strSuccessMsg = "The file" &amp; Chr(34) &amp; strFileName &amp; Chr(34) &amp; " was copied to " &amp; _  
 Chr(34) &amp; strNewDestPath &amp; Chr(34) &amp; "."  
 If blFileExists Then strSuccessMsg = strSuccessMsg &amp; Chr(10) &amp; _  
 "(Note, the existing file in the destination folder was overwritten)."  
 MsgBox strSuccessMsg, vbInformation, "File copied"  
 ' error  
 Else  
 MsgBox strErrMsg, vbCritical, "Error!"  
 End If  
 End With  
 Set FSO = Nothing  
 End Sub  


Saturday, April 20, 2019

VBA Range Run-time error 91 Object variable or With block variable not set

Run-time error 91;Run-time error 91 Object variable or With block variable not set;Run-time error 91 while defining Range;VBA Range Run-time error 91 Object variable or With block variable not set;create 5 seconds before unload userform


Error:


I encountered the below error when I try to define a range variable.

Run-time error '91':
Object variable or With block variable not set


Code:

Dim changedRange As Range

changedRange = Sheets(sheetName).ListObjects(table).DataBodyRange --Error on this line

Solution:


Needed to use "Set", because I had defined changedRange as Range - and this is an Object ;)
Set changedRange = Sheets(sheetName).ListObjects(table).DataBodyRange

That solved the issue.


VBA - Create a time delay before unloading User Form

create delay to unload VBA userform;VBA;VBA unload userform delay;create 5 seconds before unload userform


I had a requirement to unload an VBA user form after few seconds delay, that I used to display progress of the tasks.

Solution:


You may try the below:

Application.Wait Now + TimeValue("0:00:03") 'Create a time delay of 3 seconds

Sample Code:

FrmProgress.Label1.Caption = "Loading data from database...."
FrmProgress.Show
FrmProgress.Repaint

'...code for whatever it is you want to do

Application.Wait Now + TimeValue("0:00:03") 'Create a time delay

Unload FrmProgress
Sample Code 2:

MsgBox "Running Script in Database...Please Wait..", vbInformation, "In-Progress"Wait (5)MsgBox "Script Executed Successfully!", vbInformation, "Execution Completed" Sub Wait(seconds As Integer)      Dim now As Long      now = Timer()      Do          DoEvents      Loop While (Timer < now + seconds)    End Sub


Thursday, January 24, 2019

Citrix SSL Error 61: Contact your help desk with the following information

Citrix SSL Error 61;Citrix SSL Error 61: Contact your help desk with the following information;the issuer of the server's security certificate ((SSL error 61)


I am using Ubuntu 12.04 LTS (32bit) and the latest Linux version of Citrix Receiver. Whenever I try to connect to my work network through Citrix I used get the error message below:

SSL error : Contact your help desk with the following information: You have not chosen to trust "/C=US/ST=/L=/O=Equifax/OU=Equifax Secure Certificate Authority/CN=", the issuer of the server's security certificate (SSL error 61).


Solution:


Make Firefox's certificates accessible to Citrix. Run the below code in the terminal.

sudo ln -s /usr/share/ca-certificates/mozilla/* /opt/Citrix/ICAClient/keystore/cacerts
This did the trick for me.

Could not load file or assembly System.Web.Mvc, Version=3.0.0.1


Could not load file or assembly 'System.Web.Mvc, Version=3.0.0.1;Could not load file or assembly 'System.Web.Mvc, Version=3.0.0.1, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified;

Error:

System.IO.FileNotFoundException: Could not load file or assembly 'System.Web.Mvc, Version=3.0.0.1, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified

Solution:

  • Remove the MVC reference and add the correct reference to the project
  • Change the Copy Local property of the reference to true
  • Update the bindingRedirect setting in web.config as below

web.config runtime section:
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.1" />
</dependentAssembly>

Change the Copy Local setting will include the System.Web.MVC.dll file in the bin folder when you publish the project, so that it works even if the server is not updated with the new version.

Error 3 'CompareAttribute' is an ambiguous reference between 'System.ComponentModel.DataAnnotations.CompareAttribute' and 'System.Web.Mvc.CompareAttribute'

Error 3 'CompareAttribute' is an ambiguous reference between 'System.ComponentModel.DataAnnotations.CompareAttribute' and 'System.Web.Mvc.CompareAttribute';Error 3;CompareAttribute is an ambiguous reference

Error:


Error 3 'CompareAttribute' is an ambiguous reference between 'System.ComponentModel.DataAnnotations.CompareAttribute' and 'System.Web.Mvc.CompareAttribute'

Cause:


This happens when running VS 11 with mvc Beta 4 and .Net 4.5.

Solution:


To make this work between .NET4 and .NET45 you need to add the below statement in those files to:

using CompareAttribute = System.Web.Mvc.CompareAttribute;