タグ: Excel

SQLを実行してきてデータを取得するまではよいのだが、その後シート上に出力するときに異様に時間がかかる場合がある。
で、端末個体の理由は置いといて、複数のワークブックを開けていた場合に起こるのであれば、対策は、

　処理中は必要なとき以外「自動計算」させない！

ということらしい。

With Application
    .ScreenUpdating = False
    .Calculation =xlCalculationManual
    .EnableEvents = False
End With

としておけば、
　画面を更新せず、
　再計算させず、
　イベント有効にしない
とできるわけである。
これで、余計なイベントも発生しないので良いのだが、再計算してほしいときにはCaluculateメソッドを発行しておかねばならない。

で、Application.Calculation = xlCalculationManualをせず、シート上にバサッと出力したいなぁ～などと思っていたら、

　WorksheetFunction.Transpose()

シート関数のTRANSPORT関数を使って、引数に出力したい配列を設定することで、ドバっとあっという間に出力できてしまうやり方を紹介しているページがあったので、ありがたやありがたやとやってみたところ、本当にあっさりとドバっと出力された。

参考URL：エクセルの神髄「マクロVBAの高速化・速度対策の具体的手順と検証」

入れる配列は（横、縦）の状態にして、Transport関数で縦横逆にして出力！ってなことになっている。
ありがたやありがたや！

ワークシート上にある「SHP1」という名前の図形をコピーして、「SUB_SHP1」という名前を付け、B2セル内に配置するロジックを考えてみた。
コピー元の図形のDuplicateメソッドをつかって、新しい図形(コピー先:以下サンプルでは変数「shp」のこと)を作り、その新しい図形のプロパティをいじって、図形の位置とか、図形の名前、図形に紐づくマクロの設定などを行う。
サンプルでは、セルB2の下部中央に配置する方法。

Dim ws  As Worksheet
Dim shp As Shape

Set ws = Thisworkbook.Worksheets("Sheet1")
Set shp = ws.Shapes("SHP1").Duplicate
With shp
  .Top = ws.Range("B2").Top - .Height -5
  .Left = ws.Range("B2").Left + (ws.Range("B2").Width - .Width) / 2
  .Name = "SUB_SHP1"
  .Visible = msoTrue
  .OnAction = "RunXXX"
End With
Set shp = Nothing
Set ws = Nothing

図形のコピーに「Duplicate」を使うというのは初めて知った。duplicateの意味が「複写する」ってことらしい。

Blogの外観をちょいと変えてみた。また変えるかも。

さて、Windows10でExcel2010とか2013とかを使うようになって、Windows7で作ったExcelマクロのActiveXコントロール（コンボボックス）がうまく表示されないようになった。

これは困った・・・

グループ化すれば治る・・・って、治んない。

困りましたな・・・

ってことで、もうフォームコントロールに変えることにした。
面倒だわ・・・
ActiveXコントロールだと、リストの情報はマクロ上で設定すればいいのだが、フォームコントロールはリストの情報をセルに持たせて、そのセルとリンクを貼る・・・という厄介な手順を踏まなければならない。

ということで、ActiveSheet上にあるフォームコントロール「DLIST」のリストにしたい情報が、同じシートのセル「A2～A10」にあるとすると、

With ActiveSheet.Shapes("DLIST").ControlFormat
    .ListFillRange = "A2:A10"
    .LinkedCell = "B2"
End With

参照リストセルが別のシート(仮に「Sheet2」とする)にある場合、ListFillRange の設定をちょっと変える必要がある。

    .ListFillRange = "Sheet2!A2:A10"

でも、コントロールのフォントサイズの変え方がわかんない・・・
もうヤダ。

Shell関数を使って別のexeを起動。

できんかった・・・・・・

なので調べた。

どうやら、カレントディレクトリをexeのある場所に指定しないとダメな様子。
で、ChDir関数を使ってカレントディレクトリの変更！ってやってみたけど、うまくできなかったので、別の方法でカレントディレクトリを変える。

