代码如下:
Option Explicit
Dim xlApp As Excel.Application
Dim xlBook As Workbook
Dim fso As New FileSystemObject
Dim gExcel1995Folder As String
Dim gExcel2003Folder As String
Dim gCode2003File As String
Dim initialFlag As Boolean
Private Sub cmdCancel_Click()
End
End Sub
Private Sub cmdChange_Click()
Dim excel95FileName As String
Dim excel03FileName As String
Dim iniFileName As String
Dim strCode As String
Dim mfiles As Files
Dim mFile As File
Call LoadInformation("ファイルの切り替えが開始します。", Now())
iniFileName = App.Path & "\setting.ini"
Set xlApp = New Excel.Application
xlApp.Visible = False
xlApp.DisplayAlerts = False
If getIniSetting(iniFileName) = True Then
strCode = getCode(gCode2003File)
Set mfiles = fso.GetFolder(gExcel1995Folder).Files
For Each mFile In mfiles
If changeExcel(gExcel1995Folder + "\" + mFile.Name, strCode, gExcel2003Folder + "\" + mFile.Name) = True Then
Call LoadInformation(mFile.Name + "ファイルの切り替えがが成功します。", Now())
Else
Call LoadInformation(mFile.Name + "ファイルの切り替えが失敗します。", Now())
End If
Next
Call LoadInformation("ファイルの切り替えがしました。", Now())
Else
Call LoadInformation("初期化情報を取得に失敗しました。", Now())
End If
xlApp.Quit
Set xlApp = Nothing
End Sub
Private Sub LoadInformation(strMess As String, mdate As String)
lstInformation.AddItem ("[" & mdate & "]" & strMess)
End Sub
'初期化ファイルを読む
Private Function getIniSetting(iniFilePath As String) As Boolean
Dim intFileNum As Integer 'File NUm
Dim strTemp As String
Dim equitPosition As Integer 'position of "="
Dim strKey As String 'Key
Dim strData As String 'Value
If fso.FileExists(iniFilePath) = False Then Exit Function
intFileNum = FreeFile
Open iniFilePath For Input As #intFileNum 'ファイルを開ける
Do While Not EOF(intFileNum) '全部読む
Line Input #intFileNum, strTemp 'データを取得
equitPosition = InStr(1, strTemp, "=") '=の位置取得
If equitPosition <> 0 Then '=がある場合
strKey = Mid(strTemp, 1, equitPosition - 1) 'キーの所得
strData = Mid(strTemp, equitPosition + 1) 'データ
Select Case strKey '1995フォルダ
Case "EXCEL1995"
gExcel1995Folder = strData
Case "EXCEL2003" '2003フォルダ
gExcel2003Folder = strData
Case "CODEFILE" 'コード読む
gCode2003File = strData
End Select
End If
Loop
Close #intFileNum
If fso.FolderExists(gExcel1995Folder) = True And _
fso.FolderExists(gExcel2003Folder) = True And _
fso.FileExists(gCode2003File) = True Then
getIniSetting = True
End If
End Function
'コードファイルを読む
Private Function getCode(codeFilePath As String) As String
Dim intFileNum As Integer 'File NUm
Dim strCode As String 'Code string
Dim strTemp As String
intFileNum = FreeFile
Open codeFilePath For Input As #intFileNum
Do While Not EOF(intFileNum)
Line Input #intFileNum, strTemp
strCode = strCode & strTemp & vbCrLf
Loop
Close #intFileNum
getCode = strCode
End Function
'ファイルの変更
Private Function changeExcel(excelFileName As String, strCode As String, newExcelFileName) As Boolean
On Error GoTo ExitPort
Dim iCount As Integer
Set xlBook = xlApp.Workbooks.Open(excelFileName)
If xlBook.Sheets(1).Name = "オープン時設定" And xlBook.Sheets(2).Name = "器具データ" And xlBook.Sheets(3).Name = "接続データ" And _
xlBook.Sheets(4).Name = "布線データ" And xlBook.Sheets(5).Name = "配線図" Then
With xlBook.VBProject
With .VBComponents(1).CodeModule
iCount = .CountOfLines
Call .DeleteLines(1, iCount)
.AddFromString strCode
End With
End With
xlBook.SaveAs newExcelFileName
End If
xlBook.Close (False)
changeExcel = True
Exit Function
ExitPort:
changeExcel = False
End Function