4月のカレンダー
 123456
78910111213
14151617181920
21222324252627
282930    
最近の日記
全体の新着
各月の日記
ユーザーRSS
Pitekanさんの公開日記
02月04日
22:43
前日(2/3)の日記の作業の逆のマクロを作成しました。
ただし、前日の解説の中の???の部分について、少し思い出したので
今回のマクロにはその追加部分も含まれています。
近いうちに2/3の日記の追加修正版を書きます。
追加部分は選択科目に関わる部分です。
名簿ファイルに選択科目が付属している場合のルーチンを付けました。
REM ***** BASIC *****
Option Explicit
Dim oSheet As Object
Dim oDocument As Object
Dim sName As String
Dim oFileAccess As Object
Dim oInputStream As Object
Dim sLineData As String
Dim vLineData As Long
Dim vNumberOfClass As Integer 'クラス人数
Dim i As Long, i1 As Long, i2 As Long, i3 As Long, i4 As Long
Dim iClass As Integer, iRow As Long
Dim sDriveName As String
Dim sGakuji_Address As String
Dim vYear As Integer '2032
Dim sYear As String '"32"
Dim vGrade As Integer '1-6
Dim vSelGrpNum As Integer '選択群数
Dim iSelGrpNum As Integer
Dim vU_ClassNum As Integer 'クラス数
Dim sCSV_FName As String
Dim sFileAccess As String
Dim sStrStr As String
Dim sStrStr1 As String
Dim sStrStr2 As String
Dim sFilePath As String
Dim nFileHandle As Integer

'Sheet to CSV Groupe
Sub Main
oDocument = ThisComponent
oSheet = oDocument.getSheets.getByName("Sheet1")
vSelGrpNum = oSheet.getCellByPosition(2, 3).Value 'C4
sDriveName = oSheet.getCellByPosition(2, 4).String 'C5
sGakuji_Address = oSheet.getCellByPosition(2, 5).String 'C6
vYear = oSheet.getCellByPosition(2, 6).Value 'C7
vGrade = oSheet.getCellByPosition(2, 7).Value 'C8
vU_ClassNum = oSheet.getCellByPosition(2, 8).Value 'C9
sYear = Right(CStr(vYear), 2)
sFileAccess = "file:///" & sDriveName & ":/"
sFileAccess = sFileAccess & sGakuji_Address & sYear & CStr(vGrade) & "NAM/"

iRow = 9 'Sheet2の第11行から名前を読み込む
For iClass = 1 to vU_ClassNum
sCSV_FName = sYear & CStr(vGrade) & _
Right( CStr(iClass + 100),2) & "NAM.CSV"
sFilePath = sFileAccess & sCSV_FName
nFileHandle = FreeFile
Open sFilePath For Output As nFileHandle
Print #nFileHandle, sCSV_FName
oSheet = oDocument.getSheets.getByName("Sheet1")
vNumberOfClass = oSheet.getCellByPosition(2, iClass + 9).Value
Print #nFileHandle, vNumberOfClass
Print #nFileHandle, vSelGrpNum
oSheet = oDocument.getSheets.getByName("Sheet2")
sStrStr = ""
If vSelGrpNum > 0 Then
i4 = oSheet.getCellByPosition(2, 7).Value '選択群コード
sStrStr = CStr(i4)
sStrStr1 = oSheet.getCellByPosition(2, 8).String '選択群名
sStrStr2 = oSheet.getCellByPosition(2, 9).String '選択曜日時限
If vSelGrpNum > 1 Then
For iSelGrpNum = 2 To vSelGrpNum
i4 = oSheet.getCellByPosition(iSelGrpNum+1, 7).Value '選択群コード
sStrStr = sStrStr & "," & CStr(i4)
sStrStr1 = sStrStr1 & "," & oSheet.getCellByPosition(iSelGrpNum+1, 8).String '選択群名
sStrStr2 = sStrStr2 & "," & oSheet.getCellByPosition(iSelGrpNum+1, 9).String '選択曜日時限
Next iSelGrpNum
End If
Print #nFileHandle, sStrStr
Print #nFileHandle, sStrStr1
Print #nFileHandle, sStrStr2
Else
Print #nFileHandle, sStrStr
Print #nFileHandle, sStrStr
Print #nFileHandle, sStrStr
End If

For i = 1 to vNumberOfClass
iRow = iRow + 1
i4 = oSheet.getCellByPosition(2, iRow).Value
sStrStr1 = oSheet.getCellByPosition(3, iRow).String '氏名
sStrStr2 = oSheet.getCellByPosition(4, iRow).String 'フリガナ
sStrStr = CStr(i4) & "," & sStrStr1 & "," & sStrStr2
If vSelGrpNum > 0 Then
For iSelGrpNum = 1 To vSelGrpNum
sStrStr1 = oSheet.getCellByPosition(iSelGrpNum*3+2, iRow).String '選択科目名
sStrStr2 = oSheet.getCellByPosition(iSelGrpNum*3+3, iRow).String '選択科目名2
i4 = oSheet.getCellByPosition(iSelGrpNum*3+4, iRow).Value '選択科目コード
sStrStr = sStrStr & "," & sStrStr1 & "," & sStrStr2 & "," &_
CStr(i4)
Next iSelGrpNum
End If
Print #nFileHandle, sStrStr
Next i
Close #nFileHandle

Next iClass

End Sub
公開日記トップへ
oooug-jp SNS

●サイドメニュー

  • 公開日記
  • >公開日記はログインしなくても読めます

http://www.ospn.jp/

inetd