may the VBA be with you

Excel VBAとか業務自動化とか

一文字ずつ探して蛍光色つけるWordマクロ

はじめに

最近、ものすごく久々にWordのVBAをいじるお仕事がありました。
なので、ついでに「一文字ずつ探して蛍光色つけるWordマクロ」を作ってみました。(頼まれたものとはあんまり関係なかった)

できたもの

f:id:vba-belle-equipe:20210703172216g:plain
たすけてファインダー

こんな感じです。
指定した文字列を一つずつ探して行って、蛍光色をつけます。

コード

'sleepを使うための準備
#If Win64 Then
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Sub 一文字ずつ探して蛍光色つける()
    Dim str As String
    Dim strLen As Long
    str = "たすけて"       '対象の文字列を指定
    strLen = Len(str)
    
    '文書の蛍光色をすべてクリアする
    ActiveDocument.Range.HighlightColorIndex = wdNoHighlight
    
    Dim i As Long
    Dim idx As Long
    idx = 0
    
    '文字列を最初から1文字ずつ探していく
    For i = 1 To strLen
        Sleep (1000)    '指定したミリ秒実行を待つ
        idx = 蛍光ペンで色をつける(Mid(str, i, 1), idx)
        
        '見つからなかったらその時点で終了
        If idx = -1 Then
            Exit For
        End If
    Next
End Sub

'str : 対象の文字列
'start : 検索を始める場所(カーソル位置)
Function 蛍光ペンで色をつける(str As String, start As Long) As Long
    '検索開始位置を指定
    Selection.start = start
    Selection.End = start
    With Selection.Find
        .Text = str
        .MatchFuzzy = False
        .MatchWildcards = True
        .Execute
    End With
    
    Dim ans As Long    '指定した文字が見つかった場所を記録(functionの最後で返す)
    ans = Selection.End
    If start = ans Then
        ans = -1
        Debug.Print str & "_見つからない"
    Else
        Debug.Print str & "_" & Selection.start
        
        '蛍光色つける
        ActiveDocument.Range(Selection.start, Selection.End).HighlightColorIndex = wdRed
    End If
    
    蛍光ペンで色をつける = ans
End Function

コードの説明など

Selection.Findが検索する部分ですが、Selection.start でカーソルを移動させておくと、そこ以降から検索できるので、今回はそのようにしてみました。
つまり、色をつけた場所より前で、次の文字が見つかることはありません。

蛍光色の一覧はこちらです(色見えないけど)
docs.microsoft.com

一瞬で全て見つけて色を変えられてもつまらない(?)ので、sleepを使ってみました。

感想

普段はWordのVBAを触らないどころか、Wordを開くこともほぼ無いんですが、蛍光色をつけられるというのが面白かったです。
WordのVBAも使えると、なんか色々便利になりそうですね(あんまり思いつかないけど)

おまけ

暇な場合は、かの名作「はしょれメロス」が、ちゃんとはしょっているかの確認とかもできます。

f:id:vba-belle-equipe:20210704145206g:plain
はしょれメロス確認中

Yahoo!ニュースの見出しをChrome拡張とGASでいじってみる

はじめに

Chrome拡張って面白いですよね。
こちらの記事でGASとの連携もできそうだということがわかり、思いついたのが「Googleシートの値をYahoo!ニュースの見出しに表示できたら面白いかも!」でした。
qiita.com
思いついてしまったので、実装してみます。

必要なもの

見出しが8個あるので、googleシートのA1~A8セルに表示したいものを入れておきます
f:id:vba-belle-equipe:20210606141034p:plain

GAS側

doGetだけ作って、Webアプリとして公開しておきます

function doGet(e){
  
  //値をシートから取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  const range = sheet.getRange(1,1,8,1);    //A1~A8までのrangeを指定
  const values = range.getValues();  
  let res = {};  //chrome拡張側に渡すオブジェクト
  for(let i = 0; i < 8; i++){
    let propName = "str" + (i + 1);
    res[propName] = values[i][0];
  }

  // エクステンションにレスポンスを返す
  const output = ContentService.createTextOutput();
  output.setMimeType(ContentService.MimeType.JSON);
  output.setContent(JSON.stringify(res));
  return output;
}

