How to add/insert leading zeros to numbers or text in Excel?

[Origin]: https://www.extendoffice.com/documents/excel/792-excel-add-leading-zeros-to-number-text.html

Supposing you have a range of data, and you need to add zeros in front of each cells as following screenshots shown. How could you quickly add the zeros before the numbers?

doc-add-leading-zeros1 -2 doc-add-leading-zeros2

 

 


arrow blue right bubble Add/ insert leading zeros to numbers with Text function

The Text function will add zeros in front of numbers, and make the new numbers with leading zeros in certain length.

Step 1: Enter the formula =Text (A1, “00000”) in a blank cell which is adjacent to the data cell.

doc-add-leading-zeros3

Step 2: Then press Enter key, and select cell C1 drag the fill handle Description: Selected cell with fill handle across the range that you want to fill. Then you will view all the numbers in A1:B6 are copied and pasted to C1:D6 with leading zeros. And each numbers contains 5 digits.

doc-add-leading-zeros4

Tips: 1. As they are formulas, so when you need to copy and paste them to other places, you need to paste them as values.

2. This Text function can not add leading zeros for text.


arrow blue right bubble Add/ insert leading zeros to numbers and text with Concatenate function

If you want to insert specific digit of leading zeros into each number, for example three leading zeros for each number, you should try the Concatenate function.

Step 1: Enter the formula =Concatenate (“000”, A1) in a blank cell and press Enter key. In this case, we enter the formula in Cell C1.

Step 2: Click the Cell C1, and drag the fill handle Description: Selected cell with fill handle across the range that you want to fill. Then you will view numbers in A1:B6 are pated to C1:D6, and each number contains three leading zeros.

doc-add-leading-zeros5

Dependent Drop-down Lists

[Originally Posted By]: http://www.excel-easy.com/examples/dependent-drop-down-lists.html

This example describes how to create dependent drop-down lists in Excel. Here’s what we are trying to achieve:

The user selects Pizza from a drop-down list.

First Drop-down List

As a result, a second drop-down list contains the Pizza items.

Second Drop-down List

To create these dependent drop-down lists, execute the following steps.

1. On the second sheet, create the following named ranges.

Name Range Address
Food A1:A3
Pizza B1:B4
Pancakes C1:C2
Chinese D1:D3

Named Ranges

2. On the first sheet, select cell B1.

3. On the Data tab, click Data Validation.

Click Data Validation

The ‘Data Validation’ dialog box appears.

4. In the Allow box, click List.

5. Click in the Source box and type =Food.

Validation Criteria

6. Click OK.

Result:

First Drop-down List

7. Next, select cell E1.

8. In the Allow box, click List.

9. Click in the Source box and type =INDIRECT($B$1).

Indirect Function

10. Click OK.

Result:

Dependent Drop-down Lists

Explanation: the INDIRECT function returns the reference specified by a text string. For example, the user selects Chinese from the first drop-down list. =INDIRECT($B$1) returns the Chinese reference. As a result, the second drop-down lists contains the Chinese items.

Excel VBA Drop Down Lists Using Data Validation

[Originally posted by]: http://software-solutions-online.com/excel-vba-drop-down-lists/

Excel VBA Drop Down Lists Using Data Validation

JUL 27, 2015

In this article I will explain how you can create drop down lists using data validation.

Jump To:

You can download the file and code related to this article from the link below:


Creating Data Validation (Manually):

Step1 : In the first step you would need to print the data you are going to fill the drop down list with somewhere. Usually I open a new sheet, name it something no one would ever consider using (like “far43fq”) and print the data there.

Excel VBA, Drop Down Lists, Data Validation Data

Step 2: Select the cell you would like to add the drop down list to. Then click on the Data Validation button on the Data Ribbon:

Adding a Data Validation to a specific cell, Excel VBA

Step 3: Select list:

Selecting the list type data validation from the list of available data validation types excel vba

Step 4: Input the range of the data. If the drop down list (data validation) and the data are in the same sheet you would reference them using a statement like “=A1:A6”. If they are in separate sheets you would use a statement like “=SheetName!A1:A6”, where “SheetName” is replaced with the name of the sheet.

Excel VBA, Drop Down Lists, Data Validation Input Data

Note: I this example the input data is in another sheet. The name of the sheet is Sheet1″.

After pressing Ok your drop down list is ready:

