{"id":1966,"date":"2017-10-27T06:21:33","date_gmt":"2017-10-26T21:21:33","guid":{"rendered":"http:\/\/okamurax.com\/?p=1966"},"modified":"2024-05-03T18:58:26","modified_gmt":"2024-05-03T09:58:26","slug":"vba-%e5%b7%a5%e6%95%b0%e7%ae%a1%e7%90%86%e3%83%97%e3%83%ad%e3%82%b0%e3%83%a9%e3%83%a0","status":"publish","type":"post","link":"https:\/\/appbay.org\/?p=1966","title":{"rendered":"VBA \u5de5\u6570\u7ba1\u7406\u30d7\u30ed\u30b0\u30e9\u30e0"},"content":{"rendered":"

\u524d\u56de\u306eC#\u7248\u3092\u4e2d\u6b62\u3057\u3066\u304b\u3089\u5373\u5e2d\u3067\u4f5c\u3063\u305f\u3002C#\u306e\u3068\u304d\u306fMySQL\u3060\u3063\u305f\u304c\u3001\u4eca\u56de\u306fmdb\u30d5\u30a1\u30a4\u30eb\u3068\u3057\u305f\u3002\u4f7f\u3046\u306e\u304c1~2\u540d\u3067\u3042\u3063\u3066\u3082\u8907\u6570\u7b87\u6240\u3067\u958b\u304f\u5834\u5408\u3001\u30c7\u30fc\u30bf\u306f\u5225\u30d5\u30a1\u30a4\u30eb\u306e\u65b9\u304c\u3044\u3044\u3060\u308d\u3046\u3068\u5224\u65ad\u3002<\/p>\n

\u4eca\u56de\u306f\u4f7f\u3046\u5834\u9762\u3092\u60f3\u50cf\u3067\u304d\u305f\u306e\u3067\u3001\u3042\u307e\u308a\u3057\u3063\u304b\u308a\u5bfe\u7b56\u3057\u3066\u3044\u306a\u3044\u304c\u3001\u4f7f\u3046\u4eba\u304c\u66f8\u5f0f\u3084\u95a2\u6570\u3092\u5909\u66f4\u3057\u3066\u3057\u307e\u3063\u3066\u3082\u5927\u4e08\u592b\u306a\u3088\u3046\u306b\u3001\u8d77\u52d5\u6642\u306b\u3057\u3063\u304b\u308a\u8a2d\u5b9a\u3057\u76f4\u3059\u51e6\u7406\u3092\u3044\u308c\u308c\u3070\u3001\u30a8\u30af\u30bb\u30ebVBA\u3067\u3082DB\u306e\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u3068\u3057\u3066\u304b\u306a\u308a\u4f7f\u3048\u305d\u3046\u3002<\/p>\n

UserForm1<\/p>\n

Function form_query()\r\n\r\nOn Error GoTo try\r\n\r\nSet cn = CreateObject(\"ADODB.Connection\")\r\nSet rs = CreateObject(\"ADODB.Recordset\")\r\n\r\ncn.Open \"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\" & Sheets(\"\u8a2d\u5b9a\").Range(\"B3\").Value & \";\"\r\nrs.Open \"select ID,customer,model,serial,quantity,notes from orders where serial = '\" & UserForm1.TextBox1.Text & \"';\", cn\r\n\r\nDo Until rs.EOF\r\n\r\n  UserForm1.TextBox8.Text = rs(0)\r\n  UserForm1.TextBox2.Text = rs(1)\r\n  UserForm1.TextBox3.Text = rs(2)\r\n  UserForm1.TextBox1.Text = rs(3)\r\n  UserForm1.TextBox7.Text = rs(4)\r\n  UserForm1.TextBox4.Text = rs(5)\r\n\r\nrs.movenext\r\nLoop\r\n\r\nIf UserForm1.TextBox8.Text <> \"\" Then\r\n  UserForm1.TextBox5.Text = \"1\"\r\n  UserForm1.TextBox6.Text = UserForm1.TextBox7.Text\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\nform_query = True\r\n\r\nExit Function\r\n\r\ntry:\r\n\r\n  If rs.state = 1 Then rs.Close\r\n  Set rs = Nothing\r\n\r\n  If cn.state = 1 Then cn.Close\r\n  Set cn = Nothing\r\n\r\n  MsgBox Err.Description\r\n  form_query = False\r\n  \r\nEnd Function\r\n\r\nPrivate Sub CommandButton1_Click()\r\n\r\nIf UserForm1.TextBox1.Text = \"\" Then\r\n  MsgBox \"\u5de5\u756a\u3092\u5165\u529b\u3057\u3066\u304f\u3060\u3055\u3044\u3002\"\r\n  Exit Sub\r\nEnd If\r\n\r\nIf db_chk = False Then Exit Sub\r\n\r\nform_clear\r\nform_query\r\n\r\nEnd Sub\r\n\r\nSub form_clear()\r\n\r\nUserForm1.TextBox8.Text = \"\"\r\nUserForm1.TextBox2.Text = \"\"\r\nUserForm1.TextBox3.Text = \"\"\r\nUserForm1.TextBox7.Text = \"\"\r\nUserForm1.TextBox4.Text = \"\"\r\nUserForm1.TextBox5.Text = \"\"\r\nUserForm1.TextBox6.Text = \"\"\r\n\r\nEnd Sub\r\n\r\nFunction db_chk()\r\n\r\nOn Error GoTo try\r\n\r\nSet cn = CreateObject(\"ADODB.Connection\")\r\ncn.Open \"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\" & Sheets(\"\u8a2d\u5b9a\").Range(\"B3\").Value & \";\"\r\n\r\nIf cn.state = 1 Then cn.Close\r\nSet cn = Nothing\r\n\r\ndb_chk = True\r\n\r\nExit Function\r\n\r\ntry:\r\n\r\n  If cn.state = 1 Then cn.Close\r\n  Set cn = Nothing\r\n  \r\n  MsgBox \"DB\u306b\u63a5\u7d9a\u3067\u304d\u307e\u305b\u3093\u3002\"\r\n  db_chk = False\r\n  \r\nEnd Function\r\n\r\nFunction qty_parse()\r\n\r\nOn Error GoTo try\r\n\r\nIf UserForm1.TextBox5.Text = \"\" Or UserForm1.TextBox6.Text = \"\" Then\r\n  MsgBox \"\u5bfe\u8c61\u679d\u3092\u5165\u529b\u3057\u3066\u304f\u3060\u3055\u3044\u3002\"\r\n  qty_parse = False\r\n  Exit Function\r\nEnd If\r\n\r\nqty = CInt(UserForm1.TextBox7.Text)\r\nsuffix_s = CInt(UserForm1.TextBox5.Text)\r\nsuffix_e = CInt(UserForm1.TextBox6.Text)\r\n\r\nIf suffix_s = 0 Or suffix_e = 0 Then\r\n  MsgBox \"\u5bfe\u8c61\u679d\u306f1\u4ee5\u4e0a\u306e\u6570\u5b57\u3092\u6307\u5b9a\u3059\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002\"\r\n  qty_parse = False\r\n  Exit Function\r\nEnd If\r\n\r\nIf suffix_e > qty Or suffix_s > suffix_e Then\r\n  MsgBox \"\u5bfe\u8c61\u679d\u306e\u6307\u5b9a\u7bc4\u56f2\u306b\u554f\u984c\u304c\u3042\u308a\u307e\u3059\u3002\"\r\n  qty_parse = False\r\n  Exit Function\r\nEnd If\r\n\r\nqty_parse = True\r\n\r\nExit Function\r\n\r\ntry:\r\n\r\n  MsgBox \"\u5bfe\u8c61\u679d\u304c\u6570\u5b57\u3067\u306f\u3042\u308a\u307e\u305b\u3093\u3002\"\r\n  qty_parse = False\r\n\r\nEnd Function\r\n\r\nPrivate Sub CommandButton2_Click()\r\nCall MainWrite(\"1\", \"\u30b9\u30bf\u30fc\u30c8\", \"\u4f5c\")\r\nEnd Sub\r\n\r\nPrivate Sub CommandButton3_Click()\r\nCall MainWrite(\"1\", \"\u30b9\u30bf\u30fc\u30c8\", \"\u4fee\")\r\nEnd Sub\r\n\r\nPrivate Sub CommandButton4_Click()\r\nCall MainWrite(\"3\", \"\u30b9\u30c8\u30c3\u30d7\", \"\")\r\nEnd Sub\r\n\r\nPrivate Sub CommandButton5_Click()\r\nCall MainWrite(\"4\", \"\u5b8c\u4e86\", \"\")\r\nWorkTimeAdd\r\nEnd Sub\r\n\r\nSub MainWrite(mark, state, typ)\r\n\r\nIf ActiveSheet.Name <> \"\u4f5c\u696d\u767b\u9332\" Then\r\n  MsgBox \"\u4f5c\u696d\u767b\u9332\u3067\u5b9f\u65bd\u3057\u3066\u304f\u3060\u3055\u3044\u3002\"\r\n  Exit Sub\r\nEnd If\r\n\r\nIf UserForm1.TextBox8.Text = \"\" Then\r\n  MsgBox \"\u5bfe\u8c61\u306eID\u304c\u898b\u3064\u304b\u308a\u307e\u305b\u3093\u3002\"\r\n  Exit Sub\r\nEnd If\r\n\r\nIf qty_parse = False Then Exit Sub\r\n\r\nSet w = Sheets(\"\u4f5c\u696d\u767b\u9332\")\r\n\r\nserial = UserForm1.TextBox1.Text\r\n\r\nFor suffix = CInt(UserForm1.TextBox5.Text) To CInt(UserForm1.TextBox6.Text)\r\n\r\n  f = False\r\n\r\n  For r = 2 To w.UsedRange.Rows.Count\r\n    If serial = w.Cells(r, 1).Text And suffix = w.Cells(r, 2) Then\r\n      f = True\r\n      Call WriteCol(r, serial, suffix, mark, state, typ)\r\n    End If\r\n  Next r\r\n  \r\n  If f = False Then\r\n    Call WriteCol(r, serial, suffix, mark, state, typ)\r\n  End If\r\n\r\nNext suffix\r\n\r\nEnd Sub\r\n\r\nSub WriteCol(r, serial, suffix, mark, state, typ)\r\n\r\nSet w = Sheets(\"\u4f5c\u696d\u767b\u9332\")\r\n\r\nIf w.Cells(r, 3).Value = \"\u5b8c\u4e86\" Then\r\n  MsgBox serial & \"-\" & suffix & \"\u306f\u65e2\u306b\u5b8c\u4e86\u3057\u3066\u3044\u307e\u3059\u3002\"\r\n  Exit Sub\r\nEnd If\r\n\r\nSelect Case mark\r\n\r\nCase \"1\" '\u4f5c\u696d\u30b9\u30bf\u30fc\u30c8\r\n\r\n  If w.Cells(r, 3).Value = \"\u30b9\u30bf\u30fc\u30c8\" Then\r\n    MsgBox serial & \"-\" & suffix & \"\u306f\u65e2\u306b\u30b9\u30bf\u30fc\u30c8\u3057\u3066\u3044\u307e\u3059\u3002\"\r\n    Exit Sub\r\n  End If\r\n  \r\n  If w.Cells(r, 3).Value = \"\" Or w.Cells(r, 3).Value = \"\u30b9\u30c8\u30c3\u30d7\" Then\r\n    c = 8\r\n  End If\r\n  \r\nCase \"2\" '\u4fee\u6b63\u30b9\u30bf\u30fc\u30c8\r\n\r\n  If w.Cells(r, 3).Value = \"\u30b9\u30bf\u30fc\u30c8\" Then\r\n    MsgBox serial & \"-\" & suffix & \"\u306f\u65e2\u306b\u30b9\u30bf\u30fc\u30c8\u3057\u3066\u3044\u307e\u3059\u3002\"\r\n    Exit Sub\r\n  End If\r\n  \r\n  If w.Cells(r, 3).Value = \"\" Or w.Cells(r, 3).Value = \"\u30b9\u30c8\u30c3\u30d7\" Then\r\n    c = 8\r\n  End If\r\n  \r\nCase \"3\" '\u30b9\u30c8\u30c3\u30d7\r\n\r\n  If w.Cells(r, 3).Value = \"\u30b9\u30bf\u30fc\u30c8\" Then\r\n    c = 9\r\n  End If\r\n  \r\n  If w.Cells(r, 3).Value = \"\" Or w.Cells(r, 3).Value = \"\u30b9\u30c8\u30c3\u30d7\" Then\r\n    MsgBox serial & \"-\" & suffix & \"\u306f\u30b9\u30bf\u30fc\u30c8\u3057\u3066\u3044\u307e\u305b\u3093\u3002\"\r\n    Exit Sub\r\n  End If\r\n  \r\nCase \"4\" '\u6307\u5b9a\u5de5\u756a\u306e\u5b8c\u4e86\r\n\r\n  If w.Cells(r, 3).Value = \"\u30b9\u30bf\u30fc\u30c8\" Then\r\n    MsgBox serial & \"-\" & suffix & \"\u306f\u30b9\u30bf\u30fc\u30c8\u3057\u3066\u3044\u308b\u305f\u3081\u5b8c\u4e86\u3067\u304d\u307e\u305b\u3093\u3002\"\r\n    Exit Sub\r\n  End If\r\n\r\n  If w.Cells(r, 3).Value = \"\u30b9\u30c8\u30c3\u30d7\" Or w.Cells(r, 3).Value = \"\" Then\r\n    w.Cells(r, 1).Value = serial\r\n    w.Cells(r, 2).Value = suffix\r\n    w.Cells(r, 3).Value = \"\u5b8c\u4e86\"\r\n    w.Cells(r, 3).Interior.ColorIndex = 0\r\n    Exit Sub\r\n  End If\r\n  \r\nEnd Select\r\n\r\nDo While True\r\n\r\n  If w.Cells(r, c).Value = \"\" Then\r\n  \r\n    w.Cells(r, 1).Value = serial\r\n    w.Cells(r, 2).Value = suffix\r\n    w.Cells(r, 3).Value = state\r\n    \r\n    If (mark = 1 Or mark = 2) Then '\u30b9\u30bf\u30fc\u30c8\r\n      w.Cells(r, 3).Interior.Color = RGB(255, 0, 0)\r\n      w.Cells(r, 6).Value = \"\"\r\n      w.Cells(r, c - 1).Value = typ\r\n    End If\r\n    \r\n    If (mark = 3) Then '\u30b9\u30c8\u30c3\u30d7\r\n      w.Cells(r, 3).Interior.Color = RGB(255, 255, 0)\r\n      w.Cells(r, 6).Value = c - 2\r\n    End If\r\n    \r\n    w.Cells(r, c).Value = Format(Now(), \"yyyy\/mm\/dd hh:mm:00\")\r\n    \r\n    Exit Do\r\n    \r\n  End If\r\n  \r\nc = c + 3\r\nLoop\r\n\r\nEnd Sub\r\n\r\nPrivate Sub CommandButton6_Click()\r\n\r\nUserForm1.TextBox1.Text = \"\"\r\nform_clear\r\n\r\nEnd Sub\r\n<\/pre>\n
Module1<\/p>\n
Sub auto_open()\r\n\r\nSetStyle\r\n\r\nIf (StrConv(Sheets(\"\u8a2d\u5b9a\").Range(\"B5\").Value, 10) = \"on\") Then\r\n  Sheets(\"\u4f5c\u696d\u767b\u9332\").Activate\r\n  UserForm1.Show\r\nEnd If\r\n\r\nOn Error Resume Next\r\n\r\nApplication.CommandBars(\"cell\").Controls(\"\u30d5\u30a9\u30fc\u30e0\u8868\u793a\").Delete\r\nApplication.CommandBars(\"cell\").Controls(\"\u30d5\u30a9\u30fc\u30e0\u8868\u793a\").Delete\r\n\r\nWith Application.CommandBars(\"cell\").Controls.Add\r\n.Caption = \"\u30d5\u30a9\u30fc\u30e0\u8868\u793a\"\r\n.OnAction = \"FormShow\"\r\nEnd With\r\n\r\nEnd Sub\r\n\r\nSub auto_close()\r\n\r\nOn Error Resume Next\r\n\r\nApplication.CommandBars(\"cell\").Controls(\"\u30d5\u30a9\u30fc\u30e0\u8868\u793a\").Delete\r\nApplication.CommandBars(\"cell\").Controls(\"\u30d5\u30a9\u30fc\u30e0\u8868\u793a\").Delete\r\n\r\nEnd Sub\r\n\r\nSub FormShow()\r\n\r\nSheets(\"\u4f5c\u696d\u767b\u9332\").Activate\r\nUserForm1.Show\r\n\r\nEnd Sub\r\n\r\nSub SetStyle()\r\n\r\nSheets(\"\u767b\u9332\u8005\").Cells(3, 2).NumberFormatLocal = \"@\"\r\nSheets(\"\u767b\u9332\u8005\").Cells(5, 2).NumberFormatLocal = \"@\"\r\nSheets(\"\u767b\u9332\u8005\").Cells(7, 2).NumberFormatLocal = \"@\"\r\nSheets(\"\u767b\u9332\u8005\").Cells(11, 2).NumberFormatLocal = \"@\"\r\n\r\nSheets(\"\u4f5c\u696d\u767b\u9332\").Cells.NumberFormatLocal = \"\"\r\nSheets(\"\u4f5c\u696d\u767b\u9332\").Columns(\"A\").NumberFormatLocal = \"@\"\r\n\r\nFor c = 7 To 100 Step 3\r\n\r\n  Sheets(\"\u4f5c\u696d\u767b\u9332\").Cells(1, c).Value = \"\u7a2e\"\r\n  Sheets(\"\u4f5c\u696d\u767b\u9332\").Cells(1, c + 1).Value = \"\u30b9\u30bf\u30fc\u30c8\"\r\n  Sheets(\"\u4f5c\u696d\u767b\u9332\").Cells(1, c + 2).Value = \"\u30b9\u30c8\u30c3\u30d7\"\r\n  \r\n  Sheets(\"\u4f5c\u696d\u767b\u9332\").Columns(c).NumberFormatLocal = \"\"\r\n  Sheets(\"\u4f5c\u696d\u767b\u9332\").Columns(c + 1).NumberFormatLocal = \"mm\/dd hh:mm\"\r\n  Sheets(\"\u4f5c\u696d\u767b\u9332\").Columns(c + 2).NumberFormatLocal = \"mm\/dd hh:mm\"\r\n\r\n  Sheets(\"\u4f5c\u696d\u767b\u9332\").Columns(c).ColumnWidth = \"2.5\"\r\n  Sheets(\"\u4f5c\u696d\u767b\u9332\").Columns(c + 1).ColumnWidth = \"12\"\r\n  Sheets(\"\u4f5c\u696d\u767b\u9332\").Columns(c + 2).ColumnWidth = \"12\"\r\n  \r\nNext c\r\n\r\nEnd Sub\r\n\r\nSub WorkTimeAdd()\r\n\r\nSet w = Sheets(\"\u4f5c\u696d\u767b\u9332\")\r\n\r\nFor r = 2 To w.UsedRange.Rows.Count\r\n\r\n  w.Cells(r, 4) = \"\"\r\n  w.Cells(r, 5) = \"\"\r\n\r\n  If w.Cells(r, 3).Text = \"\u5b8c\u4e86\" And w.Cells(r, 6).Text <> \"\" Then\r\n  \r\n    n = 0\r\n    f = 0\r\n  \r\n    For c = 7 To w.Cells(r, 6).Text Step 3\r\n    \r\n      Select Case w.Cells(r, c).Text\r\n        Case \"\u4f5c\"\r\n          n = n + WorkTime(w.Cells(r, c + 1).Text, w.Cells(r, c + 2).Text)\r\n        Case \"\u4fee\"\r\n          f = f + WorkTime(w.Cells(r, c + 1).Text, w.Cells(r, c + 2).Text)\r\n      End Select\r\n      \r\n    Next c\r\n    \r\n    w.Cells(r, 4) = Int(n \/ 60) & \".\" & Format(n Mod 60, \"00\") & \" H\"\r\n    w.Cells(r, 5) = Int(f \/ 60) & \".\" & Format(f Mod 60, \"00\") & \" H\"\r\n      \r\n  End If\r\n\r\nNext r\r\n\r\nw.Activate\r\n\r\nEnd Sub\r\n\r\nFunction WorkTime(date_s, date_e)\r\n\r\nOn Error GoTo try\r\n\r\ndate_s = CDate(date_s)\r\ndate_e = CDate(date_e)\r\n\r\nts = 0\r\n\r\nDo While date_s < date_e\r\n\r\n  ts = ts + 1\r\n \r\n  If date_s >= CDate(Year(date_s) & \"\/\" & Month(date_s) & \"\/\" & Day(date_s) & \" 10:00:00\") And _\r\n      date_s < CDate(Year(date_s) & \"\/\" & Month(date_s) & \"\/\" & Day(date_s) & \" 10:15:00\") Then\r\n    ts = ts - 1\r\n  End If\r\n  \r\n  If date_s >= CDate(Year(date_s) & \"\/\" & Month(date_s) & \"\/\" & Day(date_s) & \" 12:00:00\") And _\r\n      date_s < CDate(Year(date_s) & \"\/\" & Month(date_s) & \"\/\" & Day(date_s) & \" 12:50:00\") Then\r\n    ts = ts - 1\r\n  End If\r\n  \r\n  If date_s >= CDate(Year(date_s) & \"\/\" & Month(date_s) & \"\/\" & Day(date_s) & \" 15:00:00\") And _\r\n      date_s < CDate(Year(date_s) & \"\/\" & Month(date_s) & \"\/\" & Day(date_s) & \" 15:15:00\") Then\r\n    ts = ts - 1\r\n  End If\r\n  \r\n  If date_s >= CDate(Year(date_s) & \"\/\" & Month(date_s) & \"\/\" & Day(date_s) & \" 17:20:00\") And _\r\n      date_s < CDate(Year(date_s) & \"\/\" & Month(date_s) & \"\/\" & Day(date_s) & \" 17:30:00\") Then\r\n    ts = ts - 1\r\n  End If\r\n  \r\n  date_s = DateAdd(\"n\", 1, date_s)\r\n  \r\nLoop\r\n\r\nWorkTime = ts\r\n\r\nExit Function\r\n\r\ntry:\r\n\r\n  WorkTime = 0\r\n\r\nEnd Function\r\n<\/pre>\n
Sheet1(\u767b\u9332\u8005)<\/p>\n
Function BuildQuery(mark)\r\n\r\nq_i = Sheets(\"\u767b\u9332\u8005\").Range(\"B21\").Value\r\nq_c = Sheets(\"\u767b\u9332\u8005\").Range(\"B3\").Value\r\nq_m = Sheets(\"\u767b\u9332\u8005\").Range(\"B5\").Value\r\nq_s = Sheets(\"\u767b\u9332\u8005\").Range(\"B7\").Value\r\nq_q = Sheets(\"\u767b\u9332\u8005\").Range(\"B9\").Value\r\nq_n = Sheets(\"\u767b\u9332\u8005\").Range(\"B11\").Value\r\n\r\nq_i = StrConv(q_i, vbNarrow)\r\nq_s = StrConv(q_s, vbNarrow)\r\nq_q = StrConv(q_q, vbNarrow)\r\n\r\nq_i = Replace(q_i, \"'\", \"\u2019\")\r\nq_c = Replace(q_c, \"'\", \"\u2019\")\r\nq_m = Replace(q_m, \"'\", \"\u2019\")\r\nq_s = Replace(q_s, \"'\", \"\u2019\")\r\nq_q = Replace(q_q, \"'\", \"\u2019\")\r\nq_n = Replace(q_n, \"'\", \"\u2019\")\r\n\r\nq_i = Replace(q_i, \"\"\"\", \"\u2019\")\r\nq_c = Replace(q_c, \"\"\"\", \"\u2019\")\r\nq_m = Replace(q_m, \"\"\"\", \"\u2019\")\r\nq_s = Replace(q_s, \"\"\"\", \"\u2019\")\r\nq_q = Replace(q_q, \"\"\"\", \"\u2019\")\r\nq_n = Replace(q_n, \"\"\"\", \"\u2019\")\r\n\r\nIf mark = \"s1\" Then q = \"select ID,customer,model,serial,quantity,notes from orders where visible = 'true'\"\r\nIf mark = \"s2\" Then q = \"select ID,customer,model,serial,quantity,notes from orders where visible = 'false'\"\r\n\r\nIf mark = \"i\" Then q = \"insert into orders (customer,model,serial,quantity,notes,visible) values ('\" & q_c & \"','\" & q_m & \"','\" & q_s & \"',\" & q_q & \",'\" & q_n & \"','true');\"\r\nIf mark = \"u\" Then q = \"update orders set customer = '\" & q_c & \"', model = '\" & q_m & \"', serial = '\" & q_s & \"', quantity = \" & q_q & \", notes = '\" & q_n & \"' where ID = \" & q_i & \";\"\r\nIf mark = \"d\" Then q = \"delete from orders where ID = \" & q_i & \";\"\r\nIf mark = \"h\" Then q = \"update orders set visible = 'false' where ID = \" & q_i & \";\"\r\n\r\nBuildQuery = q: Debug.Print q\r\n\r\nEnd Function\r\n\r\nPrivate Sub CommandButton6_Click()\r\n\r\nIf db_chk = False Then Exit Sub\r\n\r\ngrid_clear\r\nIf grid_load(\"s1\") = False Then Exit Sub\r\n\r\nform_clear\r\n\r\nEnd Sub\r\n\r\nPrivate Sub CommandButton5_Click()\r\n\r\nIf db_chk = False Then Exit Sub\r\n\r\ngrid_clear\r\nIf grid_load(\"s2\") = False Then Exit Sub\r\n\r\nform_clear\r\n\r\nEnd Sub\r\n\r\nPrivate Sub CommandButton1_Click()\r\n\r\nIf db_chk = False Then Exit Sub\r\nIf form_chk = False Then Exit Sub\r\nIf qty_parse = False Then Exit Sub\r\n\r\nIf form_query(\"i\") = False Then Exit Sub\r\n\r\ngrid_clear\r\nIf grid_load(\"s1\") = False Then Exit Sub\r\n\r\nform_clear\r\n \r\nEnd Sub\r\n\r\nPrivate Sub CommandButton2_Click()\r\n\r\nIf db_chk = False Then Exit Sub\r\nIf id_chk = False Then Exit Sub\r\nIf form_chk = False Then Exit Sub\r\nIf qty_parse = False Then Exit Sub\r\n\r\nIf form_query(\"u\") = False Then Exit Sub\r\n\r\ngrid_clear\r\nIf grid_load(\"s1\") = False Then Exit Sub\r\n\r\nform_clear\r\n\r\nEnd Sub\r\n\r\nPrivate Sub CommandButton3_Click()\r\n\r\nIf db_chk = False Then Exit Sub\r\nIf id_chk = False Then Exit Sub\r\n\r\nyn = MsgBox(\"\u524a\u9664\u3057\u307e\u3059\u304b\uff1f\", vbYesNo)\r\nIf yn = vbNo Then Exit Sub\r\n\r\nIf form_query(\"d\") = False Then Exit Sub\r\n\r\ngrid_clear\r\nIf grid_load(\"s1\") = False Then Exit Sub\r\n\r\nform_clear\r\n\r\nEnd Sub\r\n\r\nPrivate Sub CommandButton4_Click()\r\n\r\nIf db_chk = False Then Exit Sub\r\nIf id_chk = False Then Exit Sub\r\n\r\nIf form_query(\"h\") = False Then Exit Sub\r\n\r\ngrid_clear\r\nIf grid_load(\"s1\") = False Then Exit Sub\r\n\r\nform_clear\r\n\r\nEnd Sub\r\n\r\nFunction id_chk()\r\n\r\nOn Error GoTo try\r\n\r\nIf Sheets(\"\u767b\u9332\u8005\").Range(\"B21\").Value = \"\" Then GoTo try\r\n\r\nv = CLng(Sheets(\"\u767b\u9332\u8005\").Range(\"B21\").Value)\r\n\r\nid_chk = True\r\n\r\nExit Function\r\n\r\ntry:\r\n\r\n  MsgBox \"ID\u304c\u6b63\u3057\u304f\u3042\u308a\u307e\u305b\u3093\u3002\"\r\n  id_chk = False\r\n\r\nEnd Function\r\n\r\nFunction form_chk()\r\n\r\nIf Sheets(\"\u767b\u9332\u8005\").Range(\"B3\").Value = \"\" Then GoTo try\r\nIf Sheets(\"\u767b\u9332\u8005\").Range(\"B5\").Value = \"\" Then GoTo try\r\nIf Sheets(\"\u767b\u9332\u8005\").Range(\"B7\").Value = \"\" Then GoTo try\r\nIf Sheets(\"\u767b\u9332\u8005\").Range(\"B9\").Value = \"\" Then GoTo try\r\n\r\nform_chk = True\r\n\r\nExit Function\r\n\r\ntry:\r\n\r\n  MsgBox \"\u5165\u529b\u304c\u4e0d\u8db3\u3057\u3066\u3044\u307e\u3059\u3002\"\r\n  form_chk = False\r\n\r\nEnd Function\r\n\r\nSub grid_clear()\r\n\r\nSheets(\"\u767b\u9332\u8005\").Range(\"G4:L1000\").Delete\r\n\r\nEnd Sub\r\n\r\nSub form_clear()\r\n\r\nSheets(\"\u767b\u9332\u8005\").Range(\"B3\").Value = \"\"\r\nSheets(\"\u767b\u9332\u8005\").Range(\"B5\").Value = \"\"\r\nSheets(\"\u767b\u9332\u8005\").Range(\"B7\").Value = \"\"\r\nSheets(\"\u767b\u9332\u8005\").Range(\"B9\").Value = \"\"\r\nSheets(\"\u767b\u9332\u8005\").Range(\"B11\").Value = \"\"\r\nSheets(\"\u767b\u9332\u8005\").Range(\"B21\").Value = \"\"\r\n\r\nEnd Sub\r\n\r\nFunction qty_parse()\r\n\r\nOn Error GoTo try\r\n\r\nqty = CLng(Sheets(\"\u767b\u9332\u8005\").Range(\"B9\").Value)\r\nIf qty = 0 Then GoTo try\r\n\r\nqty_parse = True\r\n\r\nExit Function\r\n\r\ntry:\r\n\r\n  MsgBox \"\u6570\u91cf\u304c\u6b63\u3057\u304f\u3042\u308a\u307e\u305b\u3093\u3002\"\r\n  qty_parse = False\r\n\r\nEnd Function\r\n\r\nFunction form_query(mark)\r\n\r\nOn Error GoTo try\r\n\r\nSet cn = CreateObject(\"ADODB.Connection\")\r\ncn.Open \"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\" & Sheets(\"\u8a2d\u5b9a\").Range(\"B3\").Value & \";\"\r\ncn.Execute BuildQuery(mark)\r\n\r\nIf cn.state = 1 Then cn.Close\r\nSet cn = Nothing\r\n\r\nform_query = True\r\n\r\nExit Function\r\n\r\ntry:\r\n\r\n  If cn.state = 1 Then cn.Close\r\n  Set cn = Nothing\r\n  \r\n  MsgBox Err.Description\r\n  form_query = False\r\n\r\nEnd Function\r\n\r\nFunction grid_load(mark)\r\n\r\nOn Error GoTo try\r\n\r\nSet cn = CreateObject(\"ADODB.Connection\")\r\nSet rs = CreateObject(\"ADODB.Recordset\")\r\n\r\ncn.Open \"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\" & Sheets(\"\u8a2d\u5b9a\").Range(\"B3\").Value & \";\"\r\nrs.Open BuildQuery(mark), cn\r\n\r\nr = 4\r\nDo Until rs.EOF\r\n\r\n  Sheets(\"\u767b\u9332\u8005\").Cells(r, 7).Value = rs(0)\r\n  Sheets(\"\u767b\u9332\u8005\").Cells(r, 8).Value = rs(1)\r\n  Sheets(\"\u767b\u9332\u8005\").Cells(r, 9).Value = rs(2)\r\n  Sheets(\"\u767b\u9332\u8005\").Cells(r, 10).Value = rs(3)\r\n  Sheets(\"\u767b\u9332\u8005\").Cells(r, 11).Value = rs(4)\r\n  Sheets(\"\u767b\u9332\u8005\").Cells(r, 12).Value = rs(5)\r\n\r\n  Sheets(\"\u767b\u9332\u8005\").Range(\"G\" & r & \":L\" & r & \"\").Borders.LineStyle = True\r\n\r\nr = r + 1\r\nrs.movenext\r\nLoop\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\ngrid_load = True\r\n\r\nExit Function\r\n\r\ntry:\r\n\r\n  If rs.state = 1 Then rs.Close\r\n  Set rs = Nothing\r\n\r\n  If cn.state = 1 Then cn.Close\r\n  Set cn = Nothing\r\n\r\n  grid_load = False\r\n    \r\nEnd Function\r\n\r\nFunction db_chk()\r\n\r\nOn Error GoTo try\r\n\r\nSet cn = CreateObject(\"ADODB.Connection\")\r\ncn.Open \"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\" & Sheets(\"\u8a2d\u5b9a\").Range(\"B3\").Value & \";\"\r\n\r\nIf cn.state = 1 Then cn.Close\r\nSet cn = Nothing\r\n\r\ndb_chk = True\r\n\r\nExit Function\r\n\r\ntry:\r\n\r\n  If cn.state = 1 Then cn.Close\r\n  Set cn = Nothing\r\n  \r\n  MsgBox \"DB\u306b\u63a5\u7d9a\u3067\u304d\u307e\u305b\u3093\u3002\"\r\n  db_chk = False\r\n  \r\nEnd Function\r\n\r\nPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)\r\n\r\nIf Target.Row >= 4 And Target.Column = 7 And Target.Text <> \"\" Then\r\n\r\n  ActiveSheet.Cells(3, 2).Value = ActiveSheet.Cells(Target.Row, 8).Value\r\n  ActiveSheet.Cells(5, 2).Value = ActiveSheet.Cells(Target.Row, 9).Value\r\n  ActiveSheet.Cells(7, 2).Value = ActiveSheet.Cells(Target.Row, 10).Value\r\n  ActiveSheet.Cells(9, 2).Value = ActiveSheet.Cells(Target.Row, 11).Value\r\n  ActiveSheet.Cells(11, 2).Value = ActiveSheet.Cells(Target.Row, 12).Value\r\n  ActiveSheet.Cells(21, 2).Value = ActiveSheet.Cells(Target.Row, 7).Value\r\n  \r\nElseIf Target.Row >= 4 And Target.Column = 7 And Target.Text = \"\" Then\r\n\r\n  ActiveSheet.Cells(3, 2).Value = \"\"\r\n  ActiveSheet.Cells(5, 2).Value = \"\"\r\n  ActiveSheet.Cells(7, 2).Value = \"\"\r\n  ActiveSheet.Cells(9, 2).Value = \"\"\r\n  ActiveSheet.Cells(11, 2).Value = \"\"\r\n  ActiveSheet.Cells(21, 2).Value = \"\"\r\n  \r\nEnd If\r\n\r\nEnd Sub\r\n<\/pre>\n
Sheet3(\u4f5c\u696d\u767b\u9332)<\/p>\n
Private Sub Worksheet_SelectionChange(ByVal Target As Range)\r\n\r\nIf UserForm1.Visible And UserForm1.TextBox8.Text = \"\" And _\r\n  Target.Row >= 2 And Target.Column = 1 And Target.Text <> \"\" Then\r\n  UserForm1.TextBox1.Text = Target.Text\r\nEnd If\r\n\r\nEnd Sub\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
\u524d\u56de\u306eC#\u7248\u3092\u4e2d\u6b62\u3057\u3066\u304b\u3089\u5373\u5e2d\u3067\u4f5c\u3063\u305f\u3002C#\u306e\u3068\u304d\u306fMySQL\u3060\u3063\u305f\u304c\u3001\u4eca\u56de\u306fmdb\u30d5\u30a1\u30a4\u30eb\u3068\u3057\u305f\u3002\u4f7f\u3046\u306e\u304c1~2\u540d\u3067\u3042\u3063\u3066\u3082\u8907\u6570\u7b87\u6240\u3067\u958b\u304f\u5834\u5408\u3001\u30c7\u30fc\u30bf\u306f\u5225\u30d5\u30a1\u30a4\u30eb\u306e\u65b9\u304c\u3044\u3044\u3060\u308d\u3046\u3068\u5224\u65ad\u3002 \u4eca\u56de\u306f\u4f7f\u3046\u5834\u9762\u3092\u60f3\u50cf\u3067\u304d\u305f\u306e\u3067 … <\/p>\n
“VBA \u5de5\u6570\u7ba1\u7406\u30d7\u30ed\u30b0\u30e9\u30e0” \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[5],"class_list":["post-1966","post","type-post","status-publish","format-standard","hentry","category-1","tag-vba"],"_links":{"self":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/1966","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1966"}],"version-history":[{"count":9,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/1966\/revisions"}],"predecessor-version":[{"id":6297,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/1966\/revisions\/6297"}],"wp:attachment":[{"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1966"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1966"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1966"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}