月: 2014年8月

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

とうとう、そういう歳になってしまった。

が、迷い戸惑い、怒りに身を震わせる自分が相変わらずここにいる。

で、Agora 「不惑」という困惑 という記事を読んだ。

まぁ、歳なんて関係ないのよ。西洋にはそんな考え方ないし・・・みたいな感じ。
こういう考え方ができると楽だよねぇ・・・とか思うのだが、本当にそれでよいのかとも思う。

自分は独身で子供もいないけど、周りにいる同世代、または親である人たちで、そりゃいい親だとか、ちゃんと育てているなと思える人たちはいっぱいいるが、そうも思えない人もいる。
ほっときゃいいじゃない、って思うかもしれないが、ほっといて本当によいのだろうか。
放置して、自分に跳ね返ってきたら、あの時ほったらかしにしたことを後悔しないかね。
（まぁ、関係ないって思っているのだったら、後悔なんかしないのかもね。）

でも、マンションの隣の部屋で餓死した人がいたらどうかな。
なんかできなかったかな、自分・・・とか思うんじゃないの。

仏教には受容という言葉があるけれど、何でもかんでも受け入れるということではない。
やっぱり自分で考えて、人の意見も聞いて、かみ砕いて消化する必要があると思う。
そして、ダメなものは駄目だし、「ならぬものは、ならぬ」。

まぁ、そんな当てもなく、いろいろ思いつつ、今日は早く仕事を終わらせるのが、今の私のメイン課題なのである。

ファイルを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()] ってのがアトミック値にするっていうことらしいがよくわからんかった。
（分解できないとか、処理速度が速いとか書いてあったけど、結局よくわからん・・・）