| |
こんにちは。
ichinose様のご協力により、2つのブックの差異個数のカウントと差異部分に色付けし、ブックの並列表示するVBAを組みました。しかし、私のPCではうまく並列表示できますが、サーバにファイルを置き他の人が実行すると、
Windows.CompareSideBySideWith "book2"
でエラーが発生してしまいます。
何故でしょうか？？
よろしくお願いいたします。
Private Sub CommandButton7_Click()
book1.xlsとbook2.xlsを開く
Workbooks.Open Filename:="C:\Documents and Settings\All Users\デスクトップ\book1.xls"
Workbooks.Open Filename:="C:\Documents and Settings\All Users\デスクトップ\book2.xls"
'並べて比較
Windows.CompareSideBySideWith "book2"
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim radd As String
Dim crng As Range
Dim diffcnt As Long
Set sht1 = Workbooks("book1").ActiveSheet
Set sht2 = Workbooks("book2").ActiveSheet
' ↑比較する開いている二つのブック名を指定する
radd = 検査セル範囲の取得(sht1, sht2)
diffcnt = 0
For Each crng In sht1.Range(radd)
With crng
If .Value <> sht2.Range(.Address).Value Then
.Interior.ColorIndex = 3
sht2.Range(.Address).Interior.ColorIndex = 4
diffcnt = diffcnt + 1
End If
End With
Next
MsgBox "相違セル個数＝ " & diffcnt & " , vbOKOnly
Set sht1 = Nothing
Set sht2 = Nothing
End Sub
'=========================================================================
Function 検査セル範囲の取得(sht1 As Worksheet, sht2 As Worksheet) As String
Dim r1 As Range
Dim r2 As Range
Dim mcol As Long
Dim mrw As Long
Set r1 = sht1.Range("a1").CurrentRegion
Set r2 = sht2.Range("a1").CurrentRegion
mcol = r1.Columns.Count
If mcol < r2.Columns.Count Then mcol = r2.Columns.Count
mrw = r1.Rows.Count
If mrw < r2.Rows.Count Then mrw = r2.Rows.Count
With sht1
検査セル範囲の取得 = .Range(.Cells(1, 1), .Cells(mrw, mcol)).Address
End With
Set r1 = Nothing
Set r2 = Nothing
End Function
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
