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.