devlog

ExcelVBA／列幅自動調整とオートフィルタ

2020年08月05日

列幅の自動調整は以下のような感じで行う。
┌──────────────────────────────────────┐
│With シート                                                                 │
│    .Columns.AutoFit                                                        │
│End With                                                                    │
└──────────────────────────────────────┘
ところが、あるシートで実行する度に幅が広がったり狭まったりする現象に出くわした。

┌──────────────────────────────────────┐
│With シート                                                                 │
│    .Columns.AutoFit                                                        │
│    .Rows("1:1").AutoFilter                                                 │
│End With                                                                    │
└──────────────────────────────────────┘
原因はオートフィルタだった。
オートフィルタがONの状態で列幅自動調整をすると広がり、
オートフィルタがOFFの状態で列幅自動調整をすると狭まっていたのだ。
しかも、「.AutoFilter」はトグル的なメソッドなので、これと組み合わさって
処理を実行する度に幅が広がったり狭まったりする現象を引き起こしていたのだ。

┌──────────────────────────────────────┐
│With シート                                                                 │
│    If .AutoFilterMode Then  '■処理追加                                    │
│        .Cells.AutoFilter    '■処理追加                                    │
│    End If                   '■処理追加                                    │
│    .Columns.AutoFit                                                        │
│    .Rows("1:1").AutoFilter                                                 │
│End With                                                                    │
└──────────────────────────────────────┘
ということで、いかなる場合もオートフィルタがOFFの状態で
列幅自動調整をするように処置。(これで狭い方オンリーになる)

分類：ExcelVBA

MSSQL／所有者を確認

2020年07月30日

データベースの所有者を確認するSQL文。
┌──────────────────────────────────────┐
│SELECT [データベースDB].[name] AS [データベース],                           │
│       [プリンシパルDB].[name] AS [所有者]                                  │
│    FROM [sys].[databases] AS [データベースDB]                              │
│        LEFT JOIN [sys].[server_principals] AS [プリンシパルDB]             │
│          ON [データベースDB].[owner_sid] = [プリンシパルDB].[sid]          │
│    ORDER BY [データベースDB].[database_id] ASC;                            │
└──────────────────────────────────────┘

分類：MSSQL

MSSQL／ユーザ権限

2020年07月28日

階層関係は以下の通り
DBサーバ→インスタンス→ログイン→ユーザ(→ロール)→データベース

詳細は以下の通り(●■は読取専用ユーザの設定を想定)

ログイン
├○Windows認証
└●SQLServer認証

サーバロール
├□sysadmin           …全部
├□serveradmin        …サーバ
├□securityadmin      …ロール
├□processadmin       …プロセス
├□setupadmin         …リンクサーバ
├□bulkadmin          …BULK INSERT
├□diskadmin          …ディスクファイル
├□dbcreator          …データベース更新
└■public             …

データベース
├□master             …システムデータベース
├□model              …  〃
├□msdb               …  〃
├□tempdb             …  〃
└■(ユーザDB)

固定データベースロール
├□db_owner           …全部
├□db_securityadmin   …ロール
├□db_accessadmin     …アクセス
├□db_backupoperator  …バックアップ
├□db_ddladmin        …定義(CREATE,ALTER)
├□db_datawriter      …更新(INSERT,UPDATE,DELETE)
├■db_datareader      …参照(SELECT)
├■db_denydatawriter  …更新の禁止
├□db_denydatareader  …参照の禁止
└■public
※msdbの場合はさらに以下もある
├□db_ssisadmin
├□db_ssisltduser
├□db_ssisoperator
├□dc_admin
├□dc_operator
├□dc_proxy
├□DatabaseMailUserRole
├□PolicyAdministratorRole
├□ServerGroupAdministratorRole
├□ServerGroupReaderRole
├□SQLAgentOperatorRole
├□SQLAgentReaderRole
├□SQLAgentUserRole
├□TargetServersRole
├□UtilityCMRReader
├□UtilityIMRReader
└□UtilityIMRWriter

