Information Technology Tips

Save Excel Sheet as UTF-8 CSV file The biggest problem of Excel is that Excel does not support saving file as UTF-8 CSV format. I have googled and tried to find the solution for saving excel as UTF-8 CSV but all of them requires 2 steps - 1) saving Excel file as Unicode csv 2) Open it by text editor and save it again as UTF-8 CSV. If you are a software developer, it is easy to write Java or C# program to manipulate Excel. But it means people (non developer users) need to install or launch the application. So I have wrriten Excel VBA addin for saving Excel sheet as UTF-8 CSV file so that we can save UTF-8 excel files only using Excel. Code for saving UTF-8 CSV Okie!, here is my solution! Sme notes for the code: The code saves an active sheet to UTF-8 CSV file in temp folder. The error handring part is not robust. I have only wrriten minimum error handling. You might need to add your custome error handring. If you are okay to add BOM on the file, please remove the corresp

Public Sub GenerateOutlookEmail() Dim Outlook As Outlook.Application Dim message As Outlook.MailItem Set Outlook = CreateObject("Outlook.Application") Set message = Outlook.CreateItem(0)  With message 　.Subject = "Test Mail" 　.To = "foobar@foobar.com" 　.HTMLBody = "Good morning life" 　.Display End With End Sub

WinHttp.WinHttpRequestオブジェクトを使って使ってHttp PostでByte配列のデータを送信するExcel VBAプログラムです。 WinHttp.WinHttpRequestを使う際には、 1. ExcelのMicrosoft Visual Basic エディタのメニューバーから「ツール->参照設定」とたどる。 2. 表示されたダイアログからMicrosoft WinHTTP Serviceにチェックを入れる。 という手順が必要です。 Byte配列をPOST Private Const BOUNDARY As String = "SOMETHING" Private Function httpPostServletByte(url As String, data() As Byte) As Byte() On Error GoTo e Dim WinHttpReq As WinHttp.WinHttpRequest If (WinHttpReq Is Nothing) Then Set WinHttpReq = New WinHttpRequest End If WinHttpReq.Open "POST", url, False WinHttpReq.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded; boundary=" & BOUNDARY WinHttpReq.send data Dim response() As Byte: response = WinHttpReq.ResponseBody httpPostServletByte = response Set WinHttpReq = Nothing Exit Function e: Set WinHttpReq = Nothing Debug.Print "httpPost Error:" & Err.

ADODB.streamオブジェクトを使って文字列をByte配列に変換するコードのサンプルです。 ExcelVBAでADODB.streamを使う際には、 1. ExcelのMicrosoft Visual Basic エディタのメニューバーから「ツール->参照設定」とたどる。 2. 表示されたダイアログからMicrosoft ActiveX Data Objectsにチェックを入れる。 という手順が必要です。 文字列からByte配列へ Private Function ADOS_EncodeStringToByte(ByVal cset As String, ByRef strUni As String) As Byte() On Error GoTo e Dim objStm As ADODB.stream: Set objStm = New ADODB.stream objStm.Mode = adModeReadWrite objStm.Open objStm.Type = adTypeText objStm.Charset = cset objStm.WriteText strUni objStm.Position = 0 objStm.Type = adTypeBinary Select Case UCase(cset) Case "UNICODE", "UTF-16" objStm.Position = 2 Case "UTF-8" objStm.Position = 3 End Select ADOS_EncodeStringToByte = objStm.Read() objStm.Close Set objStm = Nothing Exit Function e: Debug.Print "Error occurred while encoding characters" & Err.Description If objStm Is No

Office TANAKA : 基礎的なことは網羅されています。 特に Excel(エクセル) VBA入門：配列の利用 のページが参考になりました。 よねさんのWordとExcelの小部屋 Anthony's VBA Page SheetやRangeに関する記事が役に立ちました