A drop down list created using data validation


Creating Data Validation (Using VBA):

Using the code below a drop down list (data validation) will be created in the cell “J2” . The data for the drop down list will come from the range “=A1:A6” in the sheet “Sheet1”. Note you must change the highlighted parts based on the location of your source and the location for your drop down list:

Private Sub main()
	'replace "J2" with the cell you want to insert the drop down list 
	With Range("J2").Validation
		.Delete 
		'replace "=A1:A6" with the range the data is in. 
		.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
		Operator:= xlBetween, Formula1:="=Sheet1!A1:A6" 
		.IgnoreBlank = True 
		.InCellDropdown = True 
		.InputTitle = "" 
		.ErrorTitle = "" 
		.InputMessage = "" 
		.ErrorMessage = "" 
		.ShowInput = True 
		.ShowError = True 
	End With 
End Sub

 


Selection Change:

The data validation itself doesn’t have a built in function for determining when the user hasselected a new value. Though you could use the worksheet_change event handler to determine when the user has selected a new value from the drop down list.  Theworksheet_change event triggers every time changes are made to a worksheet. You could use the worksheet_change event handler to catch this event and check if the changes made were to the value selected in the drop down list.

The code below is a worksheet_change event handler. It checks if the changes in the worksheet have occurred in the cell with the drop down list or not:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("J2").Address Then 
        'your code 
    End If 
End Sub 

Modifying, Adding, Inserting and Removing Items (Usin VBA):

In order to modify, add, insert and remove items from a drop down list created using data validation, you would have to follow 2 steps.

Step 1: The first thing you would have to do is change the source data. For example lets say we want to modify the second item to “New Item 2”,  we would need to change thedata validation’s source to the values below:

Excel VBA, Drop Down Lists, Data Validation Data, Modified Data
Or for example lets say we want to add an item to the list of items. Again the first thing would be to modify the source data:

Excel VBA, Drop Down Lists, Data Validation Data, Modified Data 2
Or for example lets say we want to remove “item 4”. Again the first step would be tomodify the source data:

Excel VBA, Drop Down Lists, Data Validation Data, Modified Data 3
Step 2: In the next step we need to update the drop down list to accommodate for the changes made in its source. This can be done using the code below. The code below must be copied to the sheet with the source data. The highlighted parts must be changed based on the location of your source data and the location you would like the drop down list to appear:

Option Explicit
Dim flagProgram As Boolean 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim intRowCount As Integer 
    If flagProgram = False Then 
	flagProgram = True 
	  
	'get the total rows of data 
	intRowCount = Get_Count 
	'update the drop down list(data validation) 
	Call Update_DataValidation(intRowCount) 
	   
	flagProgram = False 
    End If 
End Sub

'This function will return the total count of rows in the 
'drop down list(data validation) source 
Private Function Get_Count() As Integer 
    'counter 
    Dim i As Integer 
    'determines if the we have reached the end 
    Dim flag As Boolean 

    i = 1
    flag = True
    While flag = True
	If Cells(i, 1) <> "" Then 
  	    'if there is still data go on 
	    i = i + 1 
	Else 
	    'if there is no more data left stop the loop 
	    flag = False 
	End If 
    Wend 

    'return the total row count 
    Get_Count = i - 1
End Function 

'the function below updates the source range for the data validation 
'based on the number of rows provided by the input 
Private Sub Update_DataValidation(ByVal intRow As Integer)
    'the reference string to the source range 
    Dim strSourceRange As String 

    strSourceRange = "=Sheet1!A1:A" + Strings.Trim(Str(intRow))
    With Sheet2.Range("J2").Validation
	.Delete 
	.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ 
	Operator:= xlBetween, Formula1:=strSourceRange 
	.IgnoreBlank = True 
	.InCellDropdown = True 
	.InputTitle = "" 
	.ErrorTitle = "" 
	.InputMessage = "" 
	.ErrorMessage = "" 
	.ShowInput = True 
	.ShowError = True 
    End With 
End Sub

The code above has 3 different function. The main function is Worksheet_Change event handler. The event handler executes when the user makes changes to the sheet with the source data:

Private Sub Worksheet_Change(ByVal Target As Range)
...
End Sub 

flagProgram determines if the current changes made to the sheet have been done by the program or the user. This is to prevent an endless recursion of the Worksheet_Changeevent handler:

