質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Access

Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

Q&A

2回答

3884閲覧

Worksheet.Copyメソッドでたまにエラーが出る

kingkamehameha

総合スコア16

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Access

Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

0グッド

0クリップ

投稿2020/04/01 08:57

編集2022/01/12 10:55

AccessからExcelを操作しているときにエラーが出たり出なかったりする

処理の流れは以下の通りです。

AppObj = Excelオブジェクト
WbObj = テンプレートシートのあるWorkBook
WsObj = 保存用シート
SaveWbObj = 保存用ワークブック

Call Excel Kill
前の処理の流れで、Excelが残っているとエラーの原因になるのでWScript.shellにexecでEXCEL.EXEをキルしています。

### エラーが起きるコード
ActiveWorkbook.SaveAs FileName:=Foldername & "" & TName & "_" & Month & "月分.xlsx", FileFormat:=xlWorkbookDefault, Local:=True

エラー内容

リモートサーバーがないか、使用できる状態ではありません。

エラー出現タイミング

初回実行時、エラーが出た後VBEから中断、再度実行すると作成できる。
作成ファイルを開くと元のテンプレート.xlsxの回復ウィンドウが表示される。

試したこと

・WbObj/SaveWbObjをVariantにしたりWorkBook などにしたりしてみた
・Sleep xxxxを入れてWbObjがセットされるまで、次の処理を待たせたりしたけどダメだった。

Form[CODE_LINK]

