Option Explicit

'表名到字段名的距・

Private Const cTblFieldSepRow = 6

'

Private Const cFieldTypeSepRow = 1

'

Private Const cFieldKeySepRow = 3

'テーブル・

Private Const cTblSchemName = "MOB1DB"

'数字型

Private Const cFieldTypeNumber = "N"

'graphic型

Private Const cFieldTypeGraphic = "G"

Private arrCol(256) As String

'テーブル・

Private StrTableName As String

'無用

Private StrFieldKey As String

'

Private StrFieldType As String

'

Private StrFieldName As String

'

Private StrFieldColName As String

'列数字にな・

Private StrColName As String

'SQL・

Private strSql As String

'

Private StrFieldValue As String

Public Function getInsertSql(Row As Integer, size As Integer) As String

'行

Dim intRowField As Integer

Dim isFirst As Boolean

Dim index As Integer

Call initArrCol(arrCol)

For index = Row To 1 Step -1

If Left(Range("A" & Trim(str(index))).Value, cTblFieldSepRow) = cTblSchemName Then

intRowField = index + cTblFieldSepRow

Exit For

End If

Next

    

StrTableName = Range("A" & intRowField - cTblFieldSepRow).Value

isFirst = True

strSql = "INSERT INTO " & StrTableName

For index = 1 To size

StrColName = arrCol(index)

StrFieldKey = Range(StrColName & intRowField).Value

StrFieldType = Range(StrColName & intRowField).Value

StrFieldName = Range(StrColName & intRowField).Value

If isFirst Then

strSql = strSql & "(" & StrFieldName

isFirst = False

Else

strSql = strSql & "," & StrFieldName

End If

Next

strSql = strSql & ")VALUES"

isFirst = True

For index = 1 To size

StrColName = arrCol(index)

StrFieldKey = Range(StrColName & intRowField - cFieldKeySepRow).Value

StrFieldType = Range(StrColName & intRowField - cFieldTypeSepRow).Value

StrFieldName = Range(StrColName & intRowField).Value

StrFieldValue = Range(StrColName & Row).Value

If isFirst Then

strSql = strSql & "("

isFirst = False

Else

strSql = strSql & ","

End If

If StrFieldType = cFieldTypeNumber Then

strSql = strSql & StrFieldValue

ElseIf StrFieldType = cFieldTypeGraphic Then

strSql = strSql & "g'" & StrFieldValue & "'"

Else

strSql = strSql & "'" & StrFieldValue & "'"

End If

Next

strSql = strSql & ");"

getInsertSql = strSql

End Function

Private Sub initArrCol(arrCol() As String)

arrCol(1) = "A"

arrCol(2) = "B"

arrCol(3) = "C"

arrCol(4) = "D"

arrCol(5) = "E"

arrCol(6) = "F"

arrCol(7) = "G"

arrCol(8) = "H"

arrCol(9) = "I"

arrCol(10) = "J"

arrCol(11) = "K"

arrCol(12) = "L"

arrCol(13) = "M"

arrCol(14) = "N"

arrCol(15) = "O"

arrCol(16) = "P"

arrCol(17) = "Q"

arrCol(18) = "R"

arrCol(19) = "S"

arrCol(20) = "T"

arrCol(21) = "U"

arrCol(22) = "V"

arrCol(23) = "W"

arrCol(24) = "X"

arrCol(25) = "Y"

arrCol(26) = "Z"

arrCol(27) = "AA"

arrCol(28) = "AB"

arrCol(29) = "AC"

arrCol(30) = "AD"

arrCol(31) = "AE"

arrCol(32) = "AF"

arrCol(33) = "AG"

arrCol(34) = "AH"

arrCol(35) = "AI"

arrCol(36) = "AJ"

arrCol(37) = "AK"

arrCol(38) = "AL"

arrCol(39) = "AM"

arrCol(40) = "AN"

arrCol(41) = "AO"

arrCol(42) = "AP"

arrCol(43) = "AQ"

arrCol(44) = "AR"

arrCol(45) = "AS"

arrCol(46) = "AT"

arrCol(47) = "AU"

arrCol(48) = "AV"

arrCol(49) = "AW"

arrCol(50) = "AX"

arrCol(51) = "AY"

arrCol(52) = "AZ"

arrCol(53) = "BA"

arrCol(54) = "BB"

arrCol(55) = "BC"

arrCol(56) = "BD"

arrCol(57) = "BE"

arrCol(58) = "BF"

arrCol(59) = "BG"

arrCol(60) = "BH"

arrCol(61) = "BI"

arrCol(62) = "BJ"

arrCol(63) = "BK"

arrCol(64) = "BL"

arrCol(65) = "BM"

arrCol(66) = "BN"

arrCol(67) = "BO"

arrCol(68) = "BP"

arrCol(69) = "BQ"

arrCol(70) = "BR"

arrCol(71) = "BS"

arrCol(72) = "BT"

arrCol(73) = "BU"

arrCol(74) = "BV"

arrCol(75) = "BW"

arrCol(76) = "BX"

arrCol(77) = "BY"

arrCol(78) = "BZ"

arrCol(79) = "CA"

arrCol(80) = "CB"

arrCol(81) = "CC"

arrCol(82) = "CD"

arrCol(83) = "CE"

arrCol(84) = "CF"

arrCol(85) = "CG"

arrCol(86) = "CH"

arrCol(87) = "CI"

arrCol(88) = "CJ"

arrCol(89) = "CK"

arrCol(90) = "CL"

