Change Numeric Field to Text for Match

I receive a request from a client this week wanting a formula to change a column of numeric data to text in order to use VLOOKUP to compare his data to data provided by one of his vendors.  He knew that he could enter an apostrophe in the first position of the cell ahead of the data but did not have time to do that for  hundreds of cells.  Actually this is something I have to do frequently so helping my client out was I already knew how to answer.

Assuming the data to be changed is in Column A and begins in Row 2 insert a column to the right and in Cell B2 enter =TEXT(A1,0).  Copy the formula and paste down to the Row of the last populated in Column A.  Select the populated cells in Column B, Copy and Paste special into Column A as Values.  Remove Column B and you're set to go.

Got an Excel question?  Drop me an email or give me a call on my cell.

Refresh MS Query Without Selection

For years I've used macros to refresh queries in workbooks.  I originally recorded a macro to accomplish the refresh and it worked as expected but in order to refresh the data if the output of the query existed on a different sheet it was necessary to include in the macro a select of the worksheet, then select a cell in the query output.  With the sheet/cell selected the query is run and when the data has been refreshed the original worksheet is selected.  Here's an example of the macro:

Sub refresh1()

    Sheets("Sheet1").Select
    Range("A2").Select
    Selection.ListObject.QueryTable.refresh BackgroundQuery:=False
    Sheets("Sheet3").Select

End Sub

While this works fine and runs so quickly the selection of another worksheet and then selecting the original worksheet is usually not noticed by the user, however, it seemed to me there should be a better way of refreshing the data.

After a bit of Google searching I found that the above code could be replaced with this:

Sub refresh2()

    Sheets("Sheet1").Range("A2").ListObject.QueryTable.refresh BackgroundQuery:=False

    

End Sub

While this was obviously an improved solution I felt there could be a better way.  So back to Google an I found this:

Sub refresh3()

    ActiveWorkbook.Connections("BranchData").refresh

End Sub

In this code "BranchData" is the connection name of the query.

This is the best solution I've found to date.

Save As .xlsm Only

With the advent of Excel 2007 Microsoft introduced new file extensions for Excel.  Instead of having only .xls for all workbooks we now have .xlsb for Excel Binary Workbooks, .xlsm for Excel Macro-Enabled Workbooks, .xlsx for Excel Workbooks and a multitude of other formats from Templates to Text.  One of the potential issues of these different formats when using Macros is the loss of the Macros when the workbook is not saved as a Macro-Enabled Workbook.  Even though the User receives a message that the Macros will be removed the message is often ignored resulting in a copy of the workbook minus the original Macros.

One solution to this issue is to prevent users from saving your workbook with any file extension other than .xlsm.  The code below will prevent this.

 
This code has to be in the Workbook module rather than a regular module.  To access the Workbook module click the Developer tab and then click the Visual Basic icon to display display  the Visual Basic editor.

Right click on ThisWorkbook and select View Code

Enter the code in the the Worksheet Module, close the editor and save the workbook.

If you have questions regarding this let me know and I'd be happy to help if I can.

Date Time Stamp

Received a request from my son last week for a way to enter a date/time stamp in a cell when any value is entered in another cell.  Also, if the entered value is removed, remove the time stamp.  He's using Excel to track specific information regarding projects assigned to him.  When he enters a Project name in Column A he wants the date/time stamp in Column C of the same row.  When the Resolution of the Project is entered in Column E a date/time stamp is placed in Column F of the same row.

This was accomplished by using the Worksheet_Change event.  You can do a Google search on "Worksheet_Change" more detailed information about this event but suffice to say that it allows you to do something when a cell is changed on a worksheet.  The only way I've previously used this event was to run a macro but in this case we're simply wanting to insert a value into a specific cell.  

Here's the basic layout of the worksheet that needs the date/time stamp in columns C and F.

The following code is written to the Private Module of the Worksheet where it will be used rather than a general Module in the workbook.  The easiest way to do this is to right click on the worksheet tab and select View Code.  Paste this code into the Private Module.

There may be (and probably is) a better way to accomplish this but this how I did it.  If you know of a better or more efficient way to accomplish this I would appreciate hearing from you.