1DOption Compare Database 2Option Explicit 3 4Private Sub 作成開始_Click() 5 6'処理の確認 7Dim chk As Integer 8 chk = MsgBox("処理を開始してよろしいですか?", vbYesNo + vbQuestion, "確認") 9 DoCmd.OpenForm "NOW_PROCESSING" 10 If chk = vbYes Then 11 12 13 14'=========================担当者の抽出=========================== 15 16'cntが担当者数、TNArrayが担当者コード(昇順) 17 18Dim db As Database 19Dim RS As Recordset 20Dim SQL As String 21Dim i As Long: i = 0 22Dim Cnt As Long 23Dim TCArray() As Variant 24Dim TNArray() As Variant 25 26SQL = "SELECT DISTINCT 利益一覧.担当者コード,利益一覧.担当者名 FROM 利益一覧 ORDER BY 利益一覧.担当者コード;" 27 28Set db = CurrentDb 29Set RS = db.OpenRecordset(SQL, dbOpenSnapshot) 30Cnt = RS.RecordCount 31ReDim TNArray(Cnt) 32ReDim TCArray(Cnt) 33 34 35Do Until RS.EOF 36 TCArray(i) = RS!担当者コード 37 TNArray(i) = RS!担当者名 38 ''Debug.Print "TCArray(" & i & ")" & RS!担当者コード & " " & "TNArray(" & i; ")" & RS!担当者名 39 RS.MoveNext 40 i = i + 1 41Loop 42Call DBexport(db, RS) 43 44'=========================担当者毎に取引先抽出=========================== 45 46Dim AppObj As Excel.Application 47Dim WbObj As Excel.Workbook 48 49Dim TName As String '担当者名 TNArrayの担当者コードを基に抽出 50Dim Month As String '算出月 51Dim arMod(2) As Long '月算出用 52Dim CCArray() As Variant '取引先コード 53Dim CNArray() As Variant '取引先名一覧 54Dim CName As String '顧客名 55Dim check As Long '取引先の処理カウント cntと一緒になったら次の担当者へ 56Dim Sales As Long '売上(+) 57Dim PurChase As Long '仕入値(-) 58Dim Revate As Long 'リベート(+) 59Dim GP As Long '売上総利益(粗利益) GrossProfit 60Dim GPM As Double '粗利率(%) GrossProfitMargin 61Dim DF As Long 'EMS(-) EMS 62Dim DFCnt As Long 'EMSの項目数 63Dim DFArray() As Variant 'EMSの項目格納用 64Dim HITDF() As Variant 'EMSの項目格納用(該当諸掛のみ) 65Dim DHL As Long 'DHL(-) DHL 66Dim DHLCnt As Long 'DHLの項目数 67Dim DHLArray() As Variant 'DHLの項目格納用 68Dim HITDHL() As Variant 'DHLの項目格納用(該当諸掛のみ) 69Dim WP As Long 'その他 70Dim WPCnt As Long 'ろう見本代の項目数 71Dim WPArray() As Variant 'ろう見本代の項目格納用 72Dim HITWP() As Variant 'ろう見本の項目格納用(該当諸掛のみ) 73Dim TP As Long '輸出利益(経費引当後) Trade Profit 74Dim TPM As Double '輸出利益率(経費引当後) TradeProfitMargin 75Dim j As Long: j = 0 76Dim k As Long: k = 0 77Dim l As Long: l = 0 '項目HIT数 78Dim m As Long: m = 0 'HIT項目収納カウント数 79Dim RC As Long '得意先別のレコード数(計算に利用) 80Dim flg As Boolean 'リベート項目があるかチェックするフラグ 81 Dim Item As Variant '配列からForeachで取出用 82 83 84中略 85 86 87 k = k + 1 88 'j = j + 1 89 check = check + 1 90 91 'ファイル作成プログラムに投げる 92 Call FileMaker(Foldername, Cnt, TName, Month, CName, Sales, PurChase, Revate, Lavel, IP, GP, GPM, EFC, CF, DF, DHL, SF, SP, WP, TP, TPM, HITScode(), HITIP(), HITEFC(), HITCF(), HITDF(), HITDHL(), HITSF(), HITSP(), HITWP(), EFCArray(), CFArray(), DFArray(), DHLArray(), SFArray(), CCArray(), TCArray(), SPArray(), WPArray(), AppObj, WbObj) 93 Loop 94 95 '変数の初期化 96 k = 0 97 j = 0 98 check = 0 99 100Next i 101 102 '処理中フォームの非表示 103 DoCmd.Close acForm, "NOW_PROCESSING" 104 105 'AppObj.Quit 106 Set AppObj = Nothing 107 108 '作成フォルダを開く 109 Dim rc2 As Integer 110 rc2 = MsgBox("処理が完了しました。ファイルを確認しますか?", vbYesNo + vbQuestion, "確認") 111 If rc2 = vbYes Then 112 Shell "C:\Windows\Explorer.exe " & Foldername, vbNormalFocus 113 Call ExcelKill 114 End If 115 116 117'処理の確認=False 118Else 119 120 Exit Sub 121 122End If 123End Sub 124 125'データーベースとレコードセット開放 126Private Sub DBexport(db As Database, RS As Recordset) 127 128 RS.Close 129 db.Close 130 Set db = Nothing 131 Set RS = Nothing 132 133End Sub 134 135'データベースとレコードセットの登録 136Private Sub DBSet(db As Database, RS As Recordset, SQL As String) 137 138 Set db = CurrentDb 139 Set RS = db.OpenRecordset(SQL, dbOpenSnapshot) 140 141End Sub 142 143'配列内の重複値削除用 144Function DeleteSameValue(ar() As Variant) As Variant 145 Dim i '// ループカウンタ1 146 Dim ii '// ループカウンタ2 147 Dim iLen '// 配列要素数 148 Dim arEdit() '// 編集後の配列 149 Dim iEdit '// 編集後配列のインデックス 150 Dim flg As Boolean '// 重複有無判定フラグ(True:重複あり、False:なし) 151 152 If IsArrayEx(ar()) = 1 Then 153 ReDim arEdit(0) 154 iLen = UBound(ar) 155 156 '// 配列ループ 157 For i = 0 To iLen 158 '// 重複有無判定フラグを重複なしとして初期化 159 flg = False 160 161 '// 重複除去済みの編集後配列ループ 162 For iEdit = 0 To UBound(arEdit) 163 '// 編集後配列に格納済みの場合 164 If (ar(i) = arEdit(iEdit)) Then 165 flg = True 166 Exit For 167 End If 168 Next 169 170 '// 現ループの値には重複がない場合 171 If (flg = False) Then 172 '// 重複がない値のみを編集後配列に格納する 173 arEdit(UBound(arEdit)) = ar(i) 174 ReDim Preserve arEdit(UBound(arEdit) + 1) 175 End If 176 Next 177 178 '// 配列に格納済みの場合 179 If (IsEmpty(arEdit(0)) = False) Then 180 '// 余分な領域を削除 181 ReDim Preserve arEdit(UBound(arEdit) - 1) 182 End If 183 184 '// 引数に編集後配列を設定 185 ar = arEdit 186 Else 187 End If 188End Function 189 190'*********************************************************** 191' 機能 : 引数が配列か判定し、配列の場合は空かどうかも判定する 192' 引数 : varArray 配列 193' 戻り値 : 判定結果(1:配列/0:空の配列/-1:配列じゃない) 194'*********************************************************** 195Public Function IsArrayEx(varArray As Variant) As Long 196On Error GoTo ERROR_ 197 198 If IsArray(varArray) Then 199 IsArrayEx = IIf(UBound(varArray) >= 0, 1, 0) 200 Else 201 IsArrayEx = -1 202 End If 203 204 Exit Function 205 206ERROR_: 207 If Err.Number = 9 Then 208 IsArrayEx = 0 209 End If 210End Function 211