If flagProgram = False Then 
    flagProgram = True 
    ... 
    flagProgram = False 
End If

The line below gets the number of rows in the source for the data validation. This value must be checked each time to account for added and removed items:

intRowCount = Get_Count

The function Update_DataValidation updates the data validation based on the input parameter intRow. The input parameter intRow determines how many rows of data thedrop down list must use. The first line of this function creates a string which is a reference to the range with the source data:

strSourceRange = "=Sheet1!A1:A" + Strings.Trim(Str(intRow))

Note the highlighted section must changed if your source data is not in Sheet1 starting from cell A1. The resulting string will be something like this:

“=Sheet1!A1:A5”

or

“=Sheet1!A1:A7”

For more information about string processing and manipulation please see the link below:

The rest of the lines in the function Update_DataValidation creates a drop down list in the cell “J2” in sheet2.

You can download the file and code related to this article from the link below:

See also:

If you need assistance with your code, or you are looking to hire a VBA programmer feel free tocontact me. Also please visit my website  www.software-solutions-online.com

Use parameterised SQL Server stored procedure in Excel database query

[Originally Posted By]: https://social.msdn.microsoft.com/Forums/en-US/d86fbdfa-82e2-4da7-92df-ae5f0f60090b/use-parameterised-sql-server-stored-procedure-in-excel-database-query?forum=isvvba

I managed to solve my problem.  Hopefully it will help you solve yours.

When you create a parameterized query in excel using a select statement like “Select * from mytable where xdate between ? and ?” two parameters are created:
“Parameter1”
“Parameter2”

When you change the command from a select statement to a stored procedure it should, but doesn’t, look for the same parameters.  After much trial and error I discovered excel is looking for:
“Parameter 1”
“Parameter 2”

Note the spaces.  While “Parameter1” is what was created with the excel query when using a select statement, “Parameter 1” is what excel is looking for when the command is referencing a stored procedure.

I found that creating the parameterized query as a stored procedure in the first place, instead of creating a select statement first and changing it later, seems to work just fine as long as the query is using the correct syntax.

That syntax is: “{CALL MyStoredProcedure (?,?)}”

So the steps on a new 2007 workbook:

1. go to Data tab, get external data from other sources, from Microsoft Query.
2. Select/Create DSN
3. Select a table and some columns to enable “next”
4. Click “next” until you can select “view data or edit query in Microsot Query”
5. Click Finish to open MS Query.
6. Click SQL button in toolbar
7. change SQL statement to “{CALL MyStoredProcedure (?,?)}” and click OK.
8. enter parameters.
9. close MS Query and tell Excel where you want the results.

You now have a parameterized SP as a Data connection in your workbook.  The parameters will have the spaces and will work properly.

There is probably a better way than this convoluted method, but at least it works.

  • Proposed as answer by wffOrdeith Saturday, September 26, 2009 5:23 PM
  • Marked as answer by danishaniModerator Saturday, October 04, 2014 3:59 PM

Saturday, September 26, 2009 12:26 AM

wffOrdeith

Wait until ActiveWorkbook.RefreshAll finishes – VBA

[Originally Posted By]: http://stackoverflow.com/questions/22083668/wait-until-activeworkbook-refreshall-finishes-vba

As commented:

‘~~> your code here before refresh

Activeworkbook.RefreshAll
DoEvents

‘~~> rest of your code here after refresh

What DoEvents does is like momentarily allowing Windows to take a break from Macro to process all pending events before returning to the macro.
So when you refresh your workbook and put DoEvents after, it will not resume the macro until the refresh is finished.

shareedit

I had the same issue, however DoEvents didn’t help me as my data connections had background-refresh enabled. Instead, using Wayne G. Dunn‘s answer as a jumping-off point, I created the following solution, which works just fine for me;

Sub Refresh_All_Data_Connections()

For Each objConnection In ThisWorkbook.Connections
‘Get current background-refresh value
bBackground = objConnection.OLEDBConnection.BackgroundQuery

‘Temporarily disable background-refresh
objConnection.OLEDBConnection.BackgroundQuery = False

‘Refresh this connection
objConnection.Refresh

‘Set background-refresh value back to original value
objConnection.OLEDBConnection.BackgroundQuery = bBackground
Next

MsgBox "Finished refreshing all data connections"

End Sub

The MsgBox is for testing only and can be removed once you’re happy the code waits.

