Excel(Windows)との自動連携
Excel(Windows)と Sales Performer の自動連携
メール連動 | FTP連動 | |||
メリット : | Gmailの送信済フォルダにCSVファイルが蓄積されるため、バックアップとして応用可 | メリット : | ネットワークセキュリティを導入していても、Excel連動を実装することが可能 ※FTPS(explicit)通信となります |
|
デメリット: | Google社による仕様変更により、Apps Scriptからのメール送信が不可になる可能性がある | デメリット: | Gmailのように送信済フォルダが無いため、CSVのバックアップ保存できない |
Excel連動をご検討されている際は、お気軽に弊社までお問い合せください
設定をお手伝いさせて頂きます
TEL 0120-958-172 (平日9:00~18:00)
[FTP連動]Excel自動連携
手順概要
|
1. | 連携対象Excelに新規シートを作成し、 Excel関数を使用してメンバー各自の集計表を作成 |
Excel関数「SUMIF」「COUTIF」などの関数を使用し、
メンバー各自の「月次」「日次」の集計表を作成する
※既に作成されていれば不要
関数計算を使った集計は、このExcelファイルで完結している必要がある 後に転記させるCSV変換用の「 CSVインポート.xlsm 」では、 集計結果のセル(値)を「=(イコール)」で紐付け表示させるため ※後に作成するCSVの生成時に連携対象Excelが立ち上がってないと、 エラーが発生してしまうため |
2. | 新たなExcelを作成し、対象Excelで集計した値(セル)を「=」で結び付ける |
- この時点でのファイルは、CSVファイル(.csv)でなく、Excelファイル(.xlsx)です。
- レイアウトはSales PerformerのCSV読み込み用レイアウトで作成し、A列とB列は予め入れておく
- このファイルに、連携対象Excelで集計した値を「=」で紐付け、値を表示させる
CSV読み込み用レイアウト
(灰色セルはExcelの列/行を示しています)
A | B | C | D | E | F | G | H | |
1 | No. | メンバー | 基本列1 | 基本列2 | 基本列3 | 基本列4 | 受注日 | 備考 |
2 | 1 | 田中 | ||||||
3 | 2 | 吉田 |
A列 No. : 対象メンバーのNo.を入力します(メンバー設定画面でも確認できます) B列 メンバー : 対象メンバーの名前を登録します C列 基本列1 : Sales Performerのグラフ列1で表示させる値を「=」で紐付けます D列 基本列2 : Sales Performerのグラフ列2で表示させる値を「=」で紐付けます E列 基本列3 : Sales Performerのグラフ列3で表示させる値を「=」で紐付けます F列 基本列4 : Sales Performerのグラフ列4で表示させる値を「=」で紐付けます G列 受注日 : 計上日をセル書式「yyyy/mm/dd」形式で入力します H列 備考 : 自動連携された入力である文言を記載します(Excel連携など) |
このファイルでは、連携対象Excelを「=」で紐付けた値を使用して、 |
|
A列:標準 ※社員IDで連携を行う場合は「文字列」 |
|
最後に、[Excel]-[データ]-[クエリと接続]-[リンクの編集]-[起動時の確認]を開き、
「メッセージを表示しないで、リンクの自動更新を行なう」を選択して保存
※[データ]タブに[リンクの編集]ボタンが選択できない場合
連携対象Excelと「=」を使用した紐付けが行えていないなど、
外部ファイルとのリンクが有効になっていないことが原因
3. | 2で作成したExcelに、自動でCSV形式に変換保存するVBA(マクロ)を作成し、保存 |
① マクロが使用できる状態にする
[Excel]-[ファイル]-[オプション]-[リボンのユーザー設定]で、
[開発]にチェックを入れ、[OK]を押す
② Visual Basicにコードを貼り付けて保存
[Excel]-[開発]-[マクロのセキュリティ]を開き、
[すべてのマクロを有効にする]を選択肢、[OK]を押す
[開発]-[Visual Basic]を開く
プロジェクトの[ThisWorkBook]をダブルクリックし、
下記のコードを貼り付ける
<code class="language-basic">Private Sub Workbook_Open() Dim myPATH As String Dim myFileName As String Dim myLooP As Long myPATH = "C:\Documents\csv\" '保存先フォルダ Application.DisplayAlerts = False '確認メッセージOFF For myLooP = 1 To ActiveWorkbook.Sheets.Count ' myFileName = myPATH & Sheets(myLooP).Name & ".csv" 'シート名を保存ファイル名にする場合 ' myFileName = myPATH & "20180000" & ".csv" '2018年を保存ファイル名にする場合 myFileName = myPATH & Format(Date, "yyyymmdd") & ".csv" '今日の日付を保存ファイル名にする場合 ActiveWorkbook.Sheets(myLooP).Copy ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlCSV ActiveWindow.Close Next myLooP Application.DisplayAlerts = True '確認メッセージON Application.Wait Now + TimeValue("00:00:10") '指定秒数タイマーで待ち Application.Quit 'プログラム終了 ActiveWorkbook.Close savechanges:=False '保存確認をしない End Sub</code>
■コード解説 | |
myPATH | : “”内に、自動変換したcsvファイルを保存するフォルダを指定 |
myFileName | : 3行のうち、先頭にクォーテーション(‘)が入っている行が無効になる |
Application.Wait | : 上記例では自動変換保存とプログラム終了の間で10秒待たせている |
コードの貼付け終了後、[名前を付けて保存]を実行し、
[Excel マクロ有効ブック(*.xlsm)]を選択して保存する
VBA設定/保存後のExcelファイルを開くと |
4. | Windowsタスクスケジューラで、3のExcelを自動で開く設定を行い、CSVを自動生成させる |
[全般]タブ | |
名前:例) SP-CSV変換 セキュリティオプション:「ユーザーがログオンしているときのみ実行する」 |
[トリガー]タブ-[新規] | |
設定:1回 繰り返し間隔:5分間など任意の自動処理間隔を選択 ※チェック段階では5分を推奨 継続時間:無制限 |
[操作]タブ-[新規] | |
プログラム/スクリプト:[参照]ボタンでEXCEL.EXEを探して選択 引数の追加(オプション):SP-CSV.xlsmファイルの配置場所 + /e を記載 繰り返し間隔:5分間など任意の自動処理間隔を選択 継続時間:無制限 |
5. | FFFTPからSales PerformerのFTPサーバーにミラーリングさせるバッチファイルを作る |
①フリーソフト「FFFTP」をダウンロードする
https://forest.watch.impress.co.jp/library/software/ffftp/
②FFFTPの設定
[ホスト一覧]で[新規ホスト]を選択
下記の設定を行う
①ホストの設定名 :任意名称でOKですが、アカウント名にするとわかりやすい
②ホスト名(アドレス) :Sales Performer[データ連動設定]-[FTPサーバー]に記載のアドレス
③ユーザー名 :Sales Performer[データ連動設定]-[FTPサーバーID PW]に記載のID
④パスワード :Sales Performer[データ連動設定]-[FTPサーバーID PW]に記載のPW
⑤ローカルの初期フォルダ:Visual Basic コードで指定した、CSV変換ファイルのフォルダ
⑥[暗号化タブ]-[FTPS(Explicit)で接続]のみ選択
※データを暗号化するために必要な操作です
④接続テスト
ホスト一覧から先ほど作成したホスト名を選択し[接続]をクリック
「ファイル一覧の取得は正常終了しました」と表示されれば正常接続OK
⑤ショートカットアイコン作成
フリーソフト「FFFTP」のショートカットアイコンの作成後、
プロパティのリンク先を下記へ変更する
“C:\Program Files (x86)\ffftp\ffftp.exe" -s “17sp0" -f -q -m
※上記赤文字(「-s」の前にある半角スペースも含む)を追記します
⑥バッチファイルを作成する
Windowsのメモ帳を開き、下記のコマンドを記載した後、
ファイル名「excel2csv.bat」で保存する
call C:************************ \FFFTP.lnk
start /w C:************************ \FFFTP.lnk
※「call C:************************ \FFFTP.lnk」は、上記で作成したショートカットアイコンのオブジェクト名を設定する
6. | Windowsタスクスケジューラで、FFFTPのバッチを実行させ、FTP連携を行う |
「4.Windowsタスクスケジューラで、3のExcelを自動で開く設定を行い、CSVを自動生成させる」と同様、タスクスケジューラを設定する
※Sales Performerの「外部連携設定」で、データ連動間隔を5分おきに設定した場合、「10時05分、11時15分」など、5(分)の倍数で起動するように設定
「操作タブ」のみ、上記画像のように設定を行う プログラム/スクリプト:batファイルの配置場所 開始(オプション):上記batファイルの「オブジェクト名」を入力 |
7. | ExcelとSales Performerが自動連携される |
念のため、各種グラフ画面や、Sales Performerの[設定]-[操作ログ]で連携動作が反映されているか確認
[メール連動]Excel自動連携
手順概要
|
各手順詳細説明
1. | 連携対象Excelに新規シートを作成し、 Excel関数を使用してメンバー各自の集計表を作成 |
Excel関数「SUMIF」「COUTIF」などの関数を使用し、
メンバー各自の「月次」「日次」の集計表を作成する
※既に作成されていれば不要
関数計算を使った集計は、このExcelファイルで完結している必要がある 後に転記させるCSV変換用の「 CSVインポート.xlsm 」では、 集計結果のセル(値)を「=(イコール)」で紐付け表示させるため ※後に作成するCSVの生成時に連携対象Excelが立ち上がってないと、 エラーが発生してしまう |
2. | CSV連動用のGoogleアカウントを新規作成する |
作成後、メールアドレス/Passwordを失念しないよう、メモ帳などで保存する
既に取得されているアカウントでも問題はありませんが、スプレッドシートとの連携をご希望の場合、作成したGoogleアカウントでの設定操作が必要になります
※新規アカウント作成方法はこちらをご覧ください
3. | ドライブ内に連携用フォルダを作成する |
ドライブ「マイドライブ」で、連携用フォルダを作成する
※右クリック「新しいフォルダー新しいフォルダの名前作成」で作成可能です
4. | ドライブのPC版をインストールする |
下記の手順でインストールを行う
[1] ドライブを開く [2] 設定クリック [3] パソコン版 ドライブをダウンロード [4] 別タブで画面表示 [5]「パソコン版ドライブをダウンロード」をクリック [6]「GoogleDriveSetup.exe」がダウンロードされる [7] ダウンロード完了後、「開く」を選択 [8] このアプリがデバイスに変更を加えることを許可しますか?→「はい」を選択 [9] ドライブをインストールしますか?→「インストール」を選択 [10]「ブラウザ」でログインをクリック [11] CSV連動用のGoogleアカウントを選択してログイン※設定後のアカウント変更は不可 [12]「Google Docs」「Google Sheets」「Google Sides」がデスクトップ表示される(削除可) [13] PC上にある適当なフォルダを開く(どのフォルダでも可) [14] フォルダ画面左のサイドパネルから「PC」を選択 [15]「デバイスとドライブ-Google Drive(G:)」を選択 [16] アイコン「マイドライブ」を右クリックし、「ショートカットを作成(S)」を選択 [17] デスクトップにショートカットアイコンを作成する→操作完了 |
5. | SEVENTEEN提供のExcelマクロ「Excel連動.xlsm」を編集する |
① Excelマクロ「Excel連動.xlsm」を取得する
Excelマクロの提供をご希望の場合は、お気軽に弊社までお問い合せください。
TEL 0120-958-172 (平日9:00~18:00)
② Shiftキーを使用して「Excel連動.xlsm」を立ち上げる
このExcelマクロは、ダブルクリックで開くと編集作業ができない為、以下の手順で操作する
- Windowsスタートボタン等から新規Excelを立ち上げる
- Excel画面左上に記載されている「開く」をクリック
- 画面中央部に表示された項目「参照」をクリック
- 先ほど保存した「Excel連動.xlsm」を一度クリックし、
ファイル全体に色が付いたら、Shiftキーを押しながら開くボタンをクリック
③ 「Excel連動.xlsm」を編集し、マクロ動作できるようにする
・「集計」シートの編集
「J列」「K列」に、Sales Performerの「メンバー設定」で登録したメンバー名とNo.を入力する
その後、「1.連携対象Excelに新規シートを作成し、Excel関数を使用してメンバー各自の集計表を作成」で新規作成したシート情報を活用し、「B列~H列」までの各所セル(値)を「=(イコール)」で紐付け表示させる
※A列はメンバーNoが自動で表示されるため、編集は不要
・「CSV」シートの編集
「A列~H列」の1001行目まで、「集計」シートのセルと紐付け(値が無い場合は空欄表示させるExcel関数を使用)を行う
・「VBA(マクロ)」の編集
Excelマクロを利用できる状態にするため、下記の操作を行う
|
上記の操作でVBA(マクロ)が使用できる状態になった為、VBAの設定を行う
・[開発タブ]-[Visual Basic] -[ツールタブ]-[参照設定]から、以下4点をチェックし、「OK」を押す
・CSVの自動保存先をドライブの連携用フォルダに指定する
[開発タブ]-[Visual Basic]-[ThisWorkbook]を開き、以下の★印の中を編集する
★印の中は、下記のオブジェクト名を入力する
I:\マイドライブ\"フォルダ名称"
例:I:\マイドライブ\セールスパフォーマー連携
※オブジェクト名の語尾に「\」を付けてください
※上記オブジェクト名はドライブ内で新規作成したフォルダ名によって変動します
※★印下部にある「20190000」は、CSV出力時のファイル名が指定できる箇所です
(変更する場合、名称は「半角数字8ケタ」で指定)
<オブジェクト名の確認方法>
★印の入力が終了したら、現在開いているVBAlの保存ボタンを押して閉じる
Excel保存/終了後、再度Excelマクロ「CSV自動変換」をダブルクリックし、ドライブの指定フォルダ中にCSVファイル(半角数字8ケタ)が保存されていることを確認し、終了
6. | Windowsタスクスケジューラを使用し、Excelマクロ「Excel連動.xlsm」を指定の時間間隔で実行させる |
[全般]タブ | |
名前:例) Excel連動 セキュリティオプション:「ユーザーがログオンしているときのみ実行する」 |
[トリガー]タブ-[新規] | |
設定:1回 繰り返し間隔:5分間など任意の自動処理間隔を選択※30分を推奨 継続時間:無制限 |
[操作]タブ-[新規] | |
プログラム/スクリプト:[参照]ボタンでEXCEL.EXEを探して選択 引数の追加(オプション):Excelマクロ「Excel連動.xlsm」の配置場所 + /e を記載 |
7. | 上記「2」で用意したGoogleアカウントで「Apps Script」設定を行う |
下記の手順に従ってApps Scriptの「エディタ」「トリガー」の設定を行う
■Apps Script設定時に下記表示が確認された際の設定方法
1 | 左記の表記が表示された場合、「権限を確認」を選択 | |
2 | 左記の表記が表示された場合、上記「2」で用意したGoogleアカウントを選択する | |
3 | 「詳細」をクリック後、「●●のプロジェクト(安全ではないページ)に移動」をクリック ※移動先もGoogleページですので、ご安心ください |
|
4 | 左記の表記が表示された場合、「許可」を選択 |
8. | ExcelとSales Performerが自動連携される |
念のため、各種グラフ画面や、Sales Performerの[設定]-[操作ログ]で連携動作が反映されているか確認