Chrome拡張側

manifest.json
background.js
content.js

の3つのファイルを同じフォルダに置いて、Chrome拡張機能として読み込ませます

manifest.json
{
  "manifest_version": 2,
  "name": "yahooニュース見出し変える",
  "version": "1.0",
  "permissions": [
    "https://script.google.com/*"
  ],
  "content_scripts": [
    {
      "matches": [
        "https://www.yahoo.co.jp/*"
      ],
      "js": [
        "content.js"
      ]
    }
  ],
  "background": {
    "scripts": [
      "background.js"
    ],
    "presistent": false
  }
}
background.js
chrome.runtime.onMessage.addListener(
  function (request, sender, callback) {
    console.log(`バックグラウンドで受け取ったもの: ${request.message}`);

    // 「ウェブアプリ」としてデプロイしてるGASのURL
    const gasUrl = [自分のURLを入れる];

    fetch(gasUrl)
    .then(response => {
      return response.text();
    })
    .then(json => {
      console.log(`GASからのレスポンス: ${json}`);
      callback(JSON.parse(json));
    });

    // 非同期を同期的に扱うためのtrue
    return true;
  }
);
content.js
console.log("content.js開始");

chrome.runtime.sendMessage({
  message: ""
}, response => {

  console.log(`backgroundからの戻り値: ${JSON.stringify(response)}`);

  //aria-label属性があるタグをfor文で回して
  //値が「主要 ニュース」となっているdivを対象にする
  let tags = document.querySelectorAll('[aria-label]');
  for (let i = 0; tags.length; i++){
    let str = tags[i].getAttribute("aria-label");
    if(str == "主要 ニュース"){
      let spans = tags[i].getElementsByTagName('span');
      let cnt = 0;

      //見出しっぽいspanを文字数や除外文字列でなんとなく指定する
      for (let j = 0; spans.length; j++) {
        let buf = spans[j].textContent;
        if(buf.length > 10 && buf.slice( -2 )!="更新"){
          cnt++;
          let propName = "str" + cnt;
          spans[j].textContent = response[propName];
          //8個セットできたら抜ける
          if(cnt >= 8){
            break;
          }
        }
      }
      break;
    }    
  }
});

結果

f:id:vba-belle-equipe:20210606141128p:plain
うまくいきました。

念のため言いますが、世の中の、他の人が見ているYahoo!ニュースの見出しは変わりません。

解説など

基本的な作りはほぼお手本の通りなので、リンク先をご参照ください。
qiita.com

見出しの文字列データはオブジェクトのキー(str1,str2....)に値(A1, A2... のセルの値)をセットして渡しています。

注意事項など

  • Chrome以外に、Edgeでも一応動くみたいです
  • 試してみる場合は、自己責任でお願いします(特に、他の人が該当ブラウザを使う場合は気をつけてください)
  • サイトのタグ構成とかは変わると思われるので、いつまでも動くものではないです

GASで数値文字参照を読めるようにする練習

とある場所で「とある配送業者の配送状況を追跡番号から取得してgoogleシートに表示する的なシステム」の公開依頼が予算1,000円~3,000円で発注されてて、提案してる人がいて「すげーな」と思いました。

f:id:vba-belle-equipe:20210505160800j:plain
やったぜ

自分ではその値段で作る気はしないものの気になって調べて、がんばって取得した配送状況が

&#37197;&#36948;&#23436;&#20102;

的な数値文字参照
特殊な訓練を受けていない自分には読めないため、GASでデコードできるようにしてみました。

function test(){
  let str = '&#37197;&#36948;&#23436;&#20102;';
  console.log(numRefToString(str));  //配達完了
}

