スポンサードリンク

VBA

Excelでビッグデータを乗りこなせ(6.SQLiteデータベースの作成)

前回はExcelとは異なるデータベースの独自要素を見てみた。 www.excellovers.com 今回からはその点を踏まえつつ実際にSQLiteデータベースの作成をしてみたいと思います。 ExcelでSQLiteデータベースを操作 上記を実現するためにExcel for SQLite使用する。 …

Excelでビッグデータを乗りこなせ(5.データベースの独自要素)

ADODBを利用してCSVファイルを操作する方法を検証した。 www.excellovers.com 今回からはそのCSVを取り込むためのSQLiteデータベースの設計・作成をしてみようと思います。 データベースならではの要素 今でもSQLiteデータベースを使用していますが、感覚的…

Excelでビッグデータを乗りこなせ(4.ADODBでCSV取り込みの準備)

前回でSQLiteへCSVファイルを取り込む方法をADODBで行うことを決定した。 www.excellovers.com 今回からADODBを利用したCSV取り込みの方法を紹介します。 会計ソフトの代わりに家計簿ソフト 今回のコードは会社で使用している会計ソフトから出力したCSVファ…

Excelでビッグデータを乗りこなせ(3.CSV取り込みの試行錯誤)

前回で使用するデータベースをSQLiteにすることを決定した。 www.excellovers.com 今回からCSVファイルの取り込み方法について試行錯誤していきたいと思う。 現状の取り込み方法 ・Querytables.addでCSVをExcelに取り込む ・取り込んだデータをSQLのInsert分…

Excelでビッグデータを乗りこなせ(2.データベースを決める)

前回華々しくビッグデータの大海原に漕ぎ出したわけですが、大海原を乗り越えるための船がまだ決まっていません。このままではあえなく溺れ死んでしまいます。 ビッグデータを乗り超えるための船 データベースです まずはこれを決めることから始めたいと思い…

Excelでビッグデータを乗りこなせ(1.データベースでやりたいこと)

私は職場で経理を担当していて、月次の資料を作る際に ・会計ソフトから仕訳データをCSVで出力 ・出力したデータをExcel VBAでSQLiteにインサート ・Excel VBAからSQLを使用してSQLiteから集計後のデータを出力してExcelへ転記後、関数を使用してフォーマッ…

集計表をダブルクリックすると内訳を表示するようにしてみた

VBA

先日、会社で作ったVBAの機能に我ながらテンションが上ったので取り敢えずご紹介だけ。 完成品 このようなデータを このように集計している表があるとします。 カツの売上が先月より良いけどなんでだろうと思ったとする。 そこで5月のカツのセル(C3)をダ…

子供のために作った筆算学習ツールを割り算対応にした

VBA

先日作成したこちらの筆算の学習ツール。 www.excellovers.com 足し算・引き算・掛け算までは出来ていたのですが、勢いに任せて割り算対応もしてみました。 その過程でクライアント(嫁)から次々と要望が出てきましたが、何とか全て対応いたしました。 その…

子供のために筆算学習ツールを作ってみた

VBA

以前に100枡計算ツールを息子のために作ったのだが、学校で筆算を習うようになったので筆算のツールを作って欲しいとせがまれてしまった。 子供のリクエストとあらば答えねばなるまい! 完成品 完成品はこちらです。 足し算の動きはこんな感じです 引き算 掛…

他社に送る書類からコメントを一時的に退避させる(3.退避の改修)

前回・前々回でExcelからコメントを一覧にして抽出して別ブックに退避して削除、退避したコメントの復元までを一応完成させた。 www.excellovers.com www.excellovers.com しかし実際に使ってみたりblogへのコメントを頂いて「コメントの退避」の内容を少し…

他社に送る書類からコメントを一時的に退避させる(2.復元)

前回はコメントを全て別ブックに退避させてから、全て削除するところまでを実現させました。 今回は退避させたコメントを元のブックに復元させるところまでを検証してみます。 コード Sub CommentInport() Dim FilePath As Variant Dim myWB As Workbook Set…

他社に送る書類からコメントを一時的に退避させる(1.退避)

資料を他社に送る時に気をつけないといけないのが コメントの記載です。 自社内ならともかく他社の方に見られるとちょっとまずい内容が書かれていることが多々あります。 送付用にブックをコピーしてコメントを削除してから送信したりしていますが、この時期…

VBAでSQLを使用する際にListObjects(テーブル)をFrom句に指定する方法

VBA

ADODBを利用することでExcelのデータをSQLで操作することが出来るようになります。 SQL文のFrom句でワークシート名を指定する方法は知っていたのだが、名前定義の範囲やテーブルを指定する方法を知りたかったので調べてみた。 ワークシートの場合 まずは基本…

各種ファイル検索(Dir関数・FileSystemObject・FindFirstFile関数)比較(ファイル検索比較7)

前回まででDir関数・FileSystemObject・FindFirstFile関数と3種類のファイル検索を検証してみた。 今回はそれぞれの検索方法の特徴や処理速度などを比較検証してシリーズの締めくくりとしたい。 処理速度 まずは検索スピードについて検証してみる。 条件1 …

サブフォルダ内を含むWindows API(FindFirstFileW)を利用したファイル検索(ファイル検索比較6)

前回はFindFirstFile関数を利用したファイル検索を検証してみた、がその際にポインタがわからないとtwitterでつぶやいたところ、色々ご指導頂いたりサイトをご紹介頂いた。 www.excellovers.com ポインタとは関係ないがご紹介頂いたサイトでFindFirstFile関…

