引っ越すためついに本気で書籍リストを作っていると、どれほど少しでも省力化したくなり、しつこくバージョンアップ。
ISBNのない外国の本も検索したい
ISBNのない本が思ったより多かった。フランス語のアクサンとか入力したくない。ISBNでの検索はamazon.com, amazon.frなど検索先を切り替えているように、タイトル等での検索も各国サイトを利用したい。最初は「jpで見つからなければcom、見つからなければfr、、、」と思ったけど、だいぶ待たされちゃうし、その本のデータをどこで探したいかは決まってるので、それぞれ別のプロシージャを定義して実行できるようにした。
Public Sub searchBookInfoFromAmazonFr(dummy As Integer) searchBookInfo (amazonFr) End Sub
さて、クイックアクセスツールバーに登録するのだけど、用意されてるアイコンじゃわかりづらい。各国の旗がいいんじゃないか。と思ってからが長かった・・・が、まとめれば:
- 旗アイコンはここで入手。
- Ribbon Editor でリボンに自前のタブ・ボタンを登録
- 対象のExcelファイルを開く
- 右のDocument Explorerペインで「CustomUI Parts」を追加
- 表示される編集ペインのImageタブを開き、好きな画像を追加(From File System)
- Codeタブに下記Xmlを書いて保存、終了
- Excelを開くとカスタム・ボタンが登録されているので、ふつうにクイックアクセスツールバーにも登録
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"> <ribbon startFromScratch="false"> <tabs> <tab id="customMacro" label="custom macro"> <group id="customGroup" label="Search from..."> <button id="button1" label="Com" image="usa" size="normal" onAction="'searchBookInfoFromAmazonCom 1'" /> <button id="button2" label="France" image="France" size="normal" onAction="'searchBookInfoFromAmazonFr 1'" /> <button id="button3" label="Spain" image="Spain" size="normal" onAction="'searchBookInfoFromAmazonEs 1'" /> </group> </tab> </tabs> </ribbon> </customUI>
まとめればそれだけなんだけどボタンを押すとマクロが2回実行されるという、どうも昔からあるらしい謎仕様にはまった。
onAction="'foo()'"→「実行できません。このブックでマクロが使用できないか、またはすべてのマクロが無効になっている可能性があります。」
onAction="foo()"→ 2回実行され、2回目は意味不明なエラー。
onAction="foo" onAction="'foo'"→「引数の数が一致していません。または不正なプロパティを指定しています。」
やっと動いたのは
- プロシージャにはダミー引数を定義する
- onAction にはシングルクォート・括弧なしで書く
onAction="'foo arg'"だいぶ気持ち悪いけどやむなし。
ピッとしたらパッと入力させたい
バーコードリーダーでISBNをピッピッと軽快に入力しても、マクロ実行のためにEnter押さなきゃいけなかった。バーコードリーダーを置いて、キーボードに手を持っていくのが地味にめんどくさい。こんなフォームを作り、ISBNが入力され次第、データを取得してシートに書き出し、次の入力を待つようにした。快適なり。
裏では10桁ないし13桁入力されるまでChangeイベントをスルーするとか、Changeイベントは描画更新前に起きるらしく最後の1桁が表示されないとか、雑な作りだけども、それも自分用マクロの楽しいところ。
もっと適したイベントがあれば知りたい・・・
Private Sub isbn_Change() If Len(isbn.Value) < 3 Then Exit Sub ElseIf (Left(isbn.Value, 3) = "978") And (Len(isbn.Value) < 13) Then Exit Sub ElseIf (Len(isbn.Value) < 10) Then Exit Sub End If Cells(ActiveCell.row, 1).Value = isbn.Value Application.Cursor = xlWait main.setBookInfo Cells(ActiveCell.row + 1, 1).Select isbn.Value = "" Application.Cursor = xlDefault isbn.SetFocus End Sub
初めまして。
返信削除エクセルでできる蔵書リストを探しておりましたら、
こちらに辿り着きました。
とても便利で使わせていただいております。
先日、CDやDVDもたくさんあったので管理できると
嬉しいなと思い自分で試そう
としたのですが、さっぱりわかりませんでした。
CDやDVDは無理なのでしょうか、もし出来たら本当に嬉しいです。
このままでも十分素晴らしいのですが、もし簡単に出来たらと思いコメント
させていただきました。
蔵書の管理が便利なりうれしいです。
本当にありがとうございました。
コメント&ご利用ありがとうございます。
返信削除CDやDVDも、リクエスト・パラメータを以下のように変えれば検索できます。
私のマクロでいうと、helperモジュールのgetSignedUrlFor()でパラメータを連結している部分をいじります。
Signature以外のパラメータはアルファベット順に連結しなければならないのが、ちょっとした落とし穴です。
■バーコード値で特定の商品を検索する場合(Operation=ItemLookup)、
・ItemIdにバーコード値を指定
・IdType=EANを追加
・SearchIndexを追加(値はMusic、DVD、Videoなど。Allでも可)
■歌手名やタイトルで検索する場合(Operation=ItemSearch)
・SearchIndexを追加(Music、DVD、Videoなど)
・指定したSearchIndexで有効な絞り込みパラメータを使う
(MusicならArtist、Performer、VideoならActor、Directorなど。
SearchIndexをAllにしてしまうと、Keywordsしか使えません)
-------
また、返ってくるXMLの属性は、検索されたインデックスが何かによって変わるので、値を取り出す処理もそれに応じて変える必要があります。
マクロでいうと helper.getAttributeMaps() の
map.Add "title", attributesNode.SelectSingleNode("Title").Text
などを適切な属性に置き換えます。
-------
、、、という感じで、CDやDVDを検索することはできるのですが、
既存のマクロに統合する良い方法を思いつけません、、、。
とりあえず、DVDを検索するサンプルを
https://github.com/anarchiStraw/excel/tree/forDVD
に置きました。
https://github.com/anarchiStraw/excel/commit/13716a04704b54454153d5ba7d1e1808a62de044
で変更箇所を見られます。また何かありましたらコメントください。
非常に参考になるマクロを公開して頂きありがとうございます。
返信削除ISBNとJANで検索したいのでDVDを検索するサンプルを利用してみました。
セルにJAN&ISBN入力して「AmazonデータをISBNで検索」をクリックすると
情報取得できましたが、連続実行でJANを入力すると10桁に変換されてエラーになります。
VBAを見てもどの辺りを書きかえると良いのか分かりませんでした…
本/CD/DVD/ゲーム/ホビーの商品管理に使いたいたくて
私が必要としている機能は、
A列にISBNかJANを入力したら自動的に
B列に商品タイトル
C列にランキング
D列に価格
を表示するといった感じです。
キーワードからの検索や海外検索は使いませんし自動実行をMsgBoxの表示から入力するのではなく
自動実行を基本動作としてA列セルにJANを連続で入力できるようにしたいと考えており、
何かよい方法はないかと思いコメントさせて頂きました。
確認&お返事が遅くなってすみません。
返信削除1) A列に入力したら自動的に実行したい
下記のように、入力するワークシートのChangeイベントでデータ取得処理を呼べば実行できます。
注意点としてsetBookInfoは「選択中の行(Selection)」についてデータ取得しますので、ISBN・JANを入力したあと「Enter」ではなく「Ctrl+Enter」で入力値を確定してください。
---------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If (.Cells.Count = 1) Then
If (.Column = 1) And (.Value <> "") Then
main.setBookInfo
End If
End If
End With
End Sub
---------------------------------
あと、マクロのなかでもA列を書き換えていて、Changeイベントがループしてしまうので
main.pasteValues ファンクションの下記行を消してください。
----------------------------------
ws.Cells(row, colIsbn).Value = map("ean")
----------------------------------
ただ、マクロはバックグラウンド実行できませんので、「データ取得中に次のISBNをどんどん入力する」のは無理です。
なので、現状の 「入力・入力・入力→実行(3件取得) 」方式に比べて
検討されている「入力→実行→入力→実行」方式 がすごく便利かどうか・・・?
という気がしますが、まぁそこは好みや操作方法次第ですね。
JANが10桁に変換されてしまう件は、長くなるので次のコメントで書きます。
2) JANが10桁に変換されてしまう
返信削除これはですね・・・もともと私が作った目的が「ISBNで本を検索する」だったので、JANやEANを考えない作りになっています。
具体的には、Amazon Product Advertising API へのリクエストで
・IdTypeを指定しない → ASINと判断される
・ItemIdには10桁のISBNを指定 → ASIN=10桁のISBN なので
・SearchIndex → 不要
ということです。つまり13桁のISBNは10桁に変換しています。
amiさんのニーズですと
・IdType=EAN
・ItemId ・・・EAN、JAN、13桁ISBN はそのまま。10桁ISBNは13桁に変換
・SearchIndex=All
加えて商品・ランクを取得するために
・ResponseGroup=Large
と、リクエストパラメータを変えればよいと思います。
リクエストパラメータの生成は helper.signedUrlFor
レスポンスから値を取り出すのは helper.getAttributeMaps
セルへの書き出しは main.pasteValues
でやっています。
また、マクロ実行時に、リクエストしたURLをイミディエイトウィンドウに出力しており、
これをWebブラウザからリクエストすると、結果のXMLが見られます。
(有効期間が短いので急ぐ必要ありますが)
結果XMLのどこに欲しいデータがあるか調べるのに便利です。
いちおう動く形のをこちらに入れました。
https://github.com/anarchiStraw/excel/tree/EAN-ALL-Large
ISBN10桁→13桁変換はしていません。
きっちり動作確認とか、不要な処理を削るとかもしていませんので、あくまでご参考まで、でお願いします。
anarchiStrawさん 丁寧な回答ありがとうございます。
返信削除たしかに「Ctrl+Enter」では余計な手間が増えてしまいますから
JAN/ISBNをまとめて入力して、後からデータを取得する方が現実的のようです。
anarchiStrawさんに作って頂いたコードとVBAの本を見ながら
ここは何を書いてあるのだろうかと悩み格闘する日々です(笑)
VBAを学ぶ良い機会となっていますから、なんとか自分が求めるものを
作リたいと思います。
また分からない点を質問させて頂くことがあるかと思いますが
ご指導よろしくお願いいたします。 感謝
anarchiStrawさん
返信削除10桁のISBNデータが取得できないのを何とかしたいと思っていますが、
helperのSelect Case Len(isbn)以下の部分でCase10を
13桁に変換させるようなコードの追加が必要になるのでしょうか?
amiさん。また遅い返事ですみません。もう解決してるかもしれませんが
返信削除二通りの対応が考えられます。
A) 10桁のISBNを13桁に変換し、EAN扱いで検索する
B) IDの変換はせず、10桁ならIdType=ISBN、そうでなければEANとして検索する
Aならば
・toAsin()と似た構造でtoEan()を作り、入力値が10桁の場合、13桁ISBNに変換する
・mainでtoAsinでなくtoEanを呼ぶ
・(副作用として)helper.endpointsでの国判定時に"978"を取り除く
Bならば
・helper.toAsin() は不要なので削除
・trimとハイフン除去は必要ならする(mainででも、何か関数ででも)
・helper.signedUrlForで指定するIdTypeを、ISBNかEANか切り替える。安直には
& IIf(IsMissing(asin), "", "&IdType=" & IIf(Len(asin) = 10, "ISBN", "EAN")) _
Aの例をgithubにコミットしました。参考にしてください。
https://github.com/anarchiStraw/excel/tree/EAN-ALL-Large
お忙しいところ何回も質問して申し訳ありません。
返信削除いろいろ方法があるのですね。
参考にさせていただき少しずつ勉強していきます。
本当にありがとうございました。