//数値文字参照を通常文字列に
function numRefToString(str){  
  let re = str.match(/&#[0-9a-f]+;/g);
  let len = re.length;

  let result = '';
  for (let i = 0; i < len; i++)
  {
    console.log(re[i]);
    let tmp = re[i].replace('&#', '').replace(';','');
    tmp = String.fromCharCode(tmp);
    result += tmp;
  }
  return result;
}

説明

正規表現でマッチしたものを全て配列にいれて、数字だけにして、あとは「String.fromCharCode」にお任せというだけのものです。
ダメなケースがあったらごめんなさい。

[GAS] Googleドライブのフォルダ内ファイルの一覧作成

はじめに

f:id:vba-belle-equipe:20191001174609j:plain
000460.jpgさんです

photoshopvip.net

いやー、すごい時代ですね。
というわけで、意味なくgenerated.photosの画像を使わせてもらいました。

generated.photos

最近気になる

www.glideapps.com

glideというサービスです。
自分のGoogleシートのデータを使って簡単にスマホアプリが作れてしまうという。
いやー、すごい時代ですね。(もういいよ)

そんなわけで、たまにはGoogle Apps Scriptネタでも。

Googleドライブのフォルダ内ファイルの一覧作成

Googleシートのアクティブシートに書き出します。

コードです。

var SpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
var Sheet = SpreadSheet.getActiveSheet();
var ResultAry = [];

//対象とするフォルダ
var TargetFolder = DriveApp.getFolderById("*******適宜*******");

//フォルダ内にあるファイルの名前とURLを列挙する
function listUpFilesData(){
  var targetFiles = TargetFolder.getFiles();
  var addAry = [1];
  
  //タイトル行
  addAry[0] = 'ファイル名';
  addAry[1] = 'URL';
  ResultAry.push(addAry);  
 
  //ファイルの数だけ繰り返し
  while(targetFiles.hasNext()){
    addAry = [1];
    var file = targetFiles.next();
    addAry[0] = file.getName();
    addAry[1] = file.getUrl();
    ResultAry.push(addAry);    
  }
  
  var len = ResultAry.length;
  Sheet.clear();
  var range = Sheet.getRange(1,1,len,2);
  range.setValues(ResultAry);
}

ポイント

GASでGoogleシートを操作する時は
値を読む「getValue(s)」と値を入力する「setValue(s)」
をよく使うことになります。

が、軽い気持ちでfor文で回しながら使うととても時間がかかる。
ということで、シートに一気に吐き出す配列を使って処理をしてます。

これってExcelVBAで複数のセルの値をVariantに入れるのとそっくりですね!

結果

f:id:vba-belle-equipe:20191001182052p:plain
結果

こんな感じです。
まあ素っ気ない!

VBAでこんなことできます_その3 指定した銘柄の時系列株価データを取得するマクロ

どうもinageです。
VBAでどんなことできるかちょちょっと見せる用サンプル第3弾です。

「インターネット上のデータを自動で取得できるんです!」
と言って見せると
「え、そんなことできるんだ」と驚いてくれる場合と
「まあ、そりゃできるでしょ」顔をされる場合があります。

まあ、なんでもそうなんですけどね・・・。

f:id:vba-belle-equipe:20190909112033g:plain
web情報取得マクロ

どんなマクロ

4桁の証券コードを指定してボタンを押すと、時系列の株価データを表形式で取得します。

使い方

1)証券コード(4桁の数字)を入力する
2)株価取得ボタンを押す



以下、同じようにどっかから表形式のデータを持ってきたい場合の
参考までに、流れを書きたいと思います。

ただ動かしたい場合は「ファイルのダウンロード」まで飛んでください。

VBAでのIE制御について

こちらのサイトが超詳しく、勉強になりますので是非ともご覧ください。
www.vba-ie.net

ネット上のデータを取得するマクロができるまで

  • データを取得したいページをなんとなく探す
  • パラメータが必要か調べる
  • Webページの構造を調べる
  • VBAのコードを書いてみる

