IT's AGENT
Power Shell
Bottom

Power ShellとVBAの組み合わせ

WindowsのPowerShellはいろいろと操作の自動化ができる。
グルー言語とも言われていて、他の言語とも組み合わせて便利に使える。
全く知らなかったし、使う機会もないよね、と思っていたが、試しに無理やり使ってみた。
ローカルSQLのデータをVBAで取り出して、その内容をネット上のMySQLに保存するために、1件ずつ登録するPHPページを作って、VBAからのSendKeysで半自動入力するという作業をポチポチっとやってた。
SendKeysなんで、不調のときはやり直すことも少なからずある状態。
これをPowerShellを利用して効率的に業務の流れを作る。

1. VBAでXMLを作成する。
2. PowerShellで、XMLファイルの文字コードをUTF-8に変更する。
3. 連続して、Notepad++を起動して対象のファイル(scd.xml)を開く。
 … 必要に応じて、XMLを手作業でデータを編集して保存。
4. PowerShellで、XMLファイルをネットにアップロード。
5. 連続して、XMLファイルを読み込んでMySQLに保存するPHPをChromeで開く。
6. 連続して、結果を表示するページを開く。
やってみたら、凄く便利。難しいことは全くわからなかったが、自動化出来たら便利かな、というときにはPowerShellのことを思い出そう。

VBA起動

TOP

エクセルを起動してファイルを読み込み、指定のマクロを実行する。

$xls = New-Object -ComObject Excel.Application
$xls.Visible = $False
$wb = $xls.workbooks.open("C:\ハピネス\今日のデータ.xlsm")
$xls.Run("XmlCreate2")
$wb.Save()
$xls.Quit()
$xls = $null
[GC]::Collect()

VBAから起動

TOP

VBAから作成したXMLファイルをコンバートするPowerShellの実行。
作成したXMLファイルの文字コード変更。

Dim Wsh
Set Wsh = CreateObject("Wscript.shell")
Dim cmdstr As String
cmdstr = "get-content -Path C:\ハピネス\scd.xml | Set-Content -Path C:\ActiveWeb\schedule\scd.xml -Encoding UTF8"
Wsh.Run "powershell -ExecutionPolicy RemoteSigned -Command " & cmdstr, 0
Set Wsh = Nothing

実行：START

プログラムを起動してファイルを読み込む

start "C:\Program Files\Notepad++\notepad++.exe" "C:\ActiveWeb\schedule\scd.xml"
start "C:\Program Files\Google\Chrome\Application\chrome.exe" "http://www.hapines.info/xxx/add_xml_xxx.php"

FTP

必要な変数の設定

$localFilePath:ローカルファイルのパス（ファイル名まで）
$serverFilePath:アップロード先のパス（ファイル名まで）
$ftpUrl ：FTP接続用のURL→ 'ftp://' + $hostName + '/'
# 接続
$webClient = New-Object System.Net.WebClient;
$webClient.Credentials = New-Object System.Net.NetworkCredential($user,$pass);
$webClient.BaseAddress = $ftpUrl;
# アップロード
$webClient.UploadFile($serverFilePath , $localFilePath);
$webClient.Dispose();

分業めいと

薬局閉局に伴い、在庫の薬を引き取ってもらえることに。
いろいろな方にお世話になっております。
レセコンは「分業めいと」を利用していますが、薬を買ってもらえる薬局に伝票を発行しなければなりません。
普段の小分けは2，3品だし、「分業めいと」では、薬の名前を一部入力すると対象薬が選択でき、既定値の薬価を表示してくれるので、Lot番号と数量を入力するだけなので、そんなに負担はありません。


