-
-
Save Implem/dad3228e00b79652ebb4 to your computer and use it in GitHub Desktop.
Excelにソースコードの雛形など複数行データを効率的に格納/編集したい ref: http://qiita.com/Implem/items/ad6dad677b3aa84eef5b
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Option Explicit | |
Const CodeColumnNo As Integer = 2 | |
Public Sub EditCode() | |
Application.ScreenUpdating = False | |
Dim FN, Folder As String | |
Dim fso, TextFile, wsh As Object | |
Set fso = CreateObject("Scripting.FileSystemObject") | |
Set wsh = CreateObject("WScript.Shell") | |
'--- 適当なファイル名を作成 | |
FN = Cells(ActiveCell.Row, 1) & "_" & Mid$(CreateObject("Scriptlet.TypeLib").GUID, 2, 36) & ".txt" | |
Folder = ThisWorkbook.Path & "\Temp\" | |
If fso.FolderExists(Folder) = False Then | |
fso.CreateFolder Folder | |
End If | |
'--- セルに格納されたソースコードをテキストファイルに出力 | |
With fso.CreateTextFile(Folder & FN) | |
.Write Replace(Cells(ActiveCell.Row, CodeColumnNo), vbLf, vbCrLf) | |
.Close | |
End With | |
'--- テキストファイルを開いて閉じられるまで待機 | |
wsh.Run """" & Folder & FN & """", , True | |
'--- テキストファイルに保存された変更内容をセルに書き戻す | |
With fso.OpenTextFile(Folder & FN) | |
Set TextFile = fso.OpenTextFile(Folder & FN) | |
Cells(ActiveCell.Row, CodeColumnNo) = Replace(TextFile.ReadAll, vbCrLf, vbLf) | |
End With | |
SetCodeColumnStyle ActiveCell.Row | |
Application.ScreenUpdating = False | |
End Sub | |
Public Sub SetCodeColumnStyle(i_Row As Integer) | |
'--- 長いコードは鬱陶しいので縮小モードにする | |
With Cells(i_Row, CodeColumnNo) | |
If Cells(i_Row, CodeColumnNo) <> "" Then | |
.HorizontalAlignment = xlGeneral | |
.VerticalAlignment = xlCenter | |
.WrapText = False | |
.Orientation = 0 | |
.AddIndent = False | |
.IndentLevel = 0 | |
.ShrinkToFit = True | |
.ReadingOrder = xlContext | |
.MergeCells = False | |
End If | |
End With | |
End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment