So-net無料ブログ作成
検索選択

なんちゃって EXCEL ACCESS VBA ブログトップ

Q/A クエリQuery で今年の干支のデータを抽出? [なんちゃって EXCEL ACCESS VBA]


Q/A クエリQuery で今年の干支のデータを抽出?

今年の干支のデータがチョット分からないのですが!

★Where 省略

1) 日付から今年を抜く

Year([TBL]![ymd]) = Year(Date())
又は
Format([TBL].[ymd],''yyyy'') = Format(Date(),''yyyy'')
又は
#日付# で範囲指定 > xxx < とか Between


2) 干支テーブルを作成

1:猫 2:牛 3:虎 4:兎 5:龍、、、
^ ^
で、日付の年を12で割った時の「余り」値に合わせる

余りの計算関数: Mod
自分で計算するなら: x=Year([ymd]) - Int(Year([ymd])/12)
CInt(Year[ymd]) の型変換が必要かも!


3) 2)を踏まえて!
日付の項目に干支コードを入れておく方法も有ります


ものに依るので、もチョット具体的で無いと答えにくいデス!

Excel シートだけでも干支だけ表示なら簡単です
複雑なテーブルでなければ、
Vlookup と CountIf SumIf などでクロス集結も可能!

---

明日見むら
村長さんでした
(^^)/~~~

asumi-mura
by. Access VBA


nice!(1)  コメント(0) 

今年は2015年?和暦と西暦の計算方法! [なんちゃって EXCEL ACCESS VBA]


今年は2015年?和暦と西暦の計算方法!

書類を作ったり、
ネットで登録したり、、、

表示、入力する際に西暦だったり和暦だったり
イロイロです

ネットの登録などですと大体、西暦?
今年は、
平成26年なので2014年になります
年度は一般的には国の会計年度を指すので4月が新年度になります
よって、
平成25年度(3月まで)
平成26年度(4月以降)

ネットで検索してると大きなイベントのページですが、
2015年XXXなんて見かける事も!
予定だったら分かるのデスが、、、
^_^;
中々覚えられません?!

で、簡単な計算方法を!

覚える数字は3つ
平成=★12、◆88 (足せば100です)
昭和=☆25

これを踏まえて!

【西暦和暦変換】
2014年: 14+★12=H26年
2001年: 1+★12=H13年
2000年: 100-◆88=H12年

1998年: 98ー◆88=H10年
1976年: 76ー☆25=S51年

【和暦西暦変換】
H25年: 25ー★12=2013年
H11年: 11+◆88=1999年
S49年: 49+☆25=1974年

【年度】
3月まではー1
4月はそのまま


2000年を境にこう言った+ーで計算します
大正、明治 も有るのですが、今では実質、計算する事は余りないと思います

西暦-->和暦の2000年を除けば、
必ず、
年の下2桁で換算します

計算出来ない時は、計算できる方法の数値を選ぶ!
そう言う覚え方で良いと思います


アプリで計算してくれるのも有りますが、
この位の計算は覚えておいて損はありません
9x9 より簡単!

覚える数値は3つと言いましたが、
実質2つ、12、25だけです
平成の12だけでも実際は問題ないかも!
昭和は生年月日を変換する時くらいでしょうから、、、

計算できれば正解!
出来なければ次を試す

それで良いと思います


色んな書類を作っていると
計算方法を覚えていないとサクっ!と作れない場合が有ります

和暦の書類は全て和暦
西暦の書類は全て西暦
併記する場合も有ります

コンピュータ的には基本、西暦!
トラブルが発生した場合には西暦の方が見つけやすいです
1つしか無いから!

例えば14年だったとして、目視でチェック!
14と言う数値を探す時に、
H14、S14、T14、M14 より
2014、1914 の方が間違えにくいデス

形も違いますし、、、
数値でも形で認識すると間違えにくくなります

この辺りが右脳と左脳の違いになるのでしょうかネ?


ちなみに、
EXCEL だと、書式設定で、
西暦: yyyy/mm/dd、yyyy.m.d
和暦: gee/mm/dd(aaa) など

y=年、m=月、d=日、aaa=曜日
g=和暦元号、e=和暦年

例) 2014/05/09、2014.5.9
   H26/05/06(火)


チト長くなりましたが、
ネットで見かけた不思議な日付 の巻でした


---

閏年(うるうどし)の計算方法

---

明日見むら
村長さんでした
(^^)/~~~

asumi-mura
by. Wareki Calc

nice!(0)  コメント(0) 

ACCESSからEXCEへ出力!VBA その8 アクセス講座 写真の挿入方法 [なんちゃって EXCEL ACCESS VBA]

ACCESS から EXCE へ出力!

VBA その8 アクセス講座

個人の管理台帳などに写真を添付したいと相談を受けまして、、、

1) Access DB 自体に写真を取り込む

共通処理フォルダ ¥PIC で外部参照 LINK させる

2) 管理番号.jpg

3) 管理番号.xls

など考えられます

どれが良いかはユーザーの要望を優先させます

が、、、

個人的には、3番目がオススメです

取り敢えずEXCELに画像を貼っておけば、画面からハイパーリンクで参照させれば簡単です

DB 更新 SQL では、

テーブル=リンク項目

クエリ="#" & PATH & "管理番号.xls" & "#"

で一括更新できます

両サイドに"#"を入れます

EXCEL だと、関数でHyperlink(パス+ファイル名)

 

意外と簡単に管理できます

DB で無くてもEXCEL のリンク機能でも同様に管理可能!

 

結局、今、有る台帳に¥PICから写真を所定の位置へ挿入する事になりました

EXCEL VBA だと

ブック("Sheet名").Select

シート.Cells(行, 列).Select

シート.Pictures.Insert D:xxx¥PIC¥001.jpg

これで追加挿入できます

 

写真のサイズなどは、別のソフトで所定の大きさを決めれば良いデスね

そして、名前を付けて ¥PIC へ放り込むだけ!

 

台帳印刷で作成し直せば任務完了!

楽チンです

 

因みに、全ての写真が全て揃わないケースがある時は

If Dir(D:xxx¥PIC¥001.jpg) <> "" Then

    挿入処理

End If

となります

---

明日見むら

村長さんでした

(^^)vba

asumi-mura

by. EXCEL VBA Pictures Insert

---

ACCESSからEXCELへ出力

  ⇒VBA アクセス講座(その1)ひな形

  ⇒VBA アクセス講座(その2)ひな形の補足

  ⇒VBA アクセス講座(その3)データ定義の違い

  ⇒VBA アクセス講座(その4)共通関数の作り方

  ⇒VBA アクセス講座(その5)システムファイル

  ⇒VBA アクセス講座(その6)EXCELコピペの方法

---


nice!(0)  コメント(0) 

閏年の計算方法 ACCESS EXCEL VBA [なんちゃって EXCEL ACCESS VBA]

閏年の計算方法 ACCESS EXCEL VBA

1: 400年に1回、閏年

2: 100年に1回、閏年ではない

3: 4年に1回、閏年

実際のプログラムは明日やりましょ!

宿題!!!

まんまだと思うでしょ

イロイロ有るのネ、これ又、不思議?!

---
明日見むら
村長さんでした
(^^)/~~~

asumi-mura
by. UruDoshi 4nen

nice!(1)  コメント(0) 

Q/A VLOOKUP関数で認識できない名前は何ですか? [なんちゃって EXCEL ACCESS VBA]

Q/A Vlookup関数で認識できない名前は何ですか?

1: 名前の管理 が登録間違いしている

2: システム上の名前を使用している

3: 使えない文字を使用している

4: Office 自体のバグなど

   Microsoft Update をして最新パッチを当てる

例えば、使えない名前だと

関数と同じ文字列、SUM、ID、END などはダメな気がします

文字は特殊文字、記号とか

・ % * ① ② など、これらの文字列は、Access などDB、SQL文、などで引っかかる可能性が有ります

特に ・ は気をつけて下さい

Access のSQL記述時にエラーになったと思います

データの場合だと、仮に今だけ良くても、Windows から Unix系へ移行した時に文字化けします

