\u3061\u3087\u3063\u3068\u8a66\u3057\u306b\u3002<\/p>\n
Sub MainRoutine(TargetName, BaseYear, RoutineType)\r\n\r\nExecuteResult = False\r\n\r\nIf RoutineType = \"COMPARE_YEAR\" Then\r\n\r\nQueryString = \"select * from xxx\"\r\nExecuteResult = DbExecute(QueryString, TargetName, BaseYear, RoutineType)\r\n\r\nEnd If\r\n\r\nCall TmporaryDelete\r\n\r\nIf True = ExecuteResult Then\r\n MsgBox \"\u5b8c\u4e86\u3057\u307e\u3057\u305f\"\r\nElse\r\n MsgBox \"\u30a8\u30e9\u30fc\u304c\u767a\u751f\u3057\u3066\u3044\u307e\u3059\"\r\nEnd If\r\n\r\nEnd Sub\r\n\r\nFunction DbExecute(QueryString, TargetName, BaseYear, RoutineType)\r\n\r\nOn Error GoTo e\r\n\r\nSet cn = CreateObject(\"ADODB.Connection\")\r\nSet rs = CreateObject(\"ADODB.Recordset\")\r\n\r\nDbPath = \"C:\\xxx.accdb\"\r\nConnectionString = \"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=\" & DbPath & \"; Jet OLEDB:Database Password=xxx\"\r\n\r\ncn.Open ConnectionString\r\nrs.Open QueryString, cn\r\n\r\nIf RoutineType = \"COMPARE_YEAR\" Then\r\n If False = CompareYear(rs, TargetName, BaseYear, RoutineType) Then GoTo e\r\nEnd If\r\n\r\nIf rs.State = 1 Then rs.Close\r\nSet rs = Nothing\r\n \r\nIf cn.State = 1 Then cn.Close\r\nSet cn = Nothing\r\n\r\nDbExecute = True\r\nExit Function\r\n\r\ne:\r\n\r\nIf rs.State = 1 Then rs.Close\r\nSet rs = Nothing\r\n \r\nIf cn.State = 1 Then cn.Close\r\nSet cn = Nothing\r\n\r\nDbExecute = False\r\n\r\nEnd Function\r\n\r\nFunction CompareYear(ByVal rs, TargetName, BaseYear, RoutineType)\r\n\r\nOn Error GoTo e\r\n\r\nCall TmporaryDelete\r\nSet w = Sheets.Add: w.Name = \"tmp\"\r\n\r\nSheetName = \"\u5e74\u5ea6\u6bd4\u8f03(\" & BaseYear - 1 & \"-\" & BaseYear & \")_\" & TargetName\r\n\r\nIf True = ExistSheet(SheetName) Then\r\n MsgBox \"\u30b7\u30fc\u30c8\u540d\u304c\u91cd\u8907\u3057\u3066\u3044\u307e\u3059\"\r\n GoTo e\r\nEnd If\r\n\r\nSheets(\"(\u96db\u5f62)\u5e74\u5ea6\u6bd4\u8f03\").Copy After:=Sheets(Sheets.Count)\r\nActiveSheet.Name = SheetName\r\n\r\n'ActiveSheet.Cells(1, 1).CopyFromRecordset rs\r\n\r\nCompareYear = True\r\nExit Function\r\n\r\ne:\r\n\r\nCompareYear = False\r\n\r\nEnd Function\r\n\r\nFunction ExistSheet(SheetName)\r\n\r\nFor Each w In Sheets\r\n If w.Name = SheetName Then\r\n ExistSheet = True\r\n Exit Function\r\n End If\r\nNext\r\n\r\nExistSheet = False\r\n\r\nEnd Function\r\n\r\nSub TmporaryDelete()\r\n\r\nApplication.DisplayAlerts = False\r\n\r\nFor Each w In Sheets\r\n If w.Name = \"tmp\" Then Sheets(\"tmp\").Delete\r\nNext\r\n\r\nApplication.DisplayAlerts = True\r\n\r\nEnd Sub\r\n<\/pre>\n<\/p>\n","protected":false},"excerpt":{"rendered":"
\u3061\u3087\u3063\u3068\u8a66\u3057\u306b\u3002 Sub MainRoutine(TargetName, BaseYear, RoutineType) ExecuteResult = False If RoutineType = “COMPARE_YE … <\/p>\n