4388

Convert to Text Format In Excel

Question:

I'm extracting some data from AS400 using excel macro. In the AS400, this particular column (Ref), shows 20100729000078154 but when I extracted it to excel, it will be 2.01007E+16. I need to hv 20100729000078154 as my final output. This is the macro that I used to extract the info from AS400 :-

Sub Extract() Dim StrSQl As String FromA = Format(Sheet1.Range("B3")) FromB = Format(Sheet1.Range("B4")) FromC = Format(Sheet1.Range("B5")) FromD = Format(Sheet1.Range("B6")) StrSQl = "select Cno,Itno,Ref from test " StrSQl = StrSQl & " where Cno= " & FromA & " and Itno like " & FromB & " and " StrSQl = StrSQl & " Ref >= " & FromC & " and Ref <= " & FromD & " " StrSQl = StrSQl & " order by Cno " con = "Provider=IBMDA400;Data Source=xxx.xxx.xxx.xxx;User Id=yyyyy;Password=zzzzz" Set Db = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.recordset") Db.ConnectionString = con Db.Open rs.Open StrSQl, Db, 3, 3 Sheet2.Cells(1, 1).CopyFromRecordset rs rs.Close Set rs = Nothing Set cn = Nothing End Sub

Answer1:

You can prefix an apostrophe if you're just wanting the column to display as text, something like (assuming a single apostrophe literal can be expressed as '' in iSeries SQL)...

StrSQl = "select Cno,Itno,CONCAT('''',Ref) as Ref from test "

Recommend

  • XLSXWriter Apply Formula Across Column With Dynamic Cell Reference
  • Excel vba- Get code of change worksheet event to work for newly copied range
  • formulas excel charts how to auto dynamic select last 5 value?
  • including classes with a wsc file
  • Excel loop VBA Macro copy cells to new sheet
  • How do you walk the solution hierarchy?
  • vba paste not working
  • No Desired Output, Probable Cause is an Overload
  • python pandas: how to group by and count with a condition for every value in a column?
  • Indirect call of a column from different sheet
  • jqPlot Styling - How to remove Y axis line?
  • Why does Range.Address() return the address of the first cell when chained?
  • Button click find empty cell
  • How to avoid the redefinition of a function (PEP8 error F811)
  • Range value not shown in locals window in Excel VBA
  • Excel VBA Copying to Blank Row
  • concat two pandas DataFrame with same column/index into one DataFrame
  • git status vs fetch (or pull): How to know what “up-to-date” means (without changing anything)
  • Excel 2010 search for text in IF function - separate cell data
  • Why is 0x20 subtracted from the stack pointer in the prologue of this function's code?
  • Regular Expression to identify a Guid followed by a number
  • Get count of zeros in an integer using MySQL
  • signal 11 (SIGSEGV), fault addr 00000000 comes in my app
  • How to sort factor levels based on another category?
  • Excel: how to create a number in a column according to a text value in other column?
  • Aggregating based on “near” row values
  • Omit template arguments when create an instance of template class from another instance of template
  • Does TMonitor.GetBoundsRect have an access violation bug in Delphi 2007 triggered by VNC?
  • Pop up charts in VBA Excel
  • Maven, ActionBarSherlock v4 and Roboguice v2 - How do I get it to build?
  • How do I pass worksheet and ranges as variables?
  • OleDbDataAdapter not filling all of the rows
  • Merge the values of multiple elements and take the average of the attribute field
  • How do I pip install from Google Code?
  • How to handle AllServersUnavailable Exception
  • VBA Convert delimiter text file to Excel
  • Properly structure and highlight a GtkPopoverMenu using PyGObject
  • Is it possible to post an object from jquery to bottle.py?
  • Reading document lines to the user (python)
  • Python/Django TangoWithDjango Models and Databases