Also, I prefer ThisWorkbook to ActiveWorkbook as I know it will target the workbook where the code resides, just in case focus changes. Nine times out of ten this won’t matter, but I like to err on the side of caution.

EDIT: Just saw your edit about using an xlConnectionTypeXMLMAP connection which does not have a BackgroundQuery option, sorry. I’ll leave the above for anyone (like me) looking for a way to refresh OLEDBConnection types.

shareedit

Running a SQL Stored Procedure from Excel (No VBA)

[Originally posted by]: http://datapigtechnologies.com/blog/index.php/running-a-sql-stored-procedure-from-excel/

We all know we can use MS Query to get data from a SQL server. Typically though, we pull from a Table or a View. Well in some organizations, the IT department wants all interaction with the server to be done through Stored Procedure. This adds a level of risk management and makes the DBAs feel better.

 

So today, I’ll show you how to easily make Excel run a Stored Procedure to get data.

Step 1: Data tab – > From Other Sources -> From SQL Server

 

 

Step 2: Enter Credentials. Your server name can be an IP address

 

 

Step 3: Choose any old table or view. Pick a small one because we’ll discard it later anyway.

 

 

Step 4: Excel will pop up the Import Data dialog box. Click Properties here (NOT THE OK BUTTON).

 

Step 5: Click on the Definition tab. There, change Command Type to SQL, and then enter your Stored Procedure name in the Command Text input.

 

 

Step 6: Excel complains about something….blah…blah…blah. Click Yes – (as in yes I know what I’m doing).

 

 

Step 7: Excel will activate the Import Data dialog box again. This time click OK to fire the Stored Procedure and return the results.

 

 

Step 8: Marvel at your results

 

 

Notes:

  1. Excel will fire the Stored Procedure each time you “Refresh”
  2. If you have to pass a parameter, you can enter it in the command text like this:

     

  3. If you have to pass dynamic parameters you’ll have to turn to VBA. See this post.
  4. I assume you can do this with ORACLE databases too.
  5. I’ve yet to test whether this will fire a Stored Procedure that doesn’t return data. In other words, Stored Procedures that perform Insert, Update or Delete actions. I assume that if you can, there is the possibility of updating SQL from Excel through a simple connection. Pretty cool.

Running Advanced SQL Stored Procedures from Excel

[Originally posted by]: http://datapigtechnologies.com/blog/index.php/running-advanced-sql-stored-procedures-from-excel/

A while back, I posted two articles that showed you how to run Stored Procedures from Excel.

In “Running a SQL Stored Procedure from Excel“, I showed you how to fire a simple SQL Stored Procedure that returns a simple dataset. In “Running a SQL Stored Procedure from Excel with Dynamic Parameters“, I showed you how to pass parameters to a SQL Stored Procedure so you can filter the returned dataset.

.

In both of those examples, the common denominator is that the Stored Procedure can only contain a select statement. For example, ‘Select * from Market Sales‘.

.

But in some cases, your Stored Procedures may need to contain advanced functionality such as; creating temp tables, deleting data, updating data, etc. In these cases, you can’t simply fire the Stored Procedure from Excel. You’ll need to adjust the Procedure so that the results will return to Excel.

.

The Cause of the Problem

When you create a new Stored Procedure in SQL Server, you will automatically get a line that reads SET NOCOUNT ON. This one line of code, put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed. This is intended to improve performance by suppressing unnecessary messages to the client.

.

However, this will also suppress the returning of data to Excel, because SET NOCOUNT ONtells SQL Server that there is no need for this information to be passed back to the client.

.

The Fix

In this example, procedure, I’m creating two Temp tables in the procedure then creating a select statement which returns data from the two created tables. In order to be able to return the data to Excel with this Stored Procedure, I will need to add a line at the end of the Procedure that reads SET NOCOUNT OFF.

.

.

By wrapping your Stored Procedures with SET NOCOUNT ON … SET NOCOUNT OFF, you can fire all kinds of Stored Procedures from Excel:

  • Stored Procedures that create and use Temp Tables
  • Stored Procedures that contain Delete and Update Statements
  • Stored Procedures that contain Truncate and Insert Statements

.

In short, you can use this trick in conjunction with the other tricks you learned (in the other articles mentioned above) to trigger virtually any SQL Stored Procedure directly from Excel!