フリーのサーバ 例えば BOX などへ音楽ファイルをアップロードすると文字化けするのはこの様な事があるからデス

厳密には少々違いますが、大体そんな感じ

でも、画面操作のクエリ は通ります

区切るのであれば、アンダーバー、_ を使用して下さい

カタカナ日本語も好ましく無い

英数字は半角、空白はダメ

例)としては、極力、半角英数字にして下さい

N001_Key xUSR集計

予約後として絶対に使われそうに無くて自分が分かりやすい名前にします

Windows のログインユーザ名も半角!

プログラムは半角英語圏のモノでしょ

だから機械は半角が基本

これは絶対に変わる事は有りません

マズそうかなって思ったら使わない

数をこなさないと中々難しいデスが、、、

---
明日見むら
村長さんでした
(^^)/~~~

asumi-mura
by. Q/A Excel



nice!(0)  コメント(0) 

(その5) かんたん家計簿 EXCEL なんちゃって、下関エクセル教室 [なんちゃって EXCEL ACCESS VBA]

なんちゃって、下関エクセル教 第五段

(その5) かんたん家計簿 EXCEで作ってみましょ

=================
Sheet名: DAT データ入力シート
EXCEL.05-005.jpg

 

 

 

 

 

 

 

年月、のセルを分けて見ました!

こんな感じのを作りたい、、、とします







、、、











=================
Sheet名: MF マスタファイル

  |A|B |C       |D        |
 2| |No|購入先名|コンボ   |
 3| |1 |【繰越】|1【繰越】|
 4| |2 |スーパー|2スーパー|
 5| |3 |コンビニ|3コンビニ|
 6| |4 |薬局    |4薬局    |
 7| |5 |外食    |5外食    |
 8| |6 |        |6        |
 9| |7 |        |7        |
10| |8 |その他  |8その他  |
11| |9 |【計】  |9【計】  |


B列=購入先No  1桁
C列=購入先名 10桁
D列=セルのコンボに使用する名称

D列 =B3 & C3


EXCEL.05-004.jpg

 

 

 

 

 

 

 

 

=================
Sheet名: DAT データ入力シート

(入力)
B列 書式設定=yyyy年m月、入力は年月日
D列 コンボで選択★
E列 書式設定=#,###

(表示のみ)
C列 =IF(B2 = "", "", B2)

F列 書式設定=#,##0
F2  =E2
F3以降 =IF(E3 = "", "", F2 + E3)

これで、残高もキレイに出ます

★D列 はコンボで選択型の入力になります
EXCEL.05-011.jpg

 

 

 

 

 

 

 

データ-->データの入力規則

   入力の種類: リスト
   ドロップダウンリストから選択する:ON
   元の値: =MF!$D$3:$D$11

Office2007 以降はコレで良かったと思いますが、

それ以前は、シート参照が出来ません

上手く行かなかったら、、、

DAT と同じ、シート内に MFデータを作って参照して下さい

 


EXCEL.05-012.jpg

 

 

 

 

 

 

エラーメッセージ

   無効なデータが入力、、、:OFF

   チェックをかけるとかなり鬱陶しいデス、お好みで!


▼のマークをクリックすると、、、

コニャニャになります
EXCEL.05-013.jpg

 

 

 

 

 

 

実際に作る時は、このマスタ設定をよ~く考えてから作りましょう

データベースでは無いので、簡単には変更できません

最悪、置換で対応するって方法もありますが、、、

項目数を2桁にするか等など!

実際に使うのであれば2桁くらいは、必要なくても持っておいた方が良いと思います


今回は入力するシートなのでシートを保護してみましょ
EXCEL.05-030.jpg

 

 

 

 

 

 

 

セルの規定値ロックになっているので、OFF にします

   B2~B列、D2~D列、E2~E列
EXCEL.05-031.jpg

 

 

 

 

 

 

これでシートを保護すると入力可能なセル以外は修正できません
EXCEL.05-032.jpg

 

 

 

 

 

 

 

うっかり関数を消しちゃった、、、が無くなります

怒られると、、、

EXCEL.05-033.jpg

 

 

こんなのが出ます