以下は、Workbookと同じフォルダにあるtest.exeを実行してみるサンプル。
起動できたら、Excelを最小化させる

    Dim p   As String
    Dim res As Integer
    
    On Error Resume Next
    p = ThisWorkbook.Path
    If Right(p, 1) <> "\" Then p = p & "\"
    With CreateObject("WScript.Shell")
        .CurrentDirectory = p    // カレントディレクトリを変える
    End With
    res = Shell("test.exe", vbNormalFocus)
    If res <> 0 Then Application.WindowState = xlMinimized

Windowsのログインユーザアカウントをとってきたい！ってのを調べてロジックに組み込んだはいいが、このブログ上にメモしてなかった。

まぁWindowsにログインするときに入力するアカウントのことなんだけど、環境変数関係の情報を取得するときは、Environ関数を使う。

Environ(“USERNAME”)

と、こんな感じ。

引数にどんなものがあるか、どんな戻り値になるか、デバッグして調べてみるときは、こんな風に。

Debug.Print "OS：" & Environ("OS")
Debug.Print "デフォルトドライブ：" & Environ("HOMEDRIVE")
Debug.Print "デフォルトユーザパス(ドライブなし)：" & Environ("HOMEPATH")
Debug.Print "tempフォルダパス：" & Environ("TEMP")
Debug.Print "ログインユーザID：" & Environ("USERNAME")
Debug.Print "systemフォルダパス：" & Environ("WINDIR")

基本的にScripting.Dictionaryは、

Dim dic     As Object

Set dic = CreateObject("Scripting.Dictionary")
If Not dic.Exists(a) Then
  dic.Add a,b
End If

みたいに、Existsメソッドを利用することが多いのだが、
やはり１つずつ確認して処理を行うこともなくはない。

Dim i As Long
For i = 0 To dic.Count - 1 Step 1
    debug.print dic.Keys()(i) & "-" & dic.Item(i)
Next i

もしくは、

Dim v As Variant
For Each v In dic.Keys()
    debug.print CStr(v) & "-" & dic(v)
Next

で、いずれも、Keys() と両括弧を付けるところがポイント。

色々サイトを見てみたのだが、この()が抜けていたり、ItemがItemsになっていたりして、混乱している記載が多く、ここまでたどり着くのに英語のサイトまで見に行ってしまった。

非常にやっかいだが、もともとこういう使い方するようなオブジェクトではないのだろうから、仕方ない。

ちなみに削除するときはRemoveを使う。（Deleteではない）

Dim v As Variant
For Each v In dic.Keys()
    dic.Remove v
Next