FilaMaker

1Option Compare Database 2Option Explicit 3Private Declare Sub Sleep Lib "kernel32" (ByVal ms As Long) 4Rem ---------------------------------------------------------------------------------- 5Rem 関数名 : FileMaker 6Rem 処理内容 : 担当者毎の取引先シート別EXCELファイル作成 7Rem 引 数  : 8Rem 戻り値  : 無 9Rem メ モ  : 'Form_CODE_LINKから項目名を受け取りEXCELファイル作成←配列渡しのがスマートかも?課題 10Rem ---------------------------------------------------------------------------------- 11Function FileMaker(Foldername As String, Cnt As Long, TName As String, Month As String, CName As String, Sales As Long, PurChase As Long, Revate As Long, Lavel As Long, IP As Long, GP As Long, GPM As Double, EFC As Long, CF As Long, DF As Long, DHL As Long, SF As Long, SP As Long, WP As Long, TP As Long, TPM As Double, HITScode() As Variant, HITIP() As Variant, HITEFC() As Variant, HITCF() As Variant, HITIDF() As Variant, HITDHL() As Variant, HITSF() As Variant, HITSP() As Variant, HITWP() As Variant, EFCArray() As Variant, CFArray() As Variant, DFArray() As Variant, SFArray() As Variant, DHLArray() As Variant, CCArray() As Variant, TCArray() As Variant, SPArray() As Variant, WPArray() As Variant, AppObj As Excel.Application, WbObj As Excel.Workbook) 12 13 14Dim WsObj As Excel.Worksheet '保存シート 15Static SaveWbObj As Excel.Workbook '保存ブック 16Dim Item As Variant '配列取出用 17Dim j As Long: j = 1 'シート名重複 18Dim CNameStr As String '顧客コード整形用 19Static i As Long '取引先の数 20Static column As Long '横列 21Static rows As Long '縦行 22Static CompanyCnt As Long '取引先数 23Static flg As Boolean '初回用 24 25If flg = False Then 26 'Call ExcelKill 27' Set AppObj = CreateObject("Excel.Application") 28' Set WbObj = AppObj.Workbooks.Open(Application.CurrentProject.Path & "\【削除不可】利益算出表テンプレート.xlsx") 29 AppObj.Visible = False 30 flg = True 31 Sleep 1000 32 33End If 34 35If i = 0 Then '初回 36 CompanyCnt = Cnt '取引先数を受け取る 37 column = 2 38 rows = 5 39 Sleep 1000 40 Set SaveWbObj = AppObj.Workbooks.Add(1) 41 Debug.Print AppObj 42 'Debug.Print WbObj 43End If 44 Sleep 1000 45 'With オブジェクト変数が設定されていません。エラーがたまにでる 46 WbObj.Sheets("テンプレート").Copy After:=SaveWbObj.Worksheets(1) 47 Set WsObj = SaveWbObj.ActiveSheet 48 49 If SaveWbObj.Sheets(1).Name = "Sheet1" Then 50 SaveWbObj.Sheets("Sheet1").Delete 51 End If 52 'Set SaveWbObj = ActiveWorkbook 53 54 WsObj.Range("B2").Value = TName 55 WsObj.Range("B3").Value = Month 56 57中略 58 59 60 '書出位置初期化 61 column = 2 62 rows = 5 63 64 65 CNameStr = Left(CCArray(i) & "_" & CName, 31) 66 SaveWbObj.ActiveSheet.Name = CNameStr 67 i = i + 1 68 If CompanyCnt = i Then 69 70 i = 0 71 j = 0 72 '終了時にファイルの保存 73 AppObj.Application.DisplayAlerts = False 74 SaveWbObj.Sheets(1).Select 75 ActiveWorkbook.SaveAs FileName:=Foldername & "\" & TName & "_" & Month & "月分.xlsx", FileFormat:=xlWorkbookDefault, Local:=True 76 SaveWbObj.Close 77 Set SaveWbObj = Nothing 78 AppObj.Application.DisplayAlerts = True 79 80 If TName = "TRAN MINH DUC" And i = 0 Then 81 WbObj.Close SaveChanges:=False 82 Sleep 1000 83 AppObj.Quit 84 Set AppObj = Nothing 85 Set WbObj = Nothing 86 'Call ExcelKill 87 End If 88 89End If 90End Function

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