分類：MSSQL

SQL／先入先出法の在庫シミュレーション

2020年06月20日

入庫レコードと出庫レコードを先入先出法で紐づける場合には
出庫側を両者の重複区間とすればよい。(例はMSSQL)
┌──────────────────────────────────────┐
│WITH [入庫Ｄ]([ID],[数]) AS (                                               │
│         SELECT * FROM (VALUES (1, 3),                                      │
│                               (2, 3),                                      │
│                               (3, 3)) AS [入庫Ｄ]([ID],[数])               │
│     ),                                                                     │
│     [出庫Ｄ]([ID],[数]) AS (                                               │
│         SELECT * FROM (VALUES (1, 1),                                      │
│                               (2, 4)) AS [出庫Ｄ]([ID],[数])               │
│     )                                                                      │
│SELECT [入庫Ｄ].[ID] AS [入庫ID],                                           │
│       [出庫Ｄ].[ID] AS [出庫ID],                                           │
│       CASE                                                                 │
│         WHEN [出庫Ｄ].[始] < [入庫Ｄ].[始] THEN [入庫Ｄ].[始]              │
│         ELSE                                    [出庫Ｄ].[始]              │
│       END AS [始],                                                         │
│       CASE                                                                 │
│         WHEN [出庫Ｄ].[終] > [入庫Ｄ].[終] THEN [入庫Ｄ].[終]              │
│         ELSE                                    [出庫Ｄ].[終]              │
│       END AS [終]                                                          │
│    FROM (                                                                  │
│        SELECT [ID],                                                        │
│               ISNULL((SELECT SUM([数])                                     │
│                           FROM [入庫Ｄ] AS [他Ｄ]                          │
│                           WHERE [他Ｄ].[ID] < [入庫Ｄ].[ID]),              │
│                      0) + 1 AS [始],                                       │
│               (SELECT SUM([数])                                            │
│                    FROM [入庫Ｄ] AS [他Ｄ]                                 │
│                    WHERE [他Ｄ].[ID] <= [入庫Ｄ].[ID]) AS [終]             │
│            FROM [入庫Ｄ]                                                   │
│    ) AS [入庫Ｄ]                                                           │
│        LEFT JOIN (                                                         │
│            SELECT [ID],                                                    │
│                   ISNULL((SELECT SUM([数])                                 │
│                               FROM [出庫Ｄ] AS [他Ｄ]                      │
│                               WHERE [他Ｄ].[ID] < [出庫Ｄ].[ID]),          │
│                          0) + 1 AS [始],                                   │
│                   (SELECT SUM([数])                                        │
│                        FROM [出庫Ｄ] AS [他Ｄ]                             │
│                        WHERE [他Ｄ].[ID] <= [出庫Ｄ].[ID]) AS [終]         │
│                FROM [出庫Ｄ]                                               │
│        ) AS [出庫Ｄ]                                                       │
│          ON  [出庫Ｄ].[始] <= [入庫Ｄ].[終]                                │
│          AND [出庫Ｄ].[終] >= [入庫Ｄ].[始]                                │
│    ORDER BY [入庫Ｄ].[ID] ASC,                                             │
│             [出庫Ｄ].[ID] ASC;                                             │
└──────────────────────────────────────┘

分類：SQL

ExcelVBA／QRコード作成

2020年05月14日

【必要なもの】
  Excel2013以降とAccessRuntime2013以降
  両者のbit数は同じでなければならない
  →ActiveXコントロールに「Microsoft BarCode Control 15.0」が表示されればOK

【AccessRuntimeの入手先】
  Microsoft Access 2013 Runtime
    https://www.microsoft.com/ja-jp/download/details.aspx?id=39358
  Microsoft Access 2016 Runtime
    https://www.microsoft.com/ja-jp/download/details.aspx?id=50040