しかし今回はアイテム数が多い。500件以上買っていただけるケースもありました。
また、薬価で引き取ってもらうわけではないので、単価訂正も必要となり、短時間の間に正確に入力できるか心配でした。
薬のリストは棚卸の結果をエクセルで表にまとめてあります。販売伝票に必要な、薬品コード、単価、有効期限、Lot番号、数量が含まれます。
なので、POWERSHELLで、エクセルからデータを取得して、伝票入力画面にペーストすることを考えました。
この件は、VBは使用していませんが、利用する棚卸を記録するエクセルではVBA満載でした。
POWERSHELLでキーストロークを指示するには、VisualBasicのアセンブリが必要なようです。(Windows.Formsだけで良いのかも)
--------入力画面起動-----------------------------------------------------------
C:\CHOZAI\K_EXE\KD110.EXE
---------------------------------------------------------------------------------
--------コピペ(SendKeys)準備-------------------------------------------------
Add-Type -AssemblyName microsoft.VisualBasic
Add-Type -AssemblyName System.Windows.Forms
---------------------------------------------------------------------------------
--------エクセル起動-----------------------------------------------------------
$xls = New-Object -ComObject Excel.Application
$xls.Visible =$false
$wb = $xls.workbooks.open("C:\CHOZAI\CSV\ハピネス薬局在庫表.xls")
$sheet = $wb.Worksheets.item("m0116")
---------------------------------------------------------------------------------
ここで、入力画面にフォーカスを自動であてたかったが、なぜかできなかったので、停止させて手動でフォーカスした。
$title = "*** 実行確認 ***"
$message = "データ確認してください。" $objYes = New-Object System.Management.Automation.Host.ChoiceDescription "&Yes","Y"
$objNo = New-Object System.Management.Automation.Host.ChoiceDescription "&No","N"
$objOptions = [System.Management.Automation.Host.ChoiceDescription[]]($objYes, $objNo)
$resultVal = $host.ui.PromptForChoice($title, $message, $objOptions, 1)
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
速すぎると、エラーの原因になるので、適宜空白時間を入れる。
Start-Sleep -s 1   (1秒)
Start-Sleep -m 500  (0.5秒 ﾐﾘｾｶﾝﾄﾞ)
---------------------------------------------------------------------------------
---------伝票ヘッダー（宛先）-------------------------------------------------
宛先の薬局コード以外（日付など）はENTERで既定値とする。
[System.Windows.Forms.SendKeys]::SendWait("{ENTER}")
[System.Windows.Forms.SendKeys]::SendWait("{ENTER}")
[System.Windows.Forms.SendKeys]::SendWait("10")
[System.Windows.Forms.SendKeys]::SendWait("{ENTER}")
[System.Windows.Forms.SendKeys]::SendWait("{ENTER}")
---------------------------------------------------------------------------------
--------ループ------------------------------------------------------------------
while ($i -le 590) {
  $i +=1
}
(今回は590行まで)
---------------------------------------------------------------------------------
--------ペースト----------------------------------------------------------------
ループ内でエクセルの対象行($i)の11列目の値をペーストする。(薬品コードの列)

[System.Windows.Forms.SendKeys]::SendWait($sheet.Cells.Item($i,11).text.trim())

エクセルのセルの値では、余分なスペースが含まれていてエラーになるため、trim関数をつける。
必要な列を同様にペーストする。
[System.Windows.Forms.SendKeys]::SendWait($sheet.Cells.Item($i,8).text.trim())
[System.Windows.Forms.SendKeys]::SendWait("{ENTER}")
[System.Windows.Forms.SendKeys]::SendWait($sheet.Cells.Item($i,13).text.trim())
[System.Windows.Forms.SendKeys]::SendWait("{ENTER}")
[System.Windows.Forms.SendKeys]::SendWait($sheet.Cells.Item($i,16).text.trim())
[System.Windows.Forms.SendKeys]::SendWait("{ENTER}")
[System.Windows.Forms.SendKeys]::SendWait($sheet.Cells.Item($i,5).text.trim())
[System.Windows.Forms.SendKeys]::SendWait("{ENTER}")
[System.Windows.Forms.SendKeys]::SendWait("{ENTER}")
---------------------------------------------------------------------------------
必要列を入力するとENTERで次の行へ移動するので、エクセルの行をループして続ければよい。
が、Lot違いなどで、同じ薬品が数行になる場合があります。
すると「分業めいと」は注意してくれる。-_-  連続してキーを送るとズレておかしなことに。

なので、同じ薬品が連続する場合は、キーを余分に押さないといけないことになります。
--------連続薬品チェック------------------------------------------------------
ペーストする薬品コードと上の行の薬品コードとを比較します。
同じであれば、メッセージボックスが出ているはずなので、TABで既定値の「いいえ」から「はい」にしてENTERします。
If($sheet.Cells.Item($i,11).text.trim() -eq $sheet.Cells.Item($i-1,11).text.trim()){
  [System.Windows.Forms.SendKeys]::SendWait("{TAB}")
  [System.Windows.Forms.SendKeys]::SendWait("{ENTER}")
}
---------------------------------------------------------------------------------
「分業めいと」は50行までしか作成できません。

なので、50行で確定(F8)を押して、その伝票を確定させ、次の伝票のヘッダーを再度作成します。
--------カウンター追加--------------------------------------------------------
$j +=1
if($j -eq 51){
  $j=1
   [System.Windows.Forms.SendKeys]::SendWait("{F8}")
   [System.Windows.Forms.SendKeys]::SendWait("{ENTER}")
   [System.Windows.Forms.SendKeys]::SendWait("{ENTER}")
   [System.Windows.Forms.SendKeys]::SendWait("10")
   [System.Windows.Forms.SendKeys]::SendWait("{ENTER}")
   [System.Windows.Forms.SendKeys]::SendWait("{ENTER}")
}
---------------------------------------------------------------------------------
--------エクセル終了-----------------------------------------------------------
$xls.Quit()
$xls = $null
[GC]::Collect()
---------------------------------------------------------------------------------
伝票の最後は手動で確定しました。
比較演算子がめんどくさいです。ここのミスは見つけにくい。
KZUL
Topへ
by IT's AGENT