arrCol(91) = "CM"

arrCol(92) = "CN"

arrCol(93) = "CO"

arrCol(94) = "CP"

arrCol(95) = "CQ"

arrCol(96) = "CR"

arrCol(97) = "CS"

arrCol(98) = "CT"

arrCol(99) = "CU"

arrCol(100) = "CV"

arrCol(101) = "CW"

arrCol(102) = "CX"

arrCol(103) = "CY"

arrCol(104) = "CZ"

arrCol(105) = "DA"

arrCol(106) = "DB"

arrCol(107) = "DC"

arrCol(108) = "DD"

arrCol(109) = "DE"

arrCol(110) = "DF"

arrCol(111) = "DG"

arrCol(112) = "DH"

arrCol(113) = "DI"

arrCol(114) = "DJ"

arrCol(115) = "DK"

arrCol(116) = "DL"

arrCol(117) = "DM"

arrCol(118) = "DN"

arrCol(119) = "DO"

arrCol(120) = "DP"

arrCol(121) = "DQ"

arrCol(122) = "DR"

arrCol(123) = "DS"

arrCol(124) = "DT"

arrCol(125) = "DU"

arrCol(126) = "DV"

arrCol(127) = "DW"

arrCol(128) = "DX"

arrCol(129) = "DY"

arrCol(130) = "DZ"

arrCol(131) = "EA"

arrCol(132) = "EB"

arrCol(133) = "EC"

arrCol(134) = "ED"

arrCol(135) = "EE"

arrCol(136) = "EF"

arrCol(137) = "EG"

arrCol(138) = "EH"

arrCol(139) = "EI"

arrCol(140) = "EJ"

arrCol(141) = "EK"

arrCol(142) = "EL"

arrCol(143) = "EM"

arrCol(144) = "EN"

arrCol(145) = "EO"

arrCol(146) = "EP"

arrCol(147) = "EQ"

arrCol(148) = "ER"

arrCol(149) = "ES"

arrCol(150) = "ET"

arrCol(151) = "EU"

arrCol(152) = "EV"

arrCol(153) = "EW"

arrCol(154) = "EX"

arrCol(155) = "EY"

arrCol(156) = "EZ"

arrCol(157) = "FA"

arrCol(158) = "FB"

arrCol(159) = "FC"

arrCol(160) = "FD"

arrCol(161) = "FE"

arrCol(162) = "FF"

arrCol(163) = "FG"

arrCol(164) = "FH"

arrCol(165) = "FI"

arrCol(166) = "FJ"

arrCol(167) = "FK"

arrCol(168) = "FL"

arrCol(169) = "FM"

arrCol(170) = "FN"

arrCol(171) = "FO"

arrCol(172) = "FP"

arrCol(173) = "FQ"

arrCol(174) = "FR"

arrCol(175) = "FS"

arrCol(176) = "FT"

arrCol(177) = "FU"

arrCol(178) = "FV"

arrCol(179) = "FW"

arrCol(180) = "FX"

arrCol(181) = "FY"

arrCol(182) = "FZ"

arrCol(183) = "GA"

arrCol(184) = "GB"

arrCol(185) = "GC"

arrCol(186) = "GD"

arrCol(187) = "GE"

arrCol(188) = "GF"

arrCol(189) = "GG"

arrCol(190) = "GH"

arrCol(191) = "GI"

arrCol(192) = "GJ"

arrCol(193) = "GK"

arrCol(194) = "GL"

arrCol(195) = "GM"

arrCol(196) = "GN"

arrCol(197) = "GO"

arrCol(198) = "GP"

arrCol(199) = "GQ"

arrCol(200) = "GR"

arrCol(201) = "GS"

arrCol(202) = "GT"

arrCol(203) = "GU"

arrCol(204) = "GV"

arrCol(205) = "GW"

arrCol(206) = "GX"

arrCol(207) = "GY"

arrCol(208) = "GZ"

arrCol(209) = "HA"

arrCol(210) = "HB"

arrCol(211) = "HC"

arrCol(212) = "HD"

arrCol(213) = "HE"

arrCol(214) = "HF"

arrCol(215) = "HG"

arrCol(216) = "HH"

arrCol(217) = "HI"

arrCol(218) = "HJ"

arrCol(219) = "HK"

arrCol(220) = "HL"

arrCol(221) = "HM"

arrCol(222) = "HN"

arrCol(223) = "HO"

arrCol(224) = "HP"

arrCol(225) = "HQ"

arrCol(226) = "HR"

arrCol(227) = "HS"

arrCol(228) = "HT"

arrCol(229) = "HU"

arrCol(230) = "HV"

arrCol(231) = "HW"

arrCol(232) = "HX"

arrCol(233) = "HY"

arrCol(234) = "HZ"

arrCol(235) = "IA"

arrCol(236) = "IB"

arrCol(237) = "IC"

arrCol(238) = "ID"

arrCol(239) = "IE"

arrCol(240) = "IF"

arrCol(241) = "IG"

arrCol(242) = "IH"

arrCol(243) = "II"

arrCol(244) = "IJ"

arrCol(245) = "IK"

arrCol(246) = "IL"

arrCol(247) = "IM"

arrCol(248) = "IN"

arrCol(249) = "IO"

arrCol(250) = "IP"

arrCol(251) = "IQ"

arrCol(252) = "IR"

arrCol(253) = "IS"

arrCol(254) = "IT"

arrCol(255) = "IU"

arrCol(256) = "IV"

End Sub