Static WbObj As Workbookとオブジェクトを静的変数で宣言している目的は何でしょうか。

静的変数で宣言すると、プロシージャを抜けてもオブジェクトが解放されずにメモリ上に残ります。
コードで明示的に解放すればいいかも知れませんが、プロシージャを抜ける前に解放するなら、Dim で宣言したほうがいいように思います。

前の処理の流れで、Excelが残っているとエラーの原因になるのでWScript.shellにexecでEXCEL.EXEをキルしています。

これも、外部から強制的にキルしても、変数のメモリ上の領域は残りますので、
VBAのコードできちんとExcelを終了してメモリ領域も開放すべきだと思います。

投稿2020/04/02 01:15

hatena19

総合スコア33620

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

kingkamehameha

2020/04/02 01:53

回答有難う御座います。 WbObjはテンプレートブックなので、一度こちらのExcelファイルに書き起こすFunctionに飛んできた後、フォームに戻るのでその際に変数が初期化されてしまわないようにしています。 WbObj → SaveWbObjにシートをコピーする段階でエラーが起きているようです。元のWbobjを参照出来ていないのか、SaveWbObjを参照出来ていないのか不明です。 全ての処理が終わった時には、ワークブック=Close、オブジェクト=Quitした後、Set 変数 = Nothingで解放しています。
hatena19

2020/04/02 02:19 編集

WbObjをフォームの方でも使用するということでしょうか。Static宣言したら、そのFunction内でしか使えないはずですが。 AppObjはDIm宣言して、それからWbObjを開いているので、親のエクセル(AppObj)は解放されてしまうので、その辺も危険な気がします。 Functionの外でも使いたいなら、グローバル変数にするか、引数で渡す設計にすべきだと思います。
kingkamehameha

2020/04/02 03:15

WbObj自体はフォームの方では使用しないのですが、繰り返し処理の為またこちらに戻って来ることを想定してずっとそのままにしていたつもりでした。確かに親のObject(AppObj)を解放してしまうと危険ですね。ここはまだエラーが出ておりませんでしたが、修正したいと思います。
kingkamehameha

2020/04/02 04:15

ここら辺でEXCELを指定するのか、EXCEL.Applicationの変数を指定するのかイマイチわかっていない所も原因のひとつかと思っています。
hatena19

2020/04/02 04:26

その関数がフォームからのみ呼び出されるのなら、フォームモジュールの宣言部でEXCEL.Application、EXCEL.Workbook の変数を宣言して、関数には引数で、Workbook オブジェクトをわたす設計が安全だと思います。 フォームのUserForm_Initialize() で各オブジェクトのインスタンスを生成して、UserForm_Terminate() でオブジェクトを開放するようにします。 もし、複数の場所から呼び出されるなら、標準モジュールでグローバル変数として宣言することになりますが、バグのもとになるので推奨はできません。
kingkamehameha

2020/04/02 08:51

そのフォーム内ではEXCELに関連する処理は一切記載しておらず、フォーム内で計算の結果や配列などをこのモジュールに渡し、このモジュール内で計算の結果やモジュールを基にEXCELに書き起こしています。 毎回でるエラーも様々で、初回2回目限らずでるので、途方に暮れています。
hatena19

2020/04/02 10:02

そのフォーム内でエクセルを使用してなくても、そのフォームから関数を呼び出していて関数内でブックを利用する。関数はフォームから繰り返し呼び出す、その場合、前の呼び出しの時と同じブックである。というのなら、やはり、フォームのモジュールで宣言すべきだと思います。
kingkamehameha

2020/04/03 02:28

そのような処理にしてみました。 それよりも、Activebookと暗示的に記述していた部分を明示的にBook名の変数を指定しました。
guest

0

実際に発生したエラーメッセージを追記して頂くと、調査が捗ると思いますが、

Static WbObj As Workbook

Static WbObj As Excel.Workbook
に変更しても変わりませんか?

自身のACCESS-VBAである時期から突然エラーが発生し、As WorkBook を As Excel.Workbookに変更して回避したことがありました。

投稿2020/04/01 23:55

DreamTheater

総合スコア1095

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

kingkamehameha

2020/04/02 04:16

回答ありがとうございます。一応Objectで宣言していた CreateObject(EXCEL)/Workbook/Worksheet辺りは全てそれぞれの型に宣言し直しました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問