【追加方法】
  「Microsoft BarCode Control 15.0」からオブジェクトを追加する
  →通常のバーコードが追加される

  それの右クリックメニュー「Microsoft BarCode Control 15.0 オブジェクト」の
  「プロパティ」を表示する
  その中の「スタイル」を「11 - QRコード」に変更
  →形状がQRコードになる

  右クリックメニュー「プロパティ」中の「LinkedCell」にセル番号を入れるか
  「Value」に直接値を入れるかする
  →QRコードの内容に反映される

分類：ExcelVBA

Python／Webスクレイピング(BeautifulSoup)

2020年05月11日

「urlopen」でHTMLを取得し、「BeautifulSoup」で解析する。
その土台となっているのが「Python」ていう感じ。
┌─────────────────┐    ┌─────────────────┐
│              urllib              │ → │          BeautifulSoup           │
│                                  │    │                                  │
│from urllib.request import urlopen│    │from bs4 import BeautifulSoup     │
│html = urlopen(URL).read()        │    │soup = BeautifulSoup(html,        │
│                                  │    │                     "html.parser")
├─────────────────┴──┴─────────────────┤
│                           Python(文法・関数など)                           │
│                                                                            │
│# 結果をファイル出力                                                        │
│f = open("出力.txt", "w", encoding="utf-8")                                 │
│f.write(文字列)                                                             │
│f.close()                                                                   │
│                                                                            │
│# 取得先に負荷をかけないよう間隔を空けることも忘れずに                      │
│from datetime import datetime                                               │
│from time import sleep                                                      │
│sleep(1)                                                                    │
└──────────────────────────────────────┘

分類：Python

ExcelVBA／図形をセルに収める

2020年05月07日

図形(オートシェイプ、ActiveXコントロール)の左上が属するセルに
その図形を収めるマクロ。
┌──────────────────────────────────────┐
│Dim 図形 As Shape                                                           │
│For Each 図形 In ActiveSheet.Shapes                                         │
│    With 図形                                                               │
│        .Left = .TopLeftCell.Left                                           │
│        .Top = .TopLeftCell.Top                                             │
│        .Width = .TopLeftCell.Offset(, 1).Left - .TopLeftCell.Left          │
│        .Height = .TopLeftCell.Offset(1).Top - .TopLeftCell.Top             │
│    End With                                                                │
│Next 図形                                                                   │
└──────────────────────────────────────┘

分類：ExcelVBA

ExcelVBA／セル結合への値代入

2020年03月26日

セル結合した領域に値を代入するとエラーとなる。(値のクリアも同様)
┌───────────────────┐
│実行時エラー'1004':                   │
│この操作は結合したセルには行えません。│
└───────────────────┘

対処法としては、「.MergeArea」をかませればよい。
┌────────────────────────────┐
│×セル.Value = 値     →  ○セル.MergeArea.Value = 値   │
│×セル.ClearContents  →  ○セル.MergeArea.ClearContents│
└────────────────────────────┘

分類：ExcelVBA

MSSQL／リンクサーバーでエラー

2020年02月20日

【現象】
  リンクサーバを用いて、Oracleの内容をMSSQLに引っぱってくる場合
  テーブル項目にtimestamp型の列が含まれていると以下エラーとなる
  →SELECTでその列を指定しなくともエラーが発生する
┌──────────────────────────────────────┐
│メッセージ 7354、レベル 16、状態 1、行 1                                    │
│リンク サーバー "■" の OLE DB プロバイダー "OraOLEDB.Oracle" により、      │
│無効なメタデータが列 "(timestamp型列)" に指定されました。                   │
│The data type is not supported.                                             │
└──────────────────────────────────────┘

【対策】
  OPENQUERYを用いる
┌──────────────────────────────────────┐
│SELECT *                                                                    │
│    FROM OPENQUERY(■, 'SELECT * FROM "表"');                               │
└──────────────────────────────────────┘

分類：MSSQL

前へ 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 … 156 次へ