投稿日 | : 2003/07/29(Tue) 17:39 |
投稿者 | : 瀬名 |
Eメール | : |
URL | : |
タイトル | : Re^2: EXCELのシートのコピー |
こんにちは
花ちゃんさんの仰られた事は一通りやってみたのですが
今度は、エクセルが正常終了せずメモリに残ってしまいます。
この件もいろいろ調べてみたのですがお手上げです・・・(>_<)
以下にコードを記述しておきますのでよろしかったらご教授ください。
Private Function fncOutputExcelFile() As Boolean
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim sFileName As String
On Error GoTo fncOutputExcelFile_Error:
fncOutputExcelFile = False
If fncnExcel_Start(xlApp) = False Then
Exit Function
MsgBox "【Excel】の起動に失敗しました。", vbInformation, Me.Caption
End If
sFileName = "C:\TEST.XLS"
Set xlBook = xlApp.Workbooks.Open(sFileName)
FOR i = 1 to 10
Set xlSheet = xlBook.Sheets("原紙")
xlSheet.Select
xlSheet.Copy After:=Sheets(2)
Set xlSheet = Nothing
Set xlSheet = xlBook.Sheets("原紙 (2)")
xlSheet.Select
xlSheet.Name = i
With xlApp.ActiveSheet
.Cells(1, 1) = "TESTTESTTESTTESTTESTTESTTESTTESTTEST"
End With
Set xlSheet = Nothing
NEXT i
xlBook.Save
'∽∽∽∽∽ <<【Excel】の編集を終了します>> ∽∽∽∽∽
Call fncbExcel_End(xlApp, xlBook)
fncOutputExcelFile = True
fncOutputExcelFile_Exit:
Exit Function
fncOutputExcelFile_Error:
MsgBox Err.Number & " " + Err.Description, _
vbExclamation, _
Me.Caption
Call fncbExcel_End(xlApp, xlBook)
End Function
Private Function fncnExcel_Start(objExcelObject As Object) As Boolean
fncnExcel_Start = False
Set objExcelObject = Nothing
Set objExcelObject = New Excel.Application
objExcelObject.Application.WindowState = xlMinimized
objExcelObject.Application.Visible = False
fncnExcel_Start = True
End Function
Private Function fncbExcel_End(objExcelObject As Object, objExcelBook As Object) As Boolean
Dim bAlerts As Boolean
fncbExcel_End = False
bAlerts = objExcelObject.DisplayAlerts
objExcelObject.DisplayAlerts = False
objExcelBook.Close SaveChanges:=True
objExcelObject.DisplayAlerts = bAlerts
objExcelObject.Application.Quit
Set objExcelBook = Nothing
Set objExcelObject = Nothing
fncbExcel_End = True
End Function