サブフォルダ内を含むWindows API(FindFirstFile)を利用したファイル検索(ファイル検索比較5)

前回までにDir関数とFilesystemObjectを利用したファイル検索を検証してみた。 www.excellovers.com www.excellovers.com 今回は最後にWindows APIの1つであるFindFirstFile関数をVBAから利用してファイル検索を実現してみたいと思います。 Windows API関数…

サブフォルダ内を含むFileSystemObjectを利用したファイル検索(ファイル検索比較4)

VBA

前回はFileSystemObjectを利用して1つのフォルダ内のファイルを検索するコードを検証してみた。 www.excellovers.com 今回は再帰処理を利用して指定フォルダ内だけでなくフォルダ内フォルダの検索を実現してみようと思う。 再帰処理でフォルダ内検索 Dir関…

FileSystemObjectを利用したファイル検索(ファイル検索比較3)

VBA

前々回・前回とDir関数を使用したファイル検索について検証してみた。 www.excellovers.com www.excellovers.com 今回はFileSystemObjectを利用してファイル検索実現してみたい。 FileSystemObject FileSystemObjectを使用するには参照設定が必要です。 ツー…

サブフォル内を含むDirを利用したファイル検索(ファイル検索比較2_修正)

VBA

今回は前回記事の一部修正です。 www.excellovers.com コード記述中にずっとモヤモヤしていたことが解消できたのでわずかですが修正公開です。 今回は再帰処理を利用して指定フォルダ内だけでなく、フォルダ内のフォルダの中身を検索できるようにしてみる。 …

サブフォル内を含むDirを利用したファイル検索(ファイル検索比較2)

VBA

前回はDir関数を利用したファイル検索を検証した。 www.excellovers.com 今回は再帰処理を利用して指定フォルダ内だけでなく、フォルダ内のフォルダの中身を検索できるようにしてみる。 再帰処理でフォルダ内検索 前回は1つのプロシージャで実現したが、今…

Dirを利用したファイル検索(ファイル検索比較1)

VBA

これから何回かに渡ってVBAでのファイル検索の方法を検証してみようと思います。 まずはDir関数からです。 Dir関数の使用方法 Dir関数 Dir ( [pathname], [ attributes VbFileAttribute = vbNormal ]) as String 引数 内容 pathname 省略可能。検索したいフ…

検索対象のデフォルトを「値」に変更したい

2019/02/11 20:30 修正 以前は感じなかったのですが、最近検索してもある筈のものが検索に引っかからないと感じることが多いです。 自分の場合そういう時はほぼ検索対象が「数式」になっているためです。 個人的には式の中身を検索対象にすることは稀なので…

リスト形式へのデータ整形

VBA

先日twitterでデータ並び替えで質問を投げかけられているのを見かけたので、物は試しとやってみた。 前提条件 下記のように横1行に並んでいるデータを4つ毎に区切って下に順番に並び替えていく。 元ネタをつぶやかれた方は「For」を使ってと条件をつけてい…

ビット演算を数式の条件判定に使えないか検証してみた

VBA

前回2進数とビット演算について検証をしてみた。 www.excellovers.com 今回はビット演算の考え方をExcel関数の条件判定に使えないかと検証してみた。 sumproductの代替 パッと思いついたのがこれだ。 A列からC列はテーブルに変換している。 J列K列で名前定義…

ファイル属性の判定方法(2.ビット演算でファイル属性判定)

VBA

前回はGetAttr関数を使ってファイル・フォルダの属性値をVBFileAttribute列挙型で取得する方法を紹介した。 www.excellovers.com 今回は取得した返り値から自分が探している属性値を含んでいるかどうかを判定する方法を検証する。 どうして判定が必要か? Ge…

ファイル属性の判定方法(1.GetAttr関数の使い方)

VBA

ファイルやフォルダの属性を調べる関数と、その関数の返り値について検証してみました。 GetAttr関数構文 ファイルやフォルダの属性を調べるには GetAttrを使用します。 Function GetAttr(pathname as string) as VBFileAttribute 引数pathnameにはフォルダ…

2007形式から2003形式へ一括して変換

VBA

先日部長から「このメールに添付しているファイルを古い形式にしてAさんに送付しておいて」 と指示を受けた。どうやら先方からダウングレードして送付してほしいと依頼があったらしい。 仕方ないなぁと思いながら取り掛かろうと思ったらファイルが7つも添付…

配列から空白(Empty)を取り除く方法

セル範囲を一括で配列に取り込んだ際などに、空白が紛れ込むことがあります。 そのような場合に配列から空白を除く方法を検証してみます。 空白のある元データ 簡単なデータを用意しました。 このセル範囲を配列に取込、空白の削除を行うのが以下のコードで…

VBA変数の初期値とEmptyの不思議

VBA

変数って代入して初めて値が発生しているかと思いきや、宣言をしただけで値が入ってるんですよね。 今回は変数の型ごとの初期値を調べてみました。 変数の初期値一覧 早速だがこれが変数の型ごとの初期値一覧です。 型 初期値 String "" Boolean False Byte …

CDと本の管理システムをExcelで作成する(7.検索結果(配列)をSQLで並び替え)

前回でレーベンシュタイン距離を付与することが出来ました www.excellovers.com 今回はレーベンシュタイン距離の値に応じ検索結果を並び替えたいと思います。 ソートアルゴリズム?いえSQLです 検索結果はユーザーフォームに表示される直前に多段階配列(ジャ…