Excel VBA - Value vs Text


Value vs Text


Sheets(1).Cells(6, 3).Value will give the raw value in the cell.

Sheets(1).Cells(6, 3).Text will give what you see in the cell.

Comments

Post a Comment

Popular posts from this blog

Install zeromq on Yosemite with php PECL

brew install zeromq Building php extension via PEAR sudo pecl install zmq-beta You will have to add the extension to your php.ini with this line: extension=zmq.so Find the php.ini File Location from the Command Line php -i | grep php.ini You can, of course, always put a new php file in your web server root directory and add in the following: phpinfo();
Read more

Reading a unicode Excel file in PHP

Image
It's easy to save an Excel file as CSV and read it in PHP with the fgetcsv function but this may not work so well if the file contains non-English characters. Excel uses a non-standard character encoding for csv files. You can save an Excel file as 'unicode' text however there are several unicode systems - Windows uses UTF-16, and PHP uses UTF-8. To open the 'unicode text' file in PHP you have to convert it, in addition you may want to be able to open UTF-8 files that may be created by other systems. PHP has an encoding detection function - but it can't detect UTF-16. I've solved the problem with the following function which detects from several encodings, adds an appropriate filter, and returns a filehandle which reads as UTF-8. function fopen_utf8 ( $filename ){ $encoding = '' ; $handle = fopen ( $filename , 'r' ); $bom = fread ( $handle , 2 ); // fclose($handle); rewind ( $handle ); if( $bom === chr ( 0xff ). chr ( 0xfe...
Read more

How to create unicode files using VBA?

Image
Using the Scripting Runtime library you can create a unicode text file and write to it like: Sub test() 'set reference to Microsoft Scripting Runtime lib Dim strFile As String, strRange1 As String, strRange2 As String Dim fso As FileSystemObject Dim txtStrm As TextStream strFile = "Y:\Work\MyFile.htm" 'amend as appropriate strRange1 = Range("A1").Value strRange2 = Range("A2").Value Set fso = New FileSystemObject Set txtStrm = fso.CreateTextFile(strFile, Overwrite:=True, Unicode:=True) With txtStrm .WriteLine strRange1 .WriteLine strRange2 .Close End With End Sub You must set a reference within the VBE via Tools>References to the Microsoft Scripting Runtime lib.
Read more