ワークシート上ではたとえば「='C:\ABC\[DEF.xls]Sheet1'!$A$1」などと指定すれば 開いていないファイルのセルを参照できますが、 これと同じような方法で、たとえば Dim hoge As Worksheet Set hoge = ******** のような記述で、開いていないファイルのワークシートを参照するには どうすればいいのでしょうか？
よろしくお願いします。
821 名前：デフォルトの名無しさん [2007/09/01(土) 12:32:56 ]
試せばいいじゃん
822 名前：820 [2007/09/01(土) 13:09:10 ]
>>821 試す、って何をどう試すのですか？？ まさか set hoge = worksheets("C:\ABC\[DEF.xls]Sheet1") とかをやれってことですか？ エラーに決まってますが。
Public Sub MyTest() Dim wbkTarget As Workbook Set wbkTarget = OpenBook MsgBox "シート1の名前は" & wbkTarget.Worksheets(1).Name & "です", vbOKOnly, "Result" wbkTarget.Close False '※ここでブックの実体を閉じないとゴーストプロセスが残ってしまう Set wbkTarget = Nothing End Sub
Private Function OpenBook(Optional ByVal iSheetIndex As Integer = 1) As Workbook Dim xlApp As Excel.Application Dim wbkTarget As Workbook Dim wksSheet As Worksheet Dim strPath As String Set xlApp = New Excel.Application strPath = xlApp.GetOpenFilename("Microsoft Excelブック,*.xls")
Set wbkTarget = xlApp.Workbooks.Open(strPath) Set wksSheet = wbkTarget.Worksheets(1) Set OpenBook = wbkTarget Set wbkTarget = Nothing '※インスタンスは解放されるが実体のブックは開きっぱなし Set xlApp = Nothing End Function
830 名前：829 mailto:sage [2007/09/01(土) 21:10:43 ]
俺は一体何を示したかったんだ・・・ どうも疲れてるようだ
Private Function OpenBook(Optional ByVal iSheetIndex As Integer = 1) As Workbook Dim xlApp As Excel.Application Dim wbkTarget As Workbook Dim wksSheet As Worksheet Dim strPath As String Set xlApp = New Excel.Application strPath = xlApp.GetOpenFilename("Microsoft Excelブック,*.xls")
Set wbkTarget = xlApp.Workbooks.Open(strPath) Set wksSheet = wbkTarget.Worksheets(iSheetIndex) wksSheet.Name="ほげ" Set OpenBook = wbkTarget set wksSheet = Nothing Set wbkTarget = Nothing '※インスタンスは解放されるが実体のブックは開きっぱなし Set xlApp = Nothing End Function
'This routines must be defined by the programmer ' Sub FuncVecJac(ByRef X() As Double, _ ' ByRef FVec() As Double, _ ' ByRef FJac() As Double, _ ' ByRef IFlag As Long)
'Routines '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 'The subroutine minimizes the sum of squares of M nonlinear finctions of 'N arguments with Levenberg-Marquardt algorithm using Jacobian and 'information about function values. ' 'Programmer should redefine FuncVecJac subroutine which takes array X '(argument) whose index ranges from 1 to N as an input and if variable 'IFlag is equal to: ' * 1, returns vector of function values in array FVec (in elements from ' 1 to M), not changing FJac. ' * 2, returns Jacobian in array FJac (in elements [1..M,1..N]), not ' changing FVec. 'The subroutine can change the IFlag parameter by setting it into a negative 'number. It will terminate program. ' 'Programmer can also redefine LevenbergMarquardtNewIteration subroutine 'which is called on each new step. Current point X is passed into the 'subroutine. It is reasonable to redefine the subroutine for better 'debugging, for example, to visualize the solution process. ' 'The AdditionalLevenbergMarquardtStoppingCriterion could be redefined to 'modify stopping conditions.
863 名前：デフォルトの名無しさん [2007/09/03(月) 22:44:43 ]
'Input parameters: ' N ・ number of unknowns, N>0. ' M ・ number of summable functions, M>=N. ' X ・ initial solution approximation. ' Array whose index ranges from 1 to N. ' EpsG ・ stopping criterion. Iterations are stopped, if cosine of ' the angle between vector of function values and each of ' the Jacobian columns if less or equal EpsG by absolute ' value. In fact this value defines stopping condition which ' is based on the function gradient smallness. ' EpsF ・ stopping criterion. Iterations are stopped, if relative ' decreasing of sum of function values squares (real and ' predicted on the base of extrapolation) is less or equal ' EpsF. ' EpsX ・ stopping criterion. Iterations are stopped, if relative ' change of solution is less or equal EpsX. ' MaxIts ・ stopping criterion. Iterations are stopped, if their ' number exceeds MaxIts.
864 名前：デフォルトの名無しさん [2007/09/03(月) 22:46:24 ]
Output parameters: ' X ・ solution ' Array whose index ranges from 1 to N. ' Info ・ a reason of a program completion: ' * -1 wrong parameters were specified, ' * 0 interrupted by user, ' * 1 relative decrease of sum of function values ' squares (real and predicted on the base of ' extrapolation) is less or equal EpsF. ' * 2 relative change of solution is less or equal ' EpsX. ' * 3 conditions (1) and (2) are fulfilled. ' * 4 cosine of the angle between vector of function ' values and each of the Jacobian columns is less ' or equal EpsG by absolute value. ' * 5 number of iterations exceeds MaxIts. ' * 6 EpsF is too small. ' It is impossible to get a better result. ' * 7 EpsX is too small. ' It is impossible to get a better result. ' * 8 EpsG is too small. Vector of functions is ' orthogonal to Jacobian columns with near-machine ' precision. 'argonne national laboratory. minpack project. march 1980. 'burton s. garbow, kenneth e. hillstrom, jorge j. more ' 'Contributors: ' * Sergey Bochkanov (ALGLIB project). Translation from FORTRAN to ' pseudocode.