(^^;汗

---

次回も、家計簿で日別集計をやってみましょう

---
明日見むら
村長さんでした
(^^)excel

asumi-mura
by. EXCEL CALC


nice!(0)  コメント(0) 

(その4) クロス集計、件数、金額 EXCEL なんちゃって、下関エクセル教室 [なんちゃって EXCEL ACCESS VBA]

なんちゃって、下関エクセル教室 の第四弾

(その4) クロス集計、件数金額 EXCEL

=================
Sheet名: DAT データ領域

  |A|B        |C|D     |E  |
 1| |         | |      |   |
 2| |2014年2月1日|1|みかん|  1|
 3| |2014年2月1日|5|野菜  |  1|
 4| |2014年2月1日|6|ごはん|  1|
 5| |2014年2月2日|1|みかん|  2|
 6| |2014年2月2日|2|りんご|  4|
 7| |2014年2月2日|4|梨    |  8|
 8| |2014年2月2日|1|みかん| 10|
 9| |2014年2月2日|2|りんご| 20|
10| |2014年2月2日|3|いちご| 30|
11| |2014年2月2日|4|梨    | 40|
12| |2014年2月2日|5|野菜  | 50|
13| |2014年2月2日|6|ごはん| 60|
14| |2014年2月2日|1|みかん|100|
15| |2014年2月2日|5|野菜  |500|


B列=日付
C列=食事の番号
D列=食事の名称(マスタから表示)
E列=金額
EXCEL.04-003.jpg

 

 

 

 

 

 

 

=================
Sheet名: RPT レポート印刷

 |A|B        |C     |D     |E     |F |G   |H     |
1| | 食事    |1     |2     |3     |4 |5   |6     |
2| |2014年2月|みかん|りんご|いちご|梨|野菜|ごはん|
3| |1       |5     |100   |      |20|600 |10    |
4| |2        |200   |      |20    |1 |40  |5     |

こんな感じのを作りたい、、、とします
EXCEL.04-051.jpg

 

 

 

 

 

 







、、、











=================

Sheet名: CALC 計算領域

又々前回とほとんど同じです

関数だけ知っていればOK!
---
B2 =IF(DAT!B2 = "", "", DAT!B2)
C2 =IF(DAT!C2 = "", "", DAT!C2)
D2 =DAT!D2
E2 =DAT!E2 ★

G2 =DAY(B2)
H2 =IF(C2 = "", "", C2 & "-" & F2)

★金額に変えただけ!前回と全く同じです
---
EXCEL.04-004.jpg

 

 

 

 

 

 

 

 

=================

Sheet名: RPT レポート印刷

---
EXCEL.04-052.jpg

 

 

 

 

 

 

 

C3 =COUNTIF(KEY, D$1 & "-" & $B4)
D3 =SUMIF(KEY, D$1 & "-" & $B4, KEY金額)
EXCEL.04-051.jpg

 

 

 

 

 

 

---
C3、D3 だけ記述しました

他は、ほぼ同じです、列が違うだけ
コピペでほとんど作れます

セル=ゼロサプレス、ゼロを表示しない様にしています

名前で管理しているので、KEY数量-->KEY金額の修正も簡単です

最近のOfficeは名前を変えると自動で変更されます

EXCEL.04-002.jpg

 

 

 

 

 

 

 

ちなみに、このエクセルを作ると、標準のエクセルで使用していると、エラーが出ます

 に同じのセルがあるとオカシイとエラー表示される

 余計なお世話ですが必要な人もいるのでしょう

そんな時は、、、

EXCEL.04-001.jpg

 

 

 

領域内の他の、、、数式 をOFF

---

テスト用のデータも判る人は判っていると思いますが、

私は、この様な作り方をします

金額の数を変えるて識別しやすい数値にしていると検証が楽チンです

件数の確認も金額を見るだけで判りますネ

実際は最終的に本番のデータでもう一度確認します

その1 は無いものを表示しない
その2 以降は無いもの表示させています

この違いが判るでしょうか?

実際の業務では色々有ります

いつも固定で出すモノと、有るものだけを出す場合

エクセルのツールでは、無ければ、出すことが出来ません

出そうとすれば、又、それなりの仕掛けが必要になります

何が自分で使いやすいかを考えてエクセルを覚えると良いと思います


基本的なパターンでこの様なケースは有ります

使っている関数もゴク僅かです

覚えていても損はありません
---

次回は、家計簿、年齢別範囲の集計、などをやってみましょう

---

(その1) 有るものだけを表示する EXCEL

(その2) クロス集計、名前の管理 EXCEL

(その3) クロス集計 EXCEL

---
明日見むら
村長さんでした
(^^)excel

asumi-mura
by. EXCEL CALC


nice!(0)  コメント(0) 

(その3) クロス集計 EXCEL なんちゃって、下関エクセル教室 [なんちゃって EXCEL ACCESS VBA]

なんちゃって、下関エクセル教室 の3回目

(その3) クロス集計 EXCEL

=================
Sheet名: DAT データ領域

  |A|B        |C|D     |E  |
 1| |         | |      |   |
 2| |2014年2月1日|1|みかん|  1|
 3| |2014年2月1日|5|野菜  |  1|
 4| |2014年2月1日|6|ごはん|  1|
 5| |2014年2月2日|1|みかん|  2|
 6| |2014年2月2日|2|りんご|  4|
 7| |2014年2月2日|4|梨    |  8|
 8| |2014年2月2日|1|みかん| 10|
 9| |2014年2月2日|2|りんご| 20|
10| |2014年2月2日|3|いちご| 30|
11| |2014年2月2日|4|梨    | 40|
12| |2014年2月2日|5|野菜  | 50|
13| |2014年2月2日|6|ごはん| 60|
14| |2014年2月2日|1|みかん|100|
15| |2014年2月2日|5|野菜  |500|


B列=日付
C列=食事の番号
D列=食事の名称(マスタから表示)
E列=数量


EXCEL.03-001.jpg

 

 

 

 

 

 

=================
Sheet名: RPT レポート印刷

 |A|B        |C     |D     |E     |F |G   |H     |
1| | 食事    |1     |2     |3     |4 |5   |6     |
2| |2014年2月|みかん|りんご|いちご|梨|野菜|ごはん|
3| |1       |5     |100   |      |20|600 |10    |
4| |2        |200   |      |20    |1 |40  |5     |

こんな感じのを作りたい、、、とします
EXCEL.03-031.jpg

 

 

 

 

 

 

 







、、、











=================

Sheet名: CALC 計算領域
EXCEL.03-021.jpg

 

 

 

 

 

 

 

EXCEL.03-022.jpg

 

 

 

 

 

 

 

前回とほとんど同じですネ

今回は簡単、関数だけ知っていればOK!

---
B2 =IF(DAT!B2 = "", "", DAT!B2)
C2 =IF(DAT!C2 = "", "", DAT!C2)
D2 =DAT!D2
E2 =DAT!E2

G2 =DAY(B2)
H2 =IF(C2 = "", "", C2 & "-" & F2)

数量が増えただけです
---


=================

Sheet名: RPT レポート印刷
EXCEL.03-032.jpg

 

 

 

 

 

---

C3 =SUMIF(KEY, C$1 & "-" & $B3, KEY数量)

---
C3 だけ記述しました
EXCEL.03-031.jpg

 

 

 

 

 

 

 

他は、ほぼ同じです、列が違うだけ
コピペでほとんど作れます

名前の管理

KEY

EXCEL.03-041.jpg

 

 

 

 

 

 

=CALC!$H$2:$H$16 

KEY数量

EXCEL.03-042.jpg

 

 

 

 

 

 

=CALC!$E$2:$E$16

★KEY、KEY数量=1対1 の関係になります

同じ行が、KEY、数量、になる事です!



青色=ゼロサプレス、ゼロを表示しない様にしています
EXCEL.03-005.jpg

 

 

 

 

 

 

 

 

見栄えが良いのは、空白?っと思います

これはエクセル自体の機能を使って

セルの書式設定 ユーザー定義 #,###

---

一見出来なさそうですが、意外と簡単ですね

考える、、、が重要の意味がだんだん判ってもらえたかと思います

あとは、ほとんど応用です
---

次は、この応用をもう少しやってみましょう


---
明日見むら
村長さんでした
(^^)excel

asumi-mura
by. EXCEL CALC


---なんちゃって、下関エクセル教室---

(その1) 有るものだけを表示する

(その2) クロス集計、名前の管理

ACCESSからEXCELへ出力⇒VBA アクセス講座(その1)ひな形

---


nice!(0)  コメント(0) 

(その2) クロス集計、名前の管理 EXCEL なんちゃって、下関エクセル教室 [なんちゃって EXCEL ACCESS VBA]

なんちゃって、下関エクセル教室 の第二弾

(その2) クロス集計名前の管理 EXCELでごJAL

Sheet名: DAT データ領域
 |A  |B        |C|D     |E|
1|   |         | |      | |
2|   |2014年2月1日|1|みかん|1|
3|   |2014年2月1日|5|野菜  |1|
4|   |2014年2月1日|6|ごはん|1|
5|   |2014年2月2日|1|みかん|2|
6|   |2014年2月2日|2|りんご|2|
7|   |2014年2月2日|4|梨    |2|

B列=日付
C列=食事の番号
D列=食事の名称(マスタから表示)
E列=数量(未使用)
EXCEL.02-002.jpg

 

 

 

 

 

 

Sheet名: RPT レポート印刷

 |A  |B        |C     |D     |E     |F |G   |H     |
1|   | 食事    |1     |2     |3     |4 |5   |6     |
2|   |2014年2月|みかん|りんご|いちご|梨|野菜|ごはん|
3|   |1       |●    |      |      |  |●  |●    |
4|   |2        |●    |●    |      |●|    |      |

こんな感じのを作りたい、、、とします
EXCEL.02-041.jpg

 

 

 

 

 

 

 

 







、、、











=================

Sheet名: CALC 計算領域

(X 食事, Y 日)G列を「KEY」という名前で登録しています

今回は有るか無いかだけを表示させているのでこの「KEY」だけで用が足ります

この「KEY」を作る事が考えられれば後は簡単
---
B2 =IF(DAT!B2 = "", "", DAT!B2)
C2 =IF(DAT!C2 = "", "", DAT!C2)
D2 =DAT!D2

F2 =DAY(B2)
G2 =IF(C2 = "", "", C2 & "-" & F2)

D列は確認用、未使用なのエラーで良い
F列はG列でチェックしている為エラー可
---
EXCEL.02-031.jpg

 

 

 

 

 

 

 

EXCEL.02-032.jpg

 

 

 

 

 

 

 

=================

Sheet名: RPT レポート印刷

EXCEL.02-042.jpg

 

 

 

 

 


EXCEL.02-041.jpg

 

 

 

 

 

 

 

---
J1=セルが表示するマーク●です
  よって変更可!

C1~H6=食事のコード番号になります
  大体、DB では、マスタとして管理します

B2 =DAT!B2
B3 1
B4 =B3 + 1
B31 =IF(ISERROR(DATEVALUE(TEXT(B$2, "yyyy/mm/") & B30 + 1)), "", B30 + 1)

TEXT関数=エクセルの書式設定と大体同じ、2014/02/29 にしています

C3 =IF(ISERROR(VLOOKUP(C$1 & "-" & $B3, KEY, 1,FALSE)), "", $J$1)
D3 =IF(ISERROR(VLOOKUP(D$1 & "-" & $B3, KEY, 1,FALSE)), "", $J$1)
E3 =IF(ISERROR(VLOOKUP(E$1 & "-" & $B3, KEY, 1,FALSE)), "", $J$1)

F3 =IF(ISERROR(VLOOKUP(F$1 & "-" & $B3, KEY, 1,FALSE)), "", $J$1)
G3 =IF(ISERROR(VLOOKUP(G$1 & "-" & $B3, KEY, 1,FALSE)), "", $J$1)
H3 =IF(ISERROR(VLOOKUP(H$1 & "-" & $B3, KEY, 1,FALSE)), "", $J$1)

---
B31=日付をチェックしています、2月は28日迄なのでエラーになります

  エラーの時は空白にしておけば、毎月の表も気にせずコピペで済みます

C3=C$1 & "-" & $B3 で同じ検索KEYを作っています

EXCEL.02-001.jpg

 

 

 

 

 

 

---

当然ですが、

ACCESS SQL、EXCEL機能でクロス集計は作れます

アクセスで組むのを前提であれば、良いですが、

エクセルだけのクロス集計では、中々思ったように1回では出来ません

作って、コピペして、再度、加工

見た目の良いのを作ろうとすればするほど手間暇がかかります

これなら、1回作るだけ

間に計を入れたりするのも、そんなに大変では有りません

---

次は、有る無しだけでなく、集計もやってみましょう


--- 2014-01-23 pm16:30 ---

画像が見辛いので入れ替えました

---
明日見むら
村長さんでした
(^^)excel

asumi-mura
by. EXCEL CALC

---

(その1) 有るものだけを表示する EXCEL

(その3) クロス集計 EXCEL

ACCESSからEXCELへ出力

---


nice!(1)  コメント(0) 

下関エクセル教室 (その1) 有るものだけを表示する EXCEL [なんちゃって EXCEL ACCESS VBA]

なんちゃって、下関エクセル教室

(その1) 有るものだけを表示する EXCEL SQL

等角フォントでないので見辛いデスが、、、

Sheet名: DAT データ領域
 |A  |B     |C |
1|   |      |  |
2|   |みかん|有|
3|   |りんご|無|
4|   |いちご|無|
5|   |梨  |無|
6|   |野菜 |有|
7|   |ごはん|有|

EXCEL.01-001.jpg





 

こんな感じのデータを下記の様に印刷したい

結構あります


Sheet名: RPT レポート印刷
 |A  |B     |C |
1|   | 食事 |  |<--タイトル
2|   |みかん|  |
3|   |野菜 |  |
4|   |ごはん|  |

実は、DBの設計でも固定で10項目にするか、可変のテーブルを持つか!など、状況次第で色々あります

そんな時でも、両方でどちらも同じ事が達成できるなら云うことは無いですネ!

とりあえず、エクセルから、どうやったら良いか考えてみて下さい

 

なるべく、考えてネ!

SE の殆どの仕事は考える、これだけ!

これからOffice 習ってみようとか、思われる方も同じです

まず、考える、、、このクセを付けて下さい

 

私のやっている事は、最初にEXCEL を覚えた時から、殆ど変わりません

基本的な、いくつかだけ覚えてて、後は、考える!

回が重ねられると分かると思います

ほとんど、同じですから、、、

 

Sheet名: CALC 計算領域

EXCEL.01-002.jpg

 

 

 

 

---
A列数式 =A1 + C2
B列数式 =DAT!B2
C列数式 =IF(DAT!C2 = "有", 1,0)

このA、C列は何をしているのでしょうか?

よ~く見ると分かると思いますが、値が入っている""の変わり目に数値がカウントアップされています

しかも必ず、+1、なので連番になりますネ!

これが思いつけば後は簡単!

 

ゼロ表示されると見栄えが悪くなりますネ!

そんな時は、セルに値が無ければ、空白をセットするとキレイに見えます

B9 数式 =IF(DAT!B9 = "", "", DAT!B9)
---

Sheet名: RPT レポート印刷
EXCEL.01-003.jpg

 

 

 

---
A列数式 =A1 + 1
B列数式 =VLOOKUP(A2, CALC!A$2:B$11, 2,FALSE)

VLOOKUP A2と、範囲内 左1列目固定の中から探し、範囲内の2番目の値を、完全一致で返す

 

エラーが出るので、下記の様にします

先ほどと同じ、エラーだったら、空白をセットします

B9 数式 =IF(ISERROR(VLOOKUP(A9, CALC!A$2:B$11, 2,FALSE)), "", VLOOKUP(A9, CALC!A$2:B$11, 2,FALSE))

あんまり長いと見づらくなります

バグの元なので、下記の様にした方が分かりやすいと思います

B11数式 =IF(ISERROR(C11), "", C11)
C11数式 =VLOOKUP(A11, CALC!A$2:B$11, 2,FALSE)
---

CALC!A$2:B$11 は「名前の管理」で定義すると、増えた時10⇒20など簡単に修正ができます


---
ACCESS SQL だと、こんな感じ!

SELECT [食事]

  FROM T3010_DB

  WHERE [有無] = "有"

  ORDER BY [NO];
---

いつもこんな感じで考え事をしております

その反動で、私生活では、なるべくグータラ人間になっております

(^^;

考えるクセを付けると色々良い事があります

一番は、ボケ防止、、、ほんと子供の頃はなんとも無かったのに、仕事をし始めると年々物忘れがヒドくなります

その分、覚えることが沢山あるって事でしょうが、、、

リハビリ、リハビリ、、、

(^^);

 

次は、これを踏まえて、自分でクロス集計しちゃいましょ!

ツールは使いませヌ、少しだけの関数で、やれますので、、、

宿題! 少~し考えてみてネ!

 

ちなみに今回のは、フィルター機能で手動で出来ます

Office2007 から、フィルター機能が便利になりました

お仕事の方法は1つでは無い!

何時も行う定例業務と一時的に行う仕事、提出期限、、、

などなどに依ってやり方を変える

これが仕事を楽しくやる秘訣かなぁ〜

引き出しが沢山あるとストレスも溜まりニクいです

何より、、、

凄く時間が掛かっていた作業が、簡単コピペで出来る様になるとモノづくりの楽しさが少しは分かってもらえるかなぁ〜などと考えております

--- 2014-01-21 pm21:49 ---

見直してた所、不備があったので訂正しました

---
明日見むら
村長さんでした
(^^)excel

asumi-mura
by. EXCEL CALC

---

(その2) クロス集計、名前の管理 EXCEL

ACCESSからEXCELへ出力 VBA アクセス講座(その1)ひな形

---


nice!(0)  コメント(0) 

ACCESSからEXCEへ出力!VBA その7 アクセス講座 リンクテーブルマネージャ [なんちゃって EXCEL ACCESS VBA]

ACCESS から EXCE へ出力!

VBA その7 アクセス講座

    Set DB = CurrentDb

こちらの説明!

DAO、ADO、ODBC、Jet、などなど接続方法はイロイロです

なんだかチンプンカンプン!

そうですネ!

細かいことを云えば、それぞれにメリット、デメリットはありますが、、、

そんなの覚えなくても良いのです

繋がる! これが大切!

折角、ACCESS 使っているのに、リンクテーブルマネージャ を使わない手はありません

 

これ何か?

DB、APP、2つを分離しましょうって以前カキコしました

この時に、APP側には、本体のテーブルが有りません

このDBにリンクを貼ってしまえば良いのネ!

 

これで、リンクを貼ると、このAPP のカレントデータベースとなる訳です

接続のゴチャゴチャは、WindowsPC なら ms さんデス、この方たちのツールに任せてしまえば良いって考え!

これだと、意外と変な事にはならないです

例えば、db接続するのに、ADO.ver、Jet.ver のバージョン、記述方法など固定化すると、何かの拍子に引っかかる事があります

Office のバージョンアップ、アップデートファイル、サービスパック、などなど

お任せ出来る所は勝手にやってもらいましょって事!

見た目もシンプル、プログラムも汎用的に使えます

 

リンクを貼ってカレントDBを使う

CurrentDb

そういう事です

 

コレ以外にも、不思議な記述があるでしょ!

例えば、正規表現しないとか、、、

まぁ正規表現がイマイチ意味が分かりませんが、、、

 

よく見かけるコーディング

何かの接頭語、Prefix を付けるでしょ

これ、社内ルールとかイロイロ有りますが、元々は学校で教えられた教科書的な発想なの!

異論はあるとは思いますが、実際の現場では、こんな事で上手く作業が進まないなんて良くあります

 

例えば、数値を定義、int

Dim intResultbox As Integer

最初の打ち合わせでは、4桁数字で良かったのですが、後になって、8桁、、、

いやいや、文字型にしないといけなくなった、、、などなど

変更をお願いしたら、今更、出来ません、、、みたいな事、言われたことないですか?

そう、、、

ユーザ側に立った記述では無いのです

頭に、int、lng、txt、など本来必要もないのに、それが分かりやすいと錯覚してるダケ!

残念ですが、この傾向はどこの会社にもあります

何が問題か解らない?

数値型を文字に変更すると、、、

本来、プログラムでは定義だけを文字にすれば大体、問題なく処理されるハズです

ですが、int など付けてると、それを定義してる文字コーディング自体を全て見直ししなければいけません

intResultbox --> txtResultbox  お客さんには全く関係ないことですが、システム的にはそうしないと後で見た時に訳が解らなくなってしまいます

ダメなのは分かっているけど、修正できない

そして無理やり、、、なんて結構やります

そっ、こんなバカらしい事やる必要ないっと私個人は思っています

よって、そんなコーディングは自分のシステムの範囲であれば設計もプログラムもそんな事はいたしません

 

もう1つ、、、Goto RTN_xx

    If i < 1 Then GoTo sheet_11

構造化は昔から云われる話ですが、これも度を超えると意味が不明になってきます

ムダな処理を1回ではじける(If 文など)のに、Goto 文を禁止してると、それだけで意味なく、構造化が次々生まれ、元の値を参照する為に Public 領域や、モジュール共通領域の Private を沢山必要にしてしまいます

昔の方のプログラムは結構スゴくて、メモリなど少ない時代のモノなので、限界に近いくらい効率よく組んでいます

そのかわり、デバックするのに時間もかかります

組み方によるっと言ってしまえばそれまでですが、そう言った本人の能力に依存しない様にプログラミングする上でイロイロなルールが存在します

今は、プログラマーと言うよりは、コーダー(書くだけのヒト)、SEは、プログラムすら組んだことが無いヒト、も沢山います

テストもしないので、データ入力もほとんどしません

こういった事が、ユーザに優しくないモノが出来上がってしまう正体です

 

確かに、異論はあるとは思いますが、、、

キチンとしたモノを作るのに一番効率よく動作するプログラム

これが、ユーザに喜ばれる事なのデス

 

教育としてのプログラム、学校は学校、実社会での実践はまた違うと言うことですネ

分かっているヒトも多くいるでしょうが、こんな下らない事すら元に戻せないのですから、これからコンピュータ関連の仕事をやりたいと思う方は大変でしょう

っとまぁ、、、余談が長すぎましたが、あくまでも私見でございます

それぞれでのルールに見合わない所は自分なりに修正して下さいっと言う事で、、、

学校や参考書とは違うけど、何でだろうって思われる方の為にカキコして見ました

 

DB からExcel に出力する基本パターンは大体、分かって頂けたと思います

次は、データを抜いた後、Excel だけで、いかに集計、編集が出来るかやってみましょ

Access のSQL と Excel の表計算!の違いも少々!

Excel はマクロを使わず関数だけでって所がミソなのネ

すこぉ〜し、頭をひねると意外と何でも出来ちゃいます

 

明日見むら

村長さんでした

(^^)vba

asumi-mura

by. EXCEL VBA Copy & Paste

---

ACCESSからEXCELへ出力

  ⇒VBA アクセス講座(その1)ひな形

  ⇒VBA アクセス講座(その2)ひな形の補足

  ⇒VBA アクセス講座(その3)データ定義の違い

  ⇒VBA アクセス講座(その4)共通関数の作り方

  ⇒VBA アクセス講座(その5)システムファイル

  ⇒VBA アクセス講座(その6)EXCELコピペの方法

---


nice!(0)  コメント(0) 

ACCESSからEXCEへ出力!VBA その6 アクセス講座 [なんちゃって EXCEL ACCESS VBA]

ACCESS から EXCE へ出力! コピペのイロイロ

VBA その6 アクセス講座

ひな形」は、

---


'   (範囲指定でコピーする方法)
  
    If i < 1 Then GoTo sheet_11
                                    'xlPasteValues = -4163, xlNone = -4142, xlPasteAll = -4104
    SHEET.Rows(Y_ROW).Copy
    SHEET.Rows(Y_ROW + 1 & ":" & i + Y_ROW).PasteSpecial Paste:=-4104, Operation:=-4142, SkipBlanks:=False, Transpose:=False

---

'   (1行づつコピーする方法)
                                    'row行目の書式を含めコピーする
    For i = i To 1 Step -1
        SHEET.Rows(Y_ROW).Copy
        SHEET.Cells(Y_ROW, X_COL).INSERT Shift:=-4121     'Shift:=xlDown  '-4121=xlDown、xlShiftDown
    Next

---

違いは、エクセルの操作と同じ

行を1行選択して、その次の行へコピーします

範囲選択とどちらが良いかは、PCの環境にも依ると思います

少ない行数、100行程度だと、ほとんど違いは有りません

範囲選択すると速いのですが、若干メモリを食う気がするので、、、

とは云え、、、

範囲選択でも、90,000-行くらいまでは、一瞬、です

xp の古いPC で256M でも大丈夫!

実証済み、、、但し、OffLine で使用しています

大体の目安です

 

これとは別に、簡単な書式全てOKであれば、EXCEL の書式設定で、列を全て選択してしまうのも1つの方法です

これだと、項目数、コピー行数が多くても良いですネ

この行をコピーする時間が短縮されます

2,000行で、100項目くらい、コピペで10秒くらいかなぁ~

まっ、スペックによるのですが、実際に使用していたのは、xp、1Gメモリ、シングルコアだったので、それでも時間短縮できるのは随分違って感じます

これを踏まえて更に高速化するには、、、

---

'---(時間短縮 Ver)--- ※Export(Work)→Copy(Paste)→Delete(Work)

    Dim SHEET3 As Object
    Dim BOOK_NM3 As String
    Dim w_IPCNT3 As Integer

    BOOK_NM3 = PATH_RPT & "\R3030_Exp.xls"

sheet_11:

    w_IPCNT3 = REC.RecordCount + 1
    REC.Close
   
    If w_IPCNT3 = 1 Then GoTo sheet_2

    OBJ.DisplayAlerts = False       '確認メッセージなしに保存する-->
    OBJ.WorkBooks(1).SaveAs BOOK_NM2    '一旦、保存

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
                            "Q3030_P1DAT", BOOK_NM3, False

    OBJ.WorkBooks.Open BOOK_NM3
    Set SHEET3 = OBJ.WorkBooks(2).WorkSheets(1)

    SHEET3.Rows(2 & ":" & w_IPCNT3).Copy    '1行目:HD
                                    'xlPasteValues = -4163, xlNone = -4142
    SHEET.Rows(3).PasteSpecial Paste:=-4163, Operation:=-4142, SkipBlanks:=False, Transpose:=False
   
    OBJ.CutCopyMode = False         '切り取りモードを解除する
    OBJ.WorkBooks(1).SaveAs BOOK_NM2
    OBJ.DisplayAlerts = True        '-->戻す

    OBJ.WorkBooks(2).Close
    Kill BOOK_NM3
   
    OBJ.WorkSheets(1).Select        '複数シートの時は一旦シートを選択しないとセルを選択できない!
    SHEET.Cells(1, 1).Select        '※上記「貼り付け」時は範囲選択も解除する事!(処理が異常に遅くなる)

'---(時間短縮 End)--------------------------------------------------------------
sheet_2:

---

何をやってるかって言うと、、、

ms さんのエクスポート機能を使っています

これ実際に手動で使えば分かりますが、ms 想定内の件数であれば、エクスポートがメチャクチャ速いです

細かい制御を調べても良いのですが、そんな時間をかけるより、これをそのまま使えば良いです

ACCESS DB のテーブル、クエリ、を選択-->右クリック-->エクスポート と同じです

これを一旦行なって、この全てを選択(EXCELの左上)

その後、、、

所定のEXCEL Sheet に値の貼り付け を行なっています

これが、一番効果的でした

但し、環境に依る事があるかもしれませんので、ダメなPC環境用にも、元々の処理は残しておきます

実際は、出力画面のオプションで、高速印刷チェック ON/OFF を入れています

規定値は、ON

かなり古いPCでも、引っかかった事はないのですが、項目数、レコード数、に依るかもしれませんので、その辺りは事前によくテストして見ましょ

まぁ、最近のPC で引っかかる事はないとと思いますが、VISTA、win7 なら有るかもしれません

Office のバージョンも重要です

win8 はかなり軽いですが、win7系は元々がVISTAでしょうから、かなり効率が悪いでしょうし、オフィースに至っては、年々余計なお世話機能が入りますので、その辺りは十分なテストが必要です

    w_IPCNT3 = REC.RecordCount + 1

プラス1 してるのは、タイトル行を除くため

他は、特に難しくないと思います

同じ事をするのにも、イロイロ有るでしょ!

こうやって、1つづつ、プログラムも大人になります

(^^)

 

明日見むら

村長さんでした

(^^)vba

asumi-mura

by. EXCEL VBA Copy & Paste

---

ACCESSからEXCELへ出力

  ⇒VBA アクセス講座(その1)ひな形

  ⇒VBA アクセス講座(その2)ひな形の補足

  ⇒VBA アクセス講座(その3)データ定義の違い

  ⇒VBA アクセス講座(その4)共通関数の作り方

  ⇒VBA アクセス講座(その5)システムファイル

  ⇒VBA アクセス講座(その7)カレントdb

---


nice!(0)  コメント(0) 

ACCESS から EXCEL へ出力! VBA その5 アクセス講座 --- SYSTEM-F --- [なんちゃって EXCEL ACCESS VBA]

ACCESS から EXCEL へ出力!

VBA (その5) アクセス講座

---

    'EXCEL 保存元/先 PATH を取得
    PATH_COM = "F:\Redhp300G\APP\COM"
    PATH_RPT = "F:\Redhp300G\APP\RPT"

---

前回のを踏まえて修正してみましょ

     PATH_COM = FUNC_SQL_GET("SELECT PATH_COM FROM T1000_SYS;", "")

     PATH_RPT = FUNC_SQL_GET("SELECT PATH_RPT FROM T1000_SYS;", "")

こうすると、パスが端末ごとに可変で与えられますネ!

F:¥ で固定だと、そこから変更するのにプログラム修正が必要になります

これで、より、汎用的になって来ました

---

DBにシステム設定用のテーブル T1000_SYS を作ります

(前提条件)

必ず存在する、必ず1件、DB 側ではなく、APP 側のテーブル とする

名前: T1000_SYS

項目: PATH_COM 文字255、PATH_RPT 文字255

---

DB 、APP は触れていませんでしたが、.mdb が2つとします

DB.mdb、APP.mdb

こうする事によって、APP を修正しても上書きするだけで済みます

APP、DB、2つを1つでまとめても良いのですが、そうすると、プログラム修正中は、DB を使用することが出来なくなります

また、、、

DB は後で、取り込んでも良い(コピー)と思うかもしれませんが、実際はムダな作業になりますネ

---

明日見むら

村長さんでした

(^^)vba

asumi-mura

by. Access VBA

---

ACCESSからEXCELへ出力

  ⇒VBA アクセス講座(その1)ひな形

  ⇒VBA アクセス講座(その2)ひな形の補足

  ⇒VBA アクセス講座(その3)データ定義の違い

  ⇒VBA アクセス講座(その4)共通関数の作り方

  ⇒VBA アクセス講座(その6)EXCELコピペの方法

  ⇒VBA アクセス講座(その7)カレントdb

---


nice!(0)  コメント(0) 

ACCESS から EXCEL へ出力! VBA その4 アクセス講座 ~明日見むら~ [なんちゃって EXCEL ACCESS VBA]

ACCESS から EXCEL へ出力!

VBA (その4) アクセス講座


標準モジュール: mCOM

---

Option Compare Database
Option Explicit

Public Function FUNC_SQL_GET(SQL As String, DAT As String) As String

    Dim DB As Database
    Dim REC As Recordset
   
    Set DB = CurrentDb
   
    Set REC = DB.OpenRecordset(SQL, dbOpenSnapshot)
   
    If Not REC.EOF Then
        FUNC_SQL_GET = Nz(REC(0), "")
    Else
        FUNC_SQL_GET = DAT
    End If
   
    REC.Close
    DB.Close
   
    Set REC = Nothing
    Set DB = Nothing

End Function

---

共通で使う関数を作ってみましょ

共通なので、Public

関数は、Function

関数名、FUNC_SQL_GET

受けパラメータ、(SQL As String, DAT As String)

渡すパラメータ、String、関数名に返されます、FUNC_SQL_GET が文字属性

 

例えば、

Dim xxP As String

xxP = FUNC_SQL_GET("SELECT dbNo FROM T3030_db"), "")

dbNo = 200 だったら

xxP = "200" 文字型の200が帰ってきます

 

T3030_db に、存在するNoかどうか(P-Key)をチェックする時は

If FUNC_SQL_GET(省略) = "" Then

    真、番号が無ければ、そのまま使う

Else

    偽、番号が存在すれば、新しい番号を自動採番する

End If

こんな感じで使ったりします

 

同じ様な関数も存在しますが、自分で組んだほうがシンプルですし、何よりエラー対応が簡単です

関数使って、よく分からないメッセージを返されて調べるくらいなら、汎用的な自分の関数の方が便利です

 

ちなみに、この関数を使用する所は意外と沢山あります

次回は、これを使ってひな形を、もう少し高度にしてみましょ!

---

明日見むら

村長さんでした

(^^)/

asumi-mura

by. Access VBA

---

ACCESSからEXCELへ出力

  ⇒VBA アクセス講座(その1)ひな形

  ⇒VBA アクセス講座(その2)ひな形の補足

  ⇒VBA アクセス講座(その3)データ定義の違い

  ⇒VBA アクセス講座(その5)システムファイル

  ⇒VBA アクセス講座(その6)EXCELコピペの方法

  ⇒VBA アクセス講座(その7)カレントdb

---


nice!(0)  コメント(0) 

ACCESS から EXCEL へ出力! その3 VBA アクセス講座 [なんちゃって EXCEL ACCESS VBA]

ACCESS から EXCEL へ出力(その3)

Public とは、パブリック

昔は、グローバル Global でも使えてました

消滅した文法、記述の仕方、言葉って言った方が分かりやすい?

同じ意味デス

ms さんお馴染みの、自動更新、Automatic Update、Windows Update、Microsoft Update

プログラムの機能、プログラムの追加と削除、アプリケーション、アプリケーションの追加と削除

書くと大変ですが、ほぼ似たような事!

おまじないっと思って下さい

今は、Public 、普通の言語は大体、Public

こう言った一般的な言い方をヘンテコにするのが、ms流

意味は、全部から参照可能!

この名前で呼び出せるって事です

ですから、

Public xxStr As String

Private yyStr As String

xxStr は、何処からでも使用可能

yyStr は、そのモジュールの範囲内で使用が可能

では、全て Public で良いでは無いかって思うでしょ

それでも良いデスが、そうだと実行時に沢山のメモリを消費します

途中でスパって落ちるアプリがあるでしょ

あんな感じになり得る事が多く発生します

チェックを事細かにしてやれば回避できなくも無いですが、現実的ではありません

 

Private によく似た記述で

Dim zzStr As String

これは、更に小さい範囲で使用する時の記述方法です

ひな形 をよ〜く見れば何となく分かると思います

 

最初は取っ付きにくいでしょうが、丸覚え!

意味も分からなくて良いと思います

理屈だてて考えても仕方ない事

誰かが決めたルールだから!

1 の次は、2 これと同じ

プログラムを作成する時は、ムダに労力を使わない

キチンと動作する処理が有れば、それより、より良い方法が見つからなければ真似をする

こうやって、覚えていけば良いのデス

問題が有ればいづれ発覚します

その時に修正し易い様に作るのもポイントになります

 

私は、プログラム も芸術だと思っています

美しいプログラムはキレイに動く

これが、最終的な目標になるかナぁ!

よって、使う人に必ず意見を聞く様にしています

独りよがりは、そのプログラムも悲しい運命を辿って消滅するから、、、

 

私が考える方法が全て良いとは思いませんが

どうして、そう考えるのか?って事も含めてプログラムを眺めると自分なりの良い方法がいつか見つかります

なるべく妥協しない

精神力も大切かなぁ〜!

なんてネ!

次回は、もうチョット突っ込んだお話をいたします

多分f^_^;ʬ

アクセス講座 その3

---

明日見むら

村長さんでした

(^^)/

asumi-mura

by. ACCESS EXCEL VBA

---

ACCESSからEXCELへ出力

  ⇒VBA アクセス講座(その1)ひな形

  ⇒VBA アクセス講座(その2)ひな形の補足

  ⇒VBA アクセス講座(その4)共通関数の作り方

  ⇒VBA アクセス講座(その5)システムファイル

  ⇒VBA アクセス講座(その6)EXCELコピペの方法

  ⇒VBA アクセス講座(その7)カレントdb

---


nice!(0)  コメント(0) 

ACCESS から EXCEL へ出力! VBA その2 ~ アクセス講座 ~ [なんちゃって EXCEL ACCESS VBA]

ACCESS から EXCEL へ出力! VBA (その2)

PC は、win8.1

hp EFI 仕様で、data Drive が(F)になっております

ここは適宜、修正を、、、

★バックスラッシュ\ => へ置換をお願いします

これは、Windows の仕様が、フォルダの区切りが、¥ なのネ!

普通の、UNIX、Linux、などは\ になります

ソネットさんのサーバーはUNIX系でしょうから、変換がかかってしまいます

¥で表示させている文字は、日本語で¥を入力していますので、、、

このパスが、ひな形エクセルの取込先 COM 、保存先 RPT が必要です

COM=COMMON

RPT=Report

まっ自分で判ればそれで良いです

    PATH_COM = "F:\Redhp300G\APP\COM"
    PATH_RPT = "F:\Redhp300G\APP\RPT"

で、この2つのフォルダをどこかに作って下さい

当然、この部分のパスも変更します

VBA.001.jpg

 

 

 

もう1つは、ひな形があるフォルダ COM にエクセルが必要です

フォームの名前は、F3030_G3印刷

    With [Forms]![F3030_G3印刷]
   
        BOOK_NM1 = "\R3030_R3帳票"
        BOOK_NM2 = "\R3030_R3帳票" 

VBA.002.jpg

 

 

 

アクセス で、DB テーブル を読む所

これも自分で作らないといけません

Q3030_P1DAT が、DB の名前

T: テーブル(データ)

Q: クエリ(一時的なデータの集合体、テーブルを加工したのがクエリ)

F: フォーム(画面)

M: マクロ(実行させたりのコマンド群、複雑なのは使わない)

R: レポート(基本、使わない)

mM: モジュール(VBA を書く所、プログラミングって事)

 

モジュールの塊は、mM3030

    SQL = "SELECT * FROM Q3030_P1DAT;"
   
    Set REC = DB.OpenRecordset(SQL, dbOpenSnapshot)  '読み取り専用

先ほどの、ACCESS VBA のひな形は、モジュールにコピペします

標準モジュールで良いです

 

マクロ では、 FUNC_R3030_OP 実行させます

マクロ自体のかたまりも、M3030 などとしておく方が分かりやすいと思います

マクロ名に、印刷 にします、条件などが全て出ない時は、全てを表示するボタンの切り替えがあります

Public Function FUNC_R3030_OP()

 

フォーム では、ボタン を配置して、、、

クリック したら、のイベントに、上記マクロ名(印刷)を指定します

 

大体、これで起動できると思います

後は、自分の出したいデータを入れて、画面を作って、ボタンを押すだけ!

 

これが上手く出来れば後は簡単!

複雑なクエリが分からなくても、エクセルだけでもなんとかなります

 

なるべくシンプルに、、、そして、新しいモノを取り入れない!

これが、軽くて、どのバージョンのオフィースでも動作可能な、VBA になります

 

コレ以外にも、ポイントは幾つかあるので、これをベースにより完成度の高いものを作って下さい

アクセス講座 その2! の巻

---

明日見むら

村長さんでした

(^^)vba

asumi-mura

by. EXCEL VBA

---

ACCESSからEXCELへ出力

  ⇒VBA アクセス講座(その1)ひな形

  ⇒VBA アクセス講座(その3)データ定義の違い

  ⇒VBA アクセス講座(その4)共通関数の作り方

  ⇒VBA アクセス講座(その5)システムファイル

  ⇒VBA アクセス講座(その6)EXCELコピペの方法

  ⇒VBA アクセス講座(その7)カレントdb

--- 


nice!(0)  コメント(0) 

ACCESS から EXCEL へ出力! VBA のひな形、作ってみました ~明日見むら~ やってみよう、意外と簡単! アクセス講座 [なんちゃって EXCEL ACCESS VBA]

ACCESS から EXCEL へ出力! VBA のひな形、作ってみました

やってみよう、意外と簡単なのネ!

でも、奥は深いですゾヨ(^^;

とりあえず、以下のひな形をコピペします

後ほど、説明カキコするかも!

(^^)

------------

Option Compare Database
Option Explicit

'(修正履歴)-------------------------------------
'2014-01-01:バグ修正 DoEvents 追加
'-------------------------------------------------

Private DB As Database
Private REC As Recordset
Private SQL As String
'

Public Function FUNC_R3030_OP()

    Call R3030_MAIN

End Function

Private Sub R3030_MAIN()
   
    On Error Resume Next
   
    DoCmd.Hourglass True                '砂時計 ON Screen.MousePointer = 11
    DoCmd.SetWarnings False

    Set DB = CurrentDb
   
    Call R3030_RPT_OP
   
    DB.Close
   
    Set REC = Nothing
    Set DB = Nothing
   
    SQL = ""
   
    DoCmd.SetWarnings True
    DoCmd.Hourglass False

    Interaction.Beep

End Sub

Private Sub R3030_RPT_OP()
   
    Dim OBJ As Object
    Dim SHEET As Object
   
    Dim PATH_COM As String
    Dim PATH_RPT As String
    Dim BOOK_NM1 As String
    Dim BOOK_NM2 As String
   
    Dim X_COL As Integer
    Dim Y_ROW As Integer
   
    Dim w_NM As String
    Dim i As Integer
   
   
'---(基本情報)---
    On Error GoTo err_ex_2          'ERROR EXCEL
   
    'EXCEL 保存元/先 PATH を取得
    PATH_COM = "F:\Redhp300G\APP\COM"
    PATH_RPT = "F:\Redhp300G\APP\RPT"
   
    With [Forms]![F3030_G3印刷]
   
        BOOK_NM1 = "\R3030_R3帳票"
        BOOK_NM2 = "\R3030_R3帳票"
       
        BOOK_NM1 = PATH_COM & BOOK_NM1 & ".xls"      '元:入力・ひな形
        BOOK_NM2 = PATH_RPT & BOOK_NM2 & ".xls"      '先:出力・Excel加工後
       
    End With
   
    'Excel の起動
    Set OBJ = CreateObject("Excel.Application")
   
    OBJ.WorkBooks.Open BOOK_NM1
    OBJ.DisplayAlerts = False       '確認メッセージなしに保存する-->
   
    OBJ.WorkBooks(1).SaveAs BOOK_NM2
    OBJ.DisplayAlerts = True        '-->戻す

    Set SHEET = OBJ.WorkSheets(1)
   
    OBJ.Visible = True
    OBJ.ScreenUpdating = False      '更新:stop
   
    On Error GoTo err_ex_1          'ERROR DB

'******************
'*   P1シート   *   Sheets(1)
'******************
   
    With [Forms]![F3030_G3印刷]

'---(タイトル)---
        w_NM = "作成日:" & Format(Now, "gggee/mm/dd(aaa) hh:nn")
       
        SHEET.Cells(1, 8) = w_NM
   
    End With

'---(明細)---
                                    'ソート順、等クエリ側で設定 ※項目=レコードとエクセルの順番を合わせている
    SQL = "SELECT * FROM Q3030_P1DAT;"
   
    Set REC = DB.OpenRecordset(SQL, dbOpenSnapshot)  '読み取り専用
   
    If Not REC.EOF Then             '条件により抽出されない場合がある!
        REC.MoveLast                'カーソル位置を最後へ
    End If
   
'---(タイトル)---

    w_NM = Format(REC.RecordCount, "#,##0") & " 件"

    SHEET.Cells(1, 5) = w_NM
   
'---(明細)---

    '抽出件数を取得し、空の行をあらかじめ作成し、その後データをセットする
    i = REC.RecordCount
    i = i - 1                       '1行分は作成済の為
   
    Y_ROW = 3                       '基準:行
    X_COL = 1                       '  :列

'   (範囲指定でコピーする方法)
  
    If i < 1 Then GoTo sheet_11
                                    'xlPasteValues = -4163, xlNone = -4142, xlPasteAll = -4104
    SHEET.Rows(Y_ROW).Copy
    SHEET.Rows(Y_ROW + 1 & ":" & i + Y_ROW).PasteSpecial Paste:=-4104, Operation:=-4142, SkipBlanks:=False, Transpose:=False

    OBJ.CutCopyMode = False         '切り取りモードを解除する
    OBJ.WorkSheets(1).Select        '複数シートの時は一旦シートを選択しないとセルを選択できない!
    SHEET.Cells(1, 1).Select        '※上記「貼り付け」時は範囲選択も解除する事!(処理が異常に遅くなる)

sheet_11:
   
    If Not REC.EOF Then             '条件により抽出されない場合がある!
        REC.MoveFirst               'カーソル位置を最初へ
    End If
   
    Do Until REC.EOF

        For i = 0 To REC.Fields.Count - 1
            SHEET.Cells(Y_ROW, X_COL + i) = REC(i)
        Next

        Y_ROW = Y_ROW + 1
        REC.MoveNext
    Loop
   
    REC.Close

'******************
'*   P2シート   *   Sheets(2)
'******************

'''    Set SHEET = OBJ.WorkSheets(2)

err_ex_1:                           '『Normal End 共用』
    OBJ.ScreenUpdating = True       '更新:start
    'OBJ.Visible = True
   
    OBJ.DisplayAlerts = False       '確認メッセージなしに保存する-->
    OBJ.WorkBooks(1).SaveAs BOOK_NM2
    OBJ.DisplayAlerts = True        '-->戻す
   
    GoTo sub_ex
   
err_ex_2:
    On Error Resume Next

    OBJ.Application.Quit
   
    w_NM = "『R3帳票』作成出来ませんでした。" _
        & vbCrLf & "同一名のExcelが起動中の為だと思われます。" _
        & vbCrLf & "一旦、終了させてから再度作成して下さい!"
   
    MsgBox w_NM, vbOKOnly + vbCritical, "(R3030) R3帳票"

sub_ex:
    Set SHEET = Nothing
    Set OBJ = Nothing
   
    w_NM = ""
    PATH_COM = "":  PATH_RPT = ""
    BOOK_NM1 = "":  BOOK_NM2 = ""

End Sub


----------

等角フォントでないとキレイになりませんネ!

メモ帳にでも、コピペしたら見やすくなります

アクセス講座 始めるかも、、、

---

明日見むら

村長さんでした

(^^)/

asumi-mura

by. ACCESS EXCEL VBA

---

ACCESSからEXCELへ出力

  ⇒VBA アクセス講座(その2)ひな形の補足

  ⇒VBA アクセス講座(その3)データ定義の違い

  ⇒VBA アクセス講座(その4)共通関数の作り方

  ⇒VBA アクセス講座(その5)システムファイル

  ⇒VBA アクセス講座(その6)EXCELコピペの方法

  ⇒VBA アクセス講座(その7)カレントdb

Windows高速化設定方法

Windowsの設定 デスクトップ環境の移動

---


nice!(0)  コメント(0) 

ACCESS VBA 実行時エラーじゃないけど上手く行かない DoEvents 意外と難しいのネ! [なんちゃって EXCEL ACCESS VBA]

ACCESS VBA 実行時エラーじゃないけど上手く行かない DoEvents 意外と難しいのネ!

いつまで続くか分かりませんが、、、

なんちゃって、プログラミング的な事をカキコして見ようと思います

昨日、前職場に行って来ました

オンライン(サーバー一元管理)では無いデータベース

ACCESS 使って、EXCEL に出力

データはメールで送受信

なんて、意外と有ります

ACCESS はDB(データベース)ソフト

ms さんは、スタンドアローン推奨です

自分のPCだけ、1人ボッチで使ってネって事

よってネットワーク越しにDBを配置するのは好ましく無いデス

イロイロ処理を安定させるのもプログラム上では限界も有ります

実際、上手く行きそうに無い記述がヘルプなどにも書かれています

よって、データをcsv などでメールで受け渡しっとなる訳です

この処理で昨日あった事例

意外と多いのネ

DoEvents

待ってる処理を強制的に実行させます

これ、どんな時に使うのか?

色々あります

例えば、今回の事例では、、、

入力データが200件超

今迄、上手く動いていたのに、EXCEL が全て出力されない事態になりました

1行目だけ一部出力!

では、調査

ボタンを押して、EXCEL出力する所だとは想定できます

モジュール、VBA のプログラムが入っている .mdb を起動します

初期起動でモジュールなどが表示されない時は、Shift を押しながらクリックします

該当のモジュールを開いて

前後の、それっぽい箇所を、左端のスクロールBARへマーク

クリックすれば◯ がマークされます

これを何ヶ所かに入れて、EXCEL出力を実行

で、マークした所で止まります

1行づつ処理を確認するなら、F8、次の処理◯まで実行するなら、F5

大体、これで処理の流れとデータの内部情報が分かります

で、結局、エラーも無く最後まで処理されEXCEL も完成!

では、ナゼ、通常時にEXCEL出力が出来なかったのか!

ここがポイント!

1行づつ処理を確認する

っと言う事は、プログラムのイベントを1つづつ待ちながら実行してるって事です

ザックリ言えば、そんな感じ

だから上手く処理が完了したのデス

windows はその名の通り、ズ、複数形ですネ

人は一つづつ処理していますが、機械はアレコレと仕事をしてます

画面がチラつか無いように画面表示の随時更新を一時的に止めたり

DB を読み込んで EXCEL に出力したり、、、イロイロです

この保留された状態を何処かでリセット、待ちのない状態にする

これが、

DoEvents

っとなるのデス

今迄、上手く行ってたのに上手く出来なくなった

バグ(ミス、不具合)以外で!って時は、これが1番疑わしいデス

では、どうしてそんな事が起きるのか?

理由は様々デスが、、、

Windows XP --> Win7

Office2003 --> 2010

Access2003 のまま

pc も新調

x86(32bit)、x64(64bit)

当然、メモリ、HDD、その他メーカーのゴチャゴチャ仕様のサービス

サービスパックの適用の有無、など色々考えられます

今回は、何らかの原因でメモリ上で待機出来ていたイベント処理が限界を超えたって感じ

意外とよく有ります

逆に、このイベント待ちを解消させた為に起こる不具合も有ります

それは、、、

まぁ、基本的にはバグ

たまたま今まで機嫌良く動いてたって事ですネ

検証するのも中々大変! 運用者さんも!

作るの大変だけど1回だけ、、、

運用は、それからずっと、、、

なるべく時間をかけて、お互い上手く頑張る事

遠慮しないで何でも要望だしたり、聞いたり、、、しましょ

それが良いシステムを作る第一歩

最初から大きく、では無く

ポイントを決め、少しづつ大きなシステムへ、、、

これが私の基本的な考えデス

簡単だけど難しい DoEvents の巻 でした

明日見むら
村長さんでした
(^^)/~~~

asumi-mura
by. Excel Access VBA

nice!(0)  コメント(0) 
なんちゃって EXCEL ACCESS VBA ブログトップ