跟你一段我写的Public Function Create00020() Dim rs As DAO.Recordset Dim lonOld, lonNew As Long Dim rows As Long Dim strSheetName As String Dim strCode As String Set rs = dDB.OpenRecordset("SELECT * FROM table", dbOpenDynaset) If Not rs.EOF Or Not rs.BOF Then rs.MoveFirst lonRows = 1 lonOld = Sheet1.GetPos("00020") lonNew = lonOld - 1 strSheetName = Sheets(lonOld).Name Do Until rs.EOF strCode = rs.Fields(0).Value Sheets(lonOld).Select Sheets(lonOld).Copy after:=Sheets(lonNew) lonOld = lonOld + 1 lonNew = lonNew + 1 Sheets(lonNew).Name = strSheetName & "_" & strCode Call Write00020(lonNew, rs) lonRows = lonRows + 1 rs.MoveNext Loop Sheets(lonOld).Select Else Exit Function End If End Function '**************************************************************** 'write sheet by:RH '**************************************************************** Public Function Write00020(lonOld As Long, rs As DAO.Recordset) ' Start Write Excel File ' Write テーブルコード Dim Row_00020 As Integer Dim i As Integer Sheets(lonOld).Range("B3").Value = Mid(Trim(rs.Fields(0)), 1, 1) Sheets(lonOld).Range("C3").Value = Mid(Trim(rs.Fields(0)), 2, 1) Sheets(lonOld).Range("D3").Value = Mid(Trim(rs.Fields(0)), 3, 1) Sheets(lonOld).Range("E3").Value = Mid(Trim(rs.Fields(0)), 4, 1) Sheets(lonOld).Range("F3").Value = Mid(Trim(rs.Fields(0)), 5, 1) Sheets(lonOld).Range("G3").Value = Mid(Trim(rs.Fields(0)), 6, 1)
Sheets(lonOld).Range("P12").Value = rs.Fields(37) Sheets(lonOld).Range("Q13").Value = rs.Fields(48) Sheets(lonOld).Range("U13").Value = rs.Fields(49) Sheets(lonOld).Range("Q14").Value = rs.Fields(58) Sheets(lonOld).Range("V14").Value = rs.Fields(60) Sheets(lonOld).Range("Q16").Value = rs.Fields(68) Sheets(lonOld).Range("V16").Value = rs.Fields(70) Sheets(lonOld).Range("Q18").Value = rs.Fields(78) Sheets(lonOld).Range("V18").Value = rs.Fields(80) Sheets(lonOld).Range("W20").Value = rs.Fields(91) i = 93 For Row_00020 = 22 To 33 Sheets(lonOld).Range("L" & Row_00020).Value = rs.Fields(i) Sheets(lonOld).Range("M" & Row_00020).Value = rs.Fields(i + 1) Sheets(lonOld).Range("N" & Row_00020).Value = rs.Fields(i + 2) Sheets(lonOld).Range("O" & Row_00020).Value = rs.Fields(i + 3) If Row_00020 <> 31 And Row_00020 <> 33 Then Sheets(lonOld).Range("P" & Row_00020).Value = rs.Fields(i + 4) End If If Row_00020 <> 23 And Row_00020 <> 24 Then Sheets(lonOld).Range("Q" & Row_00020).Value = rs.Fields(i + 5) End If If Row_00020 <> 23 And Row_00020 <> 24 Then Sheets(lonOld).Range("U" & Row_00020).Value = rs.Fields(i + 6) End If If Row_00020 <> 23 And Row_00020 <> 24 And Row_00020 <> 25 And Row_00020 <> 26 And Row_00020 <> 27 And Row_00020 <> 30 And Row_00020 <> 33 Then Sheets(lonOld).Range("V" & Row_00020).Value = rs.Fields(i + 7) End If If Row_00020 = 22 Or Row_00020 = 32 Then Sheets(lonOld).Range("W" & Row_00020).Value = rs.Fields(i + 8) End If If Row_00020 = 22 Or Row_00020 = 32 Then Sheets(lonOld).Range("X" & Row_00020).Value = rs.Fields(i + 9) End If i = i + 10 Next Row_00020
Dim rs As DAO.Recordset
Dim lonOld, lonNew As Long
Dim rows As Long
Dim strSheetName As String
Dim strCode As String
Set rs = dDB.OpenRecordset("SELECT * FROM table", dbOpenDynaset) If Not rs.EOF Or Not rs.BOF Then
rs.MoveFirst
lonRows = 1
lonOld = Sheet1.GetPos("00020")
lonNew = lonOld - 1
strSheetName = Sheets(lonOld).Name
Do Until rs.EOF
strCode = rs.Fields(0).Value
Sheets(lonOld).Select
Sheets(lonOld).Copy after:=Sheets(lonNew)
lonOld = lonOld + 1
lonNew = lonNew + 1
Sheets(lonNew).Name = strSheetName & "_" & strCode
Call Write00020(lonNew, rs)
lonRows = lonRows + 1
rs.MoveNext
Loop
Sheets(lonOld).Select
Else
Exit Function
End If
End Function
'****************************************************************
'write sheet by:RH
'****************************************************************
Public Function Write00020(lonOld As Long, rs As DAO.Recordset)
' Start Write Excel File
' Write テーブルコード
Dim Row_00020 As Integer
Dim i As Integer
Sheets(lonOld).Range("B3").Value = Mid(Trim(rs.Fields(0)), 1, 1)
Sheets(lonOld).Range("C3").Value = Mid(Trim(rs.Fields(0)), 2, 1)
Sheets(lonOld).Range("D3").Value = Mid(Trim(rs.Fields(0)), 3, 1)
Sheets(lonOld).Range("E3").Value = Mid(Trim(rs.Fields(0)), 4, 1)
Sheets(lonOld).Range("F3").Value = Mid(Trim(rs.Fields(0)), 5, 1)
Sheets(lonOld).Range("G3").Value = Mid(Trim(rs.Fields(0)), 6, 1)
Sheets(lonOld).Range("R1").Value = rs.Fields(1)
Sheets(lonOld).Range("R2").Value = rs.Fields(2)
Sheets(lonOld).Range("P12").Value = rs.Fields(37)
Sheets(lonOld).Range("Q13").Value = rs.Fields(48)
Sheets(lonOld).Range("U13").Value = rs.Fields(49)
Sheets(lonOld).Range("Q14").Value = rs.Fields(58)
Sheets(lonOld).Range("V14").Value = rs.Fields(60)
Sheets(lonOld).Range("Q16").Value = rs.Fields(68)
Sheets(lonOld).Range("V16").Value = rs.Fields(70)
Sheets(lonOld).Range("Q18").Value = rs.Fields(78)
Sheets(lonOld).Range("V18").Value = rs.Fields(80)
Sheets(lonOld).Range("W20").Value = rs.Fields(91)
i = 93
For Row_00020 = 22 To 33
Sheets(lonOld).Range("L" & Row_00020).Value = rs.Fields(i)
Sheets(lonOld).Range("M" & Row_00020).Value = rs.Fields(i + 1)
Sheets(lonOld).Range("N" & Row_00020).Value = rs.Fields(i + 2)
Sheets(lonOld).Range("O" & Row_00020).Value = rs.Fields(i + 3)
If Row_00020 <> 31 And Row_00020 <> 33 Then
Sheets(lonOld).Range("P" & Row_00020).Value = rs.Fields(i + 4)
End If
If Row_00020 <> 23 And Row_00020 <> 24 Then
Sheets(lonOld).Range("Q" & Row_00020).Value = rs.Fields(i + 5)
End If
If Row_00020 <> 23 And Row_00020 <> 24 Then
Sheets(lonOld).Range("U" & Row_00020).Value = rs.Fields(i + 6)
End If
If Row_00020 <> 23 And Row_00020 <> 24 And Row_00020 <> 25 And Row_00020 <> 26 And Row_00020 <> 27 And Row_00020 <> 30 And Row_00020 <> 33 Then
Sheets(lonOld).Range("V" & Row_00020).Value = rs.Fields(i + 7)
End If
If Row_00020 = 22 Or Row_00020 = 32 Then
Sheets(lonOld).Range("W" & Row_00020).Value = rs.Fields(i + 8)
End If
If Row_00020 = 22 Or Row_00020 = 32 Then
Sheets(lonOld).Range("X" & Row_00020).Value = rs.Fields(i + 9)
End If
i = i + 10
Next Row_00020
End Function
ws.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
ws.Range(ws.Cells(1, 1), _
ws.Cells(1, rs.Fields.Count)).Font.Bold = True
ws.Range("A2").CopyFromRecordset rs