みたいな流れになります。

データを取得したいページ

f:id:vba-belle-equipe:20190909093408p:plain
Yahoo! ファイナンスの銘柄別時系列ページ

今回はこの、Yahoo! ファイナンスの時系列株価情報ページの表を
そのままExcelに持ってくることを考えるとします。

パラメータ?

アドレスバーを見ると「https://stocks.finance.yahoo.co.jp/stocks/history/?code=6460.T」となっています。

ここで「?code=6460.T」というのがパラメータの部分で、

「code」というパラメータに値「6460.T」を設定してね

ということです。

試しに他の銘柄を指定すると、「6460.T」の部分が変わることがわかります。

「.T」の部分はつけなくても良いみたいなので

https://stocks.finance.yahoo.co.jp/stocks/history/?code=銘柄コード

という形でアドレスを指定すれば良さそう、ということがわかります。

パラメータをつけたアドレス生成のコード(抜粋)
Dim codeStr As String
codeStr = Range("B2")
objIE.navigate "https://stocks.finance.yahoo.co.jp/stocks/history/?code=" & codeStr

この部分ですね。(B2セルに4桁のコードが入力される前提)

Webページの構造を調べる

ブラウザでホームページを見ている時に、おもむろにF12キーを押すと、
「開発者ツール」というものが開きます。
(ブラウザによって名前とか見た目は違います)

それをいい感じで使うと、ターゲットとなる表が
どのように構成されているかわかります。(説明放棄)

f:id:vba-belle-equipe:20190909095921p:plain
開発者ツールでWebページの構造を調べているところ


この場合、チェックするのは

・対象となる部分が<table>タグで出来ている
・見出しの行は<th>タグで、7つ(7列分)ある
・データ部分は<td>タグで、1行につき7つ(7列分)ある

というところです。

その情報を元に、どんなタグのデータを取得して、どこで折り返すか
を指定している部分が以下です。

    Const NumOfColumns As Long = 7    '一行の列数
    Const StartCol As Long = 5
    Const StartRow As Long = 4
    
    '対象テーブルの指定
    Set objTable = objIE.document.getElementsByTagName("table")(1)
    Set objTHs = objTable.getElementsByTagName("th")
    crRow = StartRow
    crCol = StartCol - 1
    
    '項目行の転記
    For Each obj In objTHs
        crCol = crCol + 1
        Cells(crRow, crCol) = obj.outerText
    Next
    
    Set objTDs = objTable.getElementsByTagName("td")
    cnt = 0
    crRow = StartRow + 1
    crCol = StartCol - 1
    
    'データ部分の転記
    For Each obj In objTDs
        cnt = cnt + 1
        crCol = crCol + 1
        Cells(crRow, crCol) = obj.outerText
        '既定の列数に達したら次の行に移る
        If cnt Mod NumOfColumns = 0 Then
            crCol = StartCol - 1
            crRow = crRow + 1
        End If
    Next

テーブルの指定

「objIE.document.getElementsByTagName("table")」は
tableタグのオブジェクトを配列として取得します。

今回はそれに(1)をつけることで、ソースコードに現れるtableのうち、
2番目のものを指定しています。1番目なら("table").(0) ですね。

f:id:vba-belle-equipe:20190909110723p:plain
なんで("table")(1)なの

画像のピンク枠が、1個目のテーブルです。

対象となるテーブルが何個目なのかは、まあ、htmlのソースを検索すれば
ある程度わかります。

仮に間違っても世界が終わるわけではないので、落ち着いて数字を変えながら
テストしていきましょう。

ファイルのダウンロード

web情報取得01.xlsm - Google ドライブ

上記リンク先からダウンロードしてください。

できなかったらゴメンナサイ。

諸注意

Windowsで動作します
・デモ用なので、エラー処理とかちゃんとやってません
・自由に使っていただいて構いませんが、自己責任でお願いします
・日光に当てたり、水を与えたり、深夜12時以降に食べ物をあげたりしないでください