ファイル渡すからプログラム組んでくれ、っていわれてプログラム作成している最中、
ファイルも寄越さないうちから、「進捗どうですか？」って聞かれて、ちょいムカっとしたのは今週の始め。
んで、ムカついたからもう少し待ってねメールのついでに、送ってきた中途半端な仕様書の「重箱の隅にもならない場所」(ちゃんと書いて！ってな場所）をつつくような質問を畳みかけてやった。（フン！）

まぁ、別に大して怒っている訳ではないのだが、
　「自分がやるべきこともしないで、なんなんだ？」とか、
　「いやいや○日かかるって言ってるでしょ！何日目だよ今日！」とか・・・
色々思うところはあった。
そうは言いつつ、自分もこうならないように気を付けようと、ちょいと思ったのだった。

で、慣れないDoCmdに苦戦しつつ、ExcelファイルをReadOnlyで開いて、データを読込み、終わったら閉じる処理を書いてみた。
DisplayAlertsをFalseにしているのは、Excelで発生したエラーやら確認メッセージを出させないためだけで、値をとってくるだけなら特に必要はないと思う。

Dim xls As Object
Dim wb  As Object

Set xls = CreateObject("Excel.Application")
xls.DisplayAlerts = False
Set wb = xls.Workbooks.Open(FileName:=[Excelパス], ReadOnly:=True)

 ・・・（読込処理）・・・

wb.Close SaveChanges:=False
Set wb = Nothing 
xls.DisplayAlerts = True
xls.Quit
Set xls = Nothing

面倒だなぁ・・・Excelでいいじゃん？
だって、xlUpとかxlToLeftとかのExcelVBA特有の定数も使えないし。
（参照設定で設定すれば、使えるようにはなるけどサ）

とは思ったのだが、まぁ、後々Accessの方がいいこともあるかもって思ったので、お客さんの言うとおりAccessにした。
だけど、やはり、今回は、Accessにデータを取込むわけでもなく、別DBに出力するロジックだったので、尚更、AccessでExcelを開くというのは、かなり馬鹿馬鹿しく感じた。
もちろん、できるならImportしたいところだが、できるほど単純なデータではなかったので、わざわざロジックを組む。

　　・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・

で、今回は読み込んだデータをAccessテーブルには放り込まなかったのだが、Accessに取込むとなるとどうするのか・・・ということも考えてみた。

しっかし、AccessのTableってのは、INSERT文で出力しようが、AddNew・Updateで入れてみようが、まぁ、遅いのなんの・・・
さらに複数のSQL文を一括実行する方法なんて探してみたのだが、

Accessにはない！

ということなので、１つ思いついたのが、
　・Excelデータを取込んで、テーブルImport用のCSVを作成。
　・CSVをImportする。
という方法。
確かに、INSERTを発行するより速いのだが、かといって、すこぶる速いという訳でもなかった。件数が多ければ、明らかに速くなる気はする。

しかし、ここで問題発生。文字コードだ。

UTF-8文字が入力されたExcelデータであったため、ADO.Streamを利用して、BOM無しのUTF8でCSV出力。
かつ、インポートするときも

　DoCmd.TransferText acImportDelim, , temp_table , csv_path, True, , 65001 　　

というように、文字コード(65001がUTF-8を示す)を指定して取込む必要があった。

ということで、いろいろ試しにやったのだが、プログラム自体はすんなり作成できた。

ってか、まだファイル来ないし・・・

略すとPPAPみたいになってるけど、ExcelのVBAプロジェクトのパスワードがわからないとき（忘れたとき）は、それを外すやり方がある。

何だっけ・・・

と思って調べた。

ここでは詳細は書かないけど、検索ワード的には
　Excel, VBA, DPB=
といったところか。

DPB=のダブルクォーテーションで括られた中身が暗号化?されたPWで、それをうまいこと置換えるわけなんだけど、バイト数が合わないとVBA関連のファイルが潰れちゃうので注意しなくてはならない。
なので、バイト数のきっちり合った置換文字を作成するため、新規ExcelファイルにVBAパスを付けて保存した後バイナリファイルの中身を確認する。
（パスワードの桁数からDPBの中身の桁数が容易にわかるわけではないので、色々作って合致した！ってなパスワードが見つかってから置換えるわけ。ちょいと面倒なのだが、熱さ過ぎればチョメチョメチョメと。）

あらら、結構書いてしまったぞい。
忘れたときのためなので、悪用するべからず！

Dir関数でファイルがないってときに、
　ただファイルがないのか、ネットワークにつながってないのかわかんないの？
　Ping飛ばしてネットワークにつながってるのか調べたらいいじゃない？
みたいな要件があって、まぁ、正直、「そこまでやるか？」って思ったけど、そういう環境で動いている人は、そういう発想になるんだろうなぁ・・・と思って、教えてもらったPing送信術をアレンジしてExcelに埋め込んでみた。

Public Function CheckNetwork(ip As String) As Boolean
    
  Dim wsh As Object
  Dim buf As String
    
  On Error GoTo ErrCheck

  Set wsh = CreateObject("WScript.Shell")
  wsh.Run "%ComSpec% /c ping -n 1 " & ip & " | clip ", 0, True
  buf = GetObject("\" _
               , "htmlfile").ParentWindow.ClipboardData.GetData("text")
  If InStr(buf, "ラウンド トリップの概算時間") > 0 Then
      CheckNetwork = True
  End If
  Exit Function
ErrCheck:
  err.clear
End Function

ちなみに、「%ComSpec%」は、「%SystemRoot%\system32\cmd.exe」のことらしい。
「 | clip」でクリップボードに入れて、それをbufに読み込むようにした。
また、Shellは、Execで動かす方法とRunで動かす方法があるが、Runのほうが、コマンドの窓が表示されないようにできるので、こちらを採用。
面倒な要件は、解決できると嬉しい。
解決しないと、逆恨みしそうだけど。

あるよねぇ～～～～
ってことで、

Sub ProtectSheet
    With ws
        If Not .ProtectContents Then         'ロックされていないときだけロック処理
            .Protect Password:="password"
                   , DrawingObjects:=True
                   , Contents:=True
                   , Scenarios:=True _
                   , AllowFiltering:=True     'ここでフィルターOKにする
        End If
    End With
End Sub

以下は試していないけれど、これでもOKらしい。

Sub ProtectSheet2
    With ws
        If Not .ProtectContents Then         'ロックされていないときだけロック処理
            .Protect Password:="password"
                   , DrawingObjects:=True
                   , Contents:=True
                   , Scenarios:=True _
                   , userInterfaceOnly:=True
        End If
        .EnableAutoFilter = True
    End With
End Sub

セル選択＆移動させる方法はいろいろあるけど、一番手っ取り早いのはApplication.Gotoのようだ。

Sub ActivateRange(rng as Range)
    On Error Resume Next
    Application.Goto rng, True
    err.clear
End Sub

１つ目の引数は選択セル、２つ目の引数はスクロールして移動するかどうか、らしい。
選択だけだったら、rng.selectでもいいように思うけど。

これを使わずに選択させようとなると、

Sub ActivateRange(rng as Range)
    On Error Resume Next
    rng.parent.parent.Activate
    rng.parent.Activate
    rng.select
    err.clear
End Sub

みたいなかんじで、ブック→シート→セルをアクティブにしないといけないわけで、これは面倒。
（親がアクティブになっていないに、子をアクティブにはできないのだ）
楽にできる関数があるということで、メモ。

このパスはローカルなのかネットワークサーバなのか・・・
とりあえず判定ロジックを作ってみた。

'ローカルならTrue、ネットワークならFalse
Public Function CheckLocal(filepath as String) As Boolean
    
    Dim cap As String
    Dim fso As Object
    Dim obj As Object
    
    '先頭が\なら確実にネットワーク
    cap = Left(filepath, 1)
    If cap = "\" Then
        Exit Function
    End If

    'ドライブ割当のローカル/サーバ確認
    Set fso = CreateObject("Scripting.FileSystemObject")
    For Each obj In fso.Drives
        If obj.DriveLetter = cap Then
            If obj.DriveType = 3 Then
            Else
                CheckLocal = True
            End If
            Exit Function
         End If
    Next
    'ここまで来たらどこかは不明
End Function

久方ぶりの投稿。いろいろあったけど、それはまた別に書くとして、CSVファイルを読み込むときの高速なやり方を探してたらQueryTablesを使った方法というのがあったので、やってみた。

    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = Workbooks.Add
    Set ws = wb.Worksheets(1)
    With ws.QueryTables.Add(Connection:="text;mega.csv", Destination:=ws.Range("A1"))
        .Name = "megacsv"
        .TextFileCommaDelimiter = True
        .Refresh
    End With

すごかった。30万行一瞬。

印刷範囲指定をしてウィンドウ枠を固定すると、アクティブセルのある領域以外の図形がちらついて見える事象。
Excel2010でしか起きないらしい当該事象だが、さてどうしようかと考えると、結局は印刷範囲指定をやめる結論に至った。
解決作的には根治してもらわなければならないのだが、MSさんが重い腰をあげないので、
自分で腰を上げるとするなら、例えば、印刷ボタンを作って、ボタンを押したときだけ印刷範囲指定→印刷→印刷範囲クリアすればよいのかなぁと考えた。
でも、厳密に印刷設定を行う必要性がないのであれば、印刷の拡大率を設定しておくだけでも何とかなるような気もする。

忘れる前にメモっとく。
VBAからコマンド実行を非同期で行う時に使うShell関数。
第2引数にvbMinimizedNoFocusを設定すると、コマンドプロンプトは非表示になって、裏側で走る仕組みにできるそうな。

Dim cmd_buf As String
Dim res     As Double

cmd_buf = "cmd /c sqlcmd -S {DB-Source} -U {DB-UserId} -P {DB-UserPw} -d {DB-Name} " _
         & " -Q ""EXEC {プロシージャ名} {*}, '{*}' """ 
res = Shell(cmd_buf, vbMinimizedNoFocus)

{}書きは環境に合わせて変更
{*}はストアドの引数で、文字列の場合はシングルクォーテーションで囲む。

Shellの戻り値はタスクIDで、「0」がかえってきたら動いていないということらしい。
非同期なので勝手に動いて、勝手に終わる。

終わったらウィンドウをアクティブにするとかすると、終わったことがわかるらしい。

If res > 0 Then Call AppActivate(res)

と、こんな感じかね。
ふむふむ、しかしだね、裏側で勝手に動いているわけで、下手にPCシャットダウンしちゃうと、処理が途中で終わっちゃう！ってところが怖い。

で、次。
リモート環境にあるバッチファイルを実行する方法。

'server：リモートサーバ(user_id/user_pwでログイン)
'exe_name：実行ファイル, exe_position：実行ファイルのパス
Public Function ExecuteRemoteExe(server As String, user_id As String, user_pw As String _
                                , exe_name As String, exe_position As String) As Boolean

    Const AUTHENTICATION_LEVEL_PKT_PRIVACY = 6
    
    Dim obj_locator As Object
    Dim obj_server  As Object
    Dim obj_process As Object
    Dim res As Long
    Dim pid As Long
    
    On Error GoTo ErrExecute
    
    Set obj_locator = CreateObject("WbemScripting.SWbemLocator")
    Set obj_server = obj_locator.ConnectServer(server, "root\cimv2", user_id, user_pw)
    obj_server.Security_.authenticationLevel = AUTHENTICATION_LEVEL_PKT_PRIVACY
    
    Set obj_process = server.get("Win32_Process")
    res = obj_process.Create(exe_position, Null, Null, pid)

    Select Case res
        Case 0
            ExecuteRemoteExe = True
        Case 2
            Msgbox "アクセスできませんでした。"
        Case 9
            Msgbox "パスが正しくありません。" 
        Case 21
            Msgbox "パラメータが正しくありません。"
        Case Else
            Msgbox "バッチを実行できませんでした。"  
    End Select
        
ErrExecute:
    
    Set obj_process = Nothing
    Set obj_server = Nothing
    Set obj_locator = Nothing
    
End Function

これでリモートのバッチファイルを実行することができる。
できるんだけど、これもこっち側のPCを切ってしまうと、バッチも終わってしまう・・・
なんとかならんか。

モノを整理するのにモノを買う・・・
という、断捨離しているはずなのにモノを増やす、愚かしいことをやってしまった。

いや、何にせよ、今のままじゃイカン！と、虚無感たっぷりに自分に活を入れつつ。

ExcelのRangeオブジェクトのメソッドにあるResize。起点セルと行列数を指定して範囲指定するのに使っているようだ。
こんなメソッドがあるとは全く知らなかったが、ネットサーフィンしてたらたまたま見つけたので試してみる。

参照サイト：Range,Cells」と「Resize」のセル範囲指定を比べてみる

パターン1：ごくごく一般的な範囲指定

With ThisWorkbook.Worksheets(1)
    .Range("C2:E4").Select
End With

パターン2：一般的なはずな範囲指定

With ThisWorkbook.Worksheets(1)
    .Range(.Cells(2, 3), .Cells(4, 5)).Select
End With

パターン3：「Resize使って同じことしてみるよ」な範囲指定

With ThisWorkbook.Worksheets(1)
    .Cells(2,3).Resize(3,3).Select
End With

パターン4：「Offset使って同じことしてみるよ」な範囲指定

With ThisWorkbook.Worksheets(1)
    .Range(.Cells(2,3),.Cells(2,3).Offset(3,3)).Select
End With

パターン1から4まで、すべて同じ範囲が指定される。（つまり、セルC2～E4）
なるほど、少なくともパターン4よりは、パターン3の方がきれいだ。
パターン2と3、どちらを使うかは、周辺のロジック次第。

なお、参照サイトには「Rangeが参照しているシートがアクティブになっていないとエラーになる」と書いてあるが、実はどのメソッドでも同じ。Selectメソッド自体、アクティブシート上にでないと動かないので、ResizeやOffsetとは関係がないと思う。

シートを保護する、解除するというのは、

Worksheets(“Sheet1”).Protect
Worksheets(“Sheet1”).Unprotect

という、メンバー（サブプロシージャ）でできるのだが、Protectするときの引数に、「UserInterfaceOnly」というのがあり、これをTrueにしておけば、シート保護の解除をしなくても、マクロでシート上のデータを変えられるそうな。

Worksheets("Sheet1").Protect UserInterfaceOnly:=True

なので、いままでわざわざシート保護を解除して処理をしていた自分が情けなくなった。
（何年VBA使ってんだよ！と自分を責めてみる。）

まだまだ知らないことがあるなぁ。

この引数を指定しない場合は、ちゃんと保護解除をしてから触ること。

Optionalで指定した引数は省略可能だが、設定されて呼び出されているかどうかを確認する関数がIsMissingだそう。
しかしながら、この変数の型がVarient型のときにしか機能しないというお粗末もの。

Private Sub Test (Optional a As Varient)
    If Not IsMissing(a) Then
        '設定されているとき
    Else 
        '設定されていないときのロジック
    End If
End Sub

下のようにLong型にすると、a＝0と判断されてしまうので×。

Private Sub Test (Optional a As Long)
    If Not IsMissing(a) Then
        '全部こちらに入ってしまう
    Else 
        'こっちに来ない
    End If
End Sub

ということで、省略時の初期値を設定しておき、条件分岐をする。

Private Sub Test (Optional a As Long = -1)
    If a>0 Then
        '省略されていないとき
    Else 
        '省略されたとき
    End If
End Sub

指定したURLを開く。(イベント用)

' url : リンク先
Private Sub GotoLink(url As String)
    On Error Resume Next
    ThisWorkbook.FollowHyperlink Address:=url
End Sub

セルに指定したURLのハイパーリンクを設定する。

' rng  : 設定セル , description : 表示文言, url : リンク先
Private Sub SetHyperlink(rng As Range, description As String, url As String)
    On Error Resume Next
    rng.Hyperlinks.Add Anchor:=rng , Address:="", SubAddress:=url, TextToDisplay:=.description
End Sub

ハイパーリンクを削除する。

' rng  : 設定セル 
Private Sub DeleteHyperlink(rng As Range)
    On Error Resume Next
    rng.Hyperlinks.Delete
End Sub

dqyファイルは便利だ。
わざわざ、PhpAdminやらManagerやら開けずにSQL文を実行して、データが確認できる。

自分がよく使うPostgreSQLとSQLServerのクエリファイルの書き方メモ。

PostgreSQL

XLODBC
1
Driver=PostgreSQL Unicode;Server={IPアドレス等};Port={PortNo};Database={DB名};Uid={UserID};Pwd={Password};
SELECT TOP 100 * FROM batch_log ORDER BY create_date desc

SQLServer

XLODBC
1
Driver=SQL Server;Server={IPアドレス等};Uid={UserID};Pwd={Password};Database={DB名};Connect Timeout=15;
SELECT * FROM batch_log ORDER BY create_date desc LIMIT 1;

SELECT文は改行すると動作しないときがあるので注意