昔バグった話。
接続しっぱなしでロジックを終わらせてたら、どんどん接続数が増えて処理できなくなった。
CLOSEすんのを忘れてたんです。スイマセン。
で、大騒ぎになる前に。
SELECT * FROM pg_stat_activity ORDER BY usename,application_name;
で、ちゃんとクローズしたことを確認する。
application_nameがpgAdmin以外で、IPアドレスを確認して、current_queryがアイドル状態になっておらず、SQL文が設定されたままだとクローズしていない。
処理が終わってもこういうのが残っていると、ちゃんと切れてないので切断すること。
このバグのせいで、運用問題にまで発展しテンヤワンヤとなったので、メモ。
Excelファイルを開く。
だけど、誰かが開いているから保存はできないよ。
だから処理をやめるね。
ってなことがやりたい！ということで調べてみた。
なんか、Open xxx For Append とか Open xxx For Binary とかで誰かがつかんでることを確認する方法とかあったのだけど、ファイルがちょっと特殊なところにあり、こういうことができない。
それに、パスワード設定しているため、誰かが使っているときにReadOnlyをFalseにして開くと、パスワードまで聞いてくる始末・・・
なので、脳みその10%をフル活用してみた。
'
' 引数で指定されたパスを開いて、開いたブックを返す
' （ここではパスワード設定は省略）
Private Function OpenBook(filePath As String, readonlyFlg as Boolean) As Workbook
Dim fileName As String
On Error Goto ErrorFunc
'(1) とにかく読取専用で開く(確認メッセージとか出さない)
Set OpenBook= Application.Workbooks.Open(Filename:=filePath , UpdateLinks:=False _
, Notify:=False, ReadOnly:=True)
'(2) 読取専用で開くときはここで終わり
If readonlyFlg Then Exit Function
'(3) 開いたブックを読み書きできるようにする(確認メッセージとか出さない)
fileName = OpenBook.Name
OpenBook.ChangeFileAccess Mode:=xlReadWrite, Notify:=False
'(4) なぜか、開き直しになり設定が切れてしまうので、もう一度設定しなおす
Set OpenBook = Workbooks(fileName)
'(5)ファイルが読取専用でなかったら、編集可能なブック！
If Not OpenBook.ReadOnly Then Exit Funtion
'(6) OpenBook.ReadOnly = True なら、誰かが掴んでる！=> 閉じる
OpenBook.Close SaveChanges:=False
Set OpenBook = Nothing
Exit Function
ErrorFunc:
'指定したパスのファイルがないときは、(1)でエラー発生し、ここに来る！
End Function
ということで、回りくどいやり方をすれば何とかできることが分かった。
上の例だと、ファイルがないときも、開けないときもNothingで返してしまうので、区別したいときはフラグを返してやるとか工夫が必要。
ブックを開いた後は
OpenBook.Windows(1).WindowState = xlMinimized
で最小化しておくと、画面のちらつきは最小限で済む。
で、これが「特殊な環境下」でうまく動くかどうかはまだ試せていない。
あぁ、フィルタよ、フィルタよ・・・
結構ややこしかったぞよ。
1つの列に対する複数条件の設定とか、フィルタ解除の方法やら、マクロの記録で出力されないロジックを調べるのに時間がかかった。
1) フィルタの解除
' フィルタが設定されていたら解除しちゃうよ
Private Sub ClearAutoFilter(target_ws as Worksheet)
If target_ws.AutoFilterMode Then target_ws.AutoFilterMode = False
End Sub
2) 1つの列に対する複数条件設定
これが結構難しかった。というより、参考になるサイトがあまりなく、やっとこさで探し当てた。
別のところで配列(Variant型)を作っとく。
たとえば、
Dim arr As Varient
arr = Split(“あ,い,う”)
として、
SetAutoFilterAt Activesheet.Range(“A1:E100”), 1, arr
ってな感じで下のプロシージャを呼び出す。
すると、「あ」「い」「う」に該当するレコードが抽出される。
Private Sub SetAutoFilterOn(target_rng as Range, col as Long, crit_arr as Variant)
target_rng.AutoFilter Field:=col , Criteria1:=crit_arr , Operator:=xlFilterValues
End Sub
試してないから、動かんかったらごめんなさい。
でも、イメージ的にはこんな感じで、Criteria1とOperator の設定がポイントということが分かった。
3) 絞り込んだデータの取得(A) 1行ずつ漁る
Dim cnt As Long
Dim rng As Range
Dim target_rng As Range
Set target_rng = Activesheet.Range("A1:E100")
'target_rngの1行目がタイトル行なので、-1 する。
With target_rng
cnt = .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count -1
If cnt > 0 Then
For Each rng In .Columns(1).SpecialCells(xlCellTypeVisible).Cells
'タイトル行以外で処理する
If rng.Row > 1 Then
'抽出範囲の1列目の情報をダイアログ表示
Msgbox rng.Parent.Cells(rng.row,1).Value
End If
Next
Else
Msgbox "データなし"
End If
End With
ForEach文が1行ずつ見ているところ。
抽出範囲の1列目ってのが、Columns(1).SpecialCells(xlCellTypeVisible).Cells。
4) 絞り込んだデータの取得(B) ワークシート関数でドバッと集計
Dim cnt As Long
Dim target_rng As Range
Set target_rng = Activesheet.Range("A1:E100")
'target_rngの1行目がタイトル行なので、-1 する。
With target_rng
cnt = .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count -1
If cnt > 0 Then
'抽出範囲の2列目の合計を取得(タイトル行は数値でないこと)
Msgbox "合計：" & Cstr(Application.WorksheetFunction.Sum(.Columns(2).SpecialCells(xlCellTypeVisible).Cells))
Else
Msgbox "データなし"
End If
End With
他の内容は割と簡単に調べられるので省略。
改めてExcelVBAって奥深いなぁ・・・って思った。
ってか、マクロの記録でこういうロジックを残してくれたら、悩まずに済むのに！といつも思う。
過去バージョンのダウンロードページを探すのは一苦労する。
Oracle JRE6 ダウンロードページ
http://www.oracle.com/technetwork/java/javase/downloads/java-archive-downloads-javase6-419409.html
Acceptのラジオボタンにチェックしてから、リンククリックしてダウンロード。
Excelファイルを作成するときに、マクロの入ったシートをコピーして作成すると、マクロが残っちゃって困ってしまった。
おまけに、一部のシートしかコピーしてないから、モジュールとかにあるプロシージャとかがないってエラーになってしまう・・・
で、マクロは保存する前に削除しちゃいましょう！なロジックを見つけてきた。
元ネタはこちら
Excelでお仕事! マクロを除いた配布用ブックを作成する。
'======================================================
'ブック上にあるマクロを全部削除しちゃうよロジック
'======================================================
Public Sub DeleteMacroIn(wb As Workbook)
Dim obj As Object '正しい型はVBComponent
Dim lines As Long
For Each obj In wb.VBProject.VBComponents
With obj.CodeModule
lines = .CountOfLines
If lines > 0 Then .DeleteLines 1, lines
End With
Next obj
End Sub
ファイルをWクリックしたときにどっちのバージョンのEXCELを開いてほしいか指定する方法。
コマンドプロンプトで以下を実行
自分の環境はExcel2013（32bit)なので、
“C:\Program Files (x86)\Microsoft Office\OFFICE15\excel.e
xe” /regserver
ファイルパスは32bitと64bitでは違うし、パスを指定してインストールしたらこれとは違うので、自分の環境にあわせて変える。
Excel2003はOFFICE11、2010はOFFICE14、2013はOFFICE15。
SQLServerで縦並びになっているデータを横並び（カンマ区切り）にして出力したくなった。
【TABLE1】
CD | KOMOKU
1 | A
1 | B
2 | C
2 | D
【TABLE2】
CD | CNAME
1 | あ
2 | い
ってあった場合、
CD | CNAME | KOMOKU
1 | あ | A,B
2 | い | C,D
って出したい！ってことで、調べた結果、
select
TABLE2.*
,replace((select KOMOKU AS [data()]
from TABLE1
where (TABLE1.CD=TABLE2.CD)
for xml path('')
),' ',',') AS KOMOKU
from TABLE2
ってな感じに記述するとのこと。
KOMOKUにスペースが含まれているとこれってうまくいかないんだよねぇ・・・とか思いつつ、自分の場合は特に問題なかったのでOK。
[data()] ってのがアトミック値にするっていうことらしいがよくわからんかった。
（分解できないとか、処理速度が速いとか書いてあったけど、結局よくわからん・・・）
ExcelVBAでファイルを起動する方法。
Excelのブックとかでなく、JpegとかTextファイルとかを起動したい場合の記載方法。
(これでやれば、ファイルなら既定のアプリでファイルを開いてくれる)
CreateObject("Wscript.Shell").Run
参考URL
1) 別のやり方も載ってる
moug ExcelVBA 他アプリを起動する
2) 引数について詳しいのはこっち
VBScript Tips & サンプル プログラムを実行する、ファイルやフォルダを開く
自ブック内でHYPERLINKをしたい場合は、
＝HYPERLINK(“#SHEET1!A1″,”LINK>>”)
と＃記号を使う。
また、忘れそうだからメモメモ！
このブログのアクセスって明らかに平日が多い。
記載した技術情報が役に立ってくれれば幸いなのだが・・・
さて、ファイルの所有者ってとってこれんのか？って話。
とってこれるみたい。
Kameya blog「VBScriptでフォルダ・ファイルの所有者の確認を行う」を参考に、ExcelVBAで作成してみた。
'-----------------------------------------------------
' ファイルの所有者を取得する
' 引数 ：ファイルパス(File Only)
'-----------------------------------------------------
Private Function GetOwner(fpath As String) As String
Dim WMIService As Object
Dim objSet As Object
Dim obj As Object
Dim buf As String
'ちゃんと渡してよ！って抜ける
If Len(fpath) = 0 Then Exit Function
Set WMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
buf = ""
'TODO 本当はちゃんとエスケープしないとダメ
Set objSet = WMIService.ExecQuery("ASSOCIATORS OF {Win32_LogicalFileSecuritySetting='" _
& Replace(fpath, "\", "\\") _
& "'} WHERE AssocClass=Win32_LogicalFileOwner ResultRole=Owner")
For Each obj In objSet
buf = buf & obj.AccountName & ","
Next
If Len(buf) > 0 Then
GetOwner = Left(buf, Len(buf) - 1)
End If
Set obj = Nothing
Set objSet = Nothing
Set WMIService = Nothing
End Function
参照したサイトだと記載されていなかったのだが、¥マークは二重にしないといけない。
（常識？いや、CとかC++とかC#とか・・・ならともかく、VB書いてるときは頭がBasicモードになってしまい、忘れてしまうのだ。）
フォルダの所有者を確認するときは、WHERE句が変わるはず。
まぁとりあえず、ぐるぐる回したいときね。
あるんですよ。
鍵のついたキーホルダーの端っこを指にはめ込んで、鍵をぐるぐる意味もなく回したりね。
いや、そんな話でなくて・・・iterator！
ex) 1,2,3,4,5と順に・・・
＜s:iterator status=”stat” value=”{1,2,3,4,5}” ＞
これなんかは、{1,3,6,10} とか括弧の中身を変えてしまうと、また違った使い方ができる。
ex) とりあえず５回
＜s:iterator status=”stat” value=”(5).{ #this }” ＞
(5)ってところを変数つかって(data_max)とかできちゃうよね。
って、forつくりゃいいじゃんか！って思うんだけどね。
まぁ、人それぞれ、言語もそれぞれってことです。
Excelのデータを1行ずつ挿入していくのが面倒なので、シートまるっとINSERTをやってみた。
引数は以下の通り
workbook_path：元データのブック
sheet_name：workbook_pathのどのシートを元データとするのか指定
db_src：DBの場所
db_name：DBの名前
login_id：DBのユーザID
login_pw：DBのユーザパスワード
table_name：DBのどのテーブルを操作するのか（ここではデータの投入先）
Private Function ExportFromXLSX(workbook_path as String,sheet_name as String, db_src as String, db_name as String,login_id as String, login_pw as String, table_name as String) As Boolean
Dim cn as ADODB.Connection
Dim recs_aff as Long
On Error Goto ErrFunc
Set cn = New ADODB.Connection
'2007バージョンのExcelファイルを開く
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & workbook_path & ";" & _
"Extended Properties=Excel 12.0"
'Jetプロバイダを使用してインポート
sql = "INSERT INTO [odbc;Driver={SQL Server};" & _
"Server=" & db_src & ";" _
& "Database=" & db_name & ";" _
& "UID=" & login_id _
& "PWD=" & login_pw _
& "]." & table_name _
& " SELECT * FROM [" & sheet_name & "$]"
cn.Execute sql, recs_aff, adExecuteNoRecords
cn.Close
Set cn = Nothing
ExportFromXLSX = True
Exit Function
ErrFunc:
'エラーメッセージ表示
' ---- (省略) ----
If Not cn Is Nothing Then
If cn.State=1 Then cn.Close
Set cn = Nothing
End If
End Function
こんなことやらなきゃいけないのは、SQLServer側からExcelシートの内容をとってこようとしたときに、分散クエリほにゃほにゃ・・・というエラーが出てしまったからなのだ。
なのでExcelシートから、SQLServer側に送るイメージ。
今日は昨日やったことの続きをやろうと思っていたら、昨日作ったロジックがどうも思い通りに動いていない。
検索絡みだ。
Find関数を使って、セル範囲内の値検索をしたかったのだが、同値のセルを検索してくれないとキタ。
Find(…) → FindeNext() を最初のセル、または戻り値がNothingになるまで繰り返すのだが、どうもうまくいかない。
で、結合セルが含まれているせいで、検索がうまくいかないのかと、結合セルをすべて解除させたシートを一つ作って、Ctrl+Fで検索してみたのだが、やはりうまくいかなかった。
Ctrl＋Fの検索ダイアログでうまくいかないものをマクロでうまくいかそうなどというのは、無駄な抵抗なので、もう行列全体を１つ１つ当たっていくことにした。
With Worksheets(xx)
For c=1 to 100 Step 1
For r=1 to 100 Step 1
If Cstr(.Cells(r,c).Value) = CheckText Then
''' ここに処理 '''
End If
''' ここに下の結合セルのMax行数抽出のサンプル挿入 '''
Next r
Next c
End With
こんな感じに１セルずつ当たっていくわけ。なんとも、面倒な話だが、仕方がない。
しかし、対象セル範囲の中に結合セルがあるので、厄介だ。
というより、結合されている場合は、先頭のセルだけ確認すればよいわけで、検索時間を短縮できると考えた。
下の「結合セルのMax行数抽出のサンプル」は縦結合しかされていないので、横結合の場合はまた変わってくるわけだが・・・
Dim rng As Range '確認対象セルを設定すること
If Not rng.MergeCells Then
'結合されていない場合
min_row = rng.Row
max_row = rng.Row
Else
'結合されている場合
min_row = rng.MergeArea.Cells(1, 1).Row
max_row = SearchMergeRowMin + rng.MergeArea.Rows.Count - 1
End If
次に確認するセルは、同じ列の max_row＋1 となる。
現在仕事でExcel2010を使っているのだが、会社のサーバにあるドキュメントなんぞを開けようものなら、
ダウンロード中・・・
みたいなメッセージが出て、もう異様に遅い。
ってなことで、ググってみた。
そうすると、信頼できる場所にしたら、マシになるかもね！なんて話が。
Microsoft Officeサポート:信頼できる場所を追加、削除、変更する
さっそくやってみたさ。
って、ネットワーク上のフォルダが指定できひんやないかい!!!
ほな、あとは、
[レジストリ エディター] で、次のレジストリを見つけます。
※ レジストリが見つからない場合、手順 “4.” 以降は実施する必要はありません。
HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\11.0\Excel\Security\FileValidation
“EnableOnLoad” をダブル クリックし、値のデータに 1 を設定し [OK] をクリックします。
[ファイル]-[レジストリ エディタの終了] をクリックします。
出典：http://support.microsoft.com/kb/2575312/ja
のあたりを見ていくしかないですなぁ・・・
WindowsXPからWindows7に変えていろいろムカつくことがある。
その1つは、Explorerを立ち上げた時に、お気に入りだのマイドキュメントだのが、どで～んと拡がること。
あのね、そんなところにファイル置きませんから。
まったく、余計なことしやがって・・・
ということで、起動時に拡げたいフォルダをDドライブのほうにしてやる。
Explorerのプロパティを開いて、explorer.exe の後ろにほにゃほにゃと追加
「%windir%\explorer.exe /e, /n, D:\」
適用させて、Explorerを立ち上げれば、Dドライブが見えて、他は縮んだままになってる。
おせっかいはほどほどにしてほしいものだ。
検索の入力欄に、Ctrl＋Jで設定（見た目わからないけど、改行コードが入っている）
↓
検索ボタンクリック
↓
改行コードのあるセルが選択される
見た目わからないのが困るんです。
副クエリーを使ったUPDATEの方法。
扱っているブログは結構あるものの、複数項目をUPDATEする方法について書いてあるものが少ない気がする。
UPDATE table1 t1 SET (data1,data2,data3)=(t2.data1,t2.data2,t3.data3) FROM table2 t2 WHERE t1.xx_cd = t2.xx_cd;
Application.Workbooks.Add
で新規ブックを作ることはできるのだが、2003バージョンで上限が6.5万行ぐらいのシートができてしまった。
これを強制的に100万行対応のファイルにできないものかと思っていたところ、
Dim def As XlFileFormat ‘DefaultSaveFormat 保管場所
With Application
def = .DefaultSaveFormat
.DefaultSaveFormat = xlExcel12 ’50 Excel2007-
.Workbooks.Add
End With
というふうにApplication.DefaultSaveFormatプロパティを一時的に変えてから新規ブックを作成してやればよいことがわかった。
処理の最後に、また元の値(def)に戻してやればよい。
HashMapの繰り返し処理の方法
// LinkedHashMapの繰り返し(同値が見つかったらキーを返す)
private String getKey(LinkedHashMap<String,String> hmap
, String targetVal) {
for (Map.Entry<String, String> ent : hmap.entrySet()) {
if (ent.getValue().toString().equals(targetVal)) {
return ent.getKey().toString();
}
}
return null;
}
今年に入ってから忙しくてGoogle Analyticsを確認できていなかったのだが、どうも2014年1月からの集計ができてなかった。
できていない理由はよくわからないが、それまでWordPressのプラグインを使ってて全然問題もなかったのに、いきなり使えなくなってしまった。
プラグインが古くて使えないのか？、もうこのプラグインは使えないのか？と、別のプラグインで試してみたのだが、結局集計できずじまい。
もう、仕方なく、テーマのヘッダーファイルに無理やりスクリプトを記載して保存した。
不安だから、GoogleのTag Managerも登録してみた。
（でも、Tag Manager自体よくわからない。）
ら・・・うまくいった。
で、結局何が原因なのかはわからないが、＠Pagesの広告がGoogle広告に変わったからかなぁ・・・などと思ったりする。
＜覚書＞
自分のアクセスは集計から除外する
http://11dax.com/google-analytics-2-460.html
自分のIPアドレスを教えてくれるサイト
http://www.cman.jp/network/support/go_access.cgi