خرید بک لینک

Vote count: 0

After much struggle with syntax, I have following code working, but I want to use error checking to determine if file is already open using a string.

(Disclosure: I have copied comparesheets from source that I will link when I find it) Trying to replace this code

Set wbkA = Workbooks.Open(FileName:=wba)

with

Set wBook = Workbooks(wba) 'run time error subscript out of range
If wBook Is Nothing Then
   Set wbkA = Workbooks.Open(FileName:=wba)
End If

But I have syntax problem with the string wba. What is proper way use string here?

Sub RunCompare_WS2()

Dim i As Integer
Dim wba, wbb As String
Dim FileName As Variant
Dim wkbA As Workbook
Dim wkbB As Workbook
Dim wBook As Workbook

wba = "C:c.xlsm"
wbb = "C:d.xlsm"

'Set wBook = Workbooks(FileName:=wba) 'compiler error named argument not found

'Set wBook = Workbooks(wba) 'run time error subscript out of range
'If wBook Is Nothing Then
  'Set wbkA = Workbooks.Open(FileName:=wba)
'End If

Set wbkA = Workbooks.Open(FileName:=wba)
Set wbkB = Workbooks.Open(FileName:=wbb)

For i = 1 To Application.Sheets.Count
Call compareSheets(wbkA.Sheets(i), wbkB.Sheets(i))
Next i

wbkA.Close SaveChanges:=True
wbkB.Close SaveChanges:=False
MsgBox "Completed...", vbInformation
End Sub

Sub compareSheets(shtSheet1 As Worksheet, shtSheet2 As Worksheet)

Dim mycell As Range
Dim mydiffs As Integer
Dim DifFound As Boolean

DifFound = False
sDestFile = "C:comp-wb.txt"
DestFileNum = FreeFile()
Open sDestFile For Append As DestFileNum

'For each cell in sheet2 that is not the same in Sheet1, color it lightgreen in first file
For Each mycell In shtSheet1.UsedRange
    If Not mycell.Value = shtSheet2.Cells(mycell.Row, mycell.Column).Value Then
        If DifFound = False Then
          Print #DestFileNum, "Row,Col" & vbTab & vbTab & "A Value" & vbTab & vbTab & "B Value"
          DifFound = True
        End If
        mycell.Interior.Color = 5296274 'LightGreen
        Print #DestFileNum, mycell.Row & "," & mycell.Column, mycell.Value, shtSheet2.Cells(mycell.Row, mycell.Column).Value '& vbInformation
        mydiffs = mydiffs + 1
    End If
Next

Print #DestFileNum, mydiffs & " differences found in " & shtSheet1.Name

Close #DestFileNum
End Sub

asked 52 secs ago

برچسب: نویسنده: استخدام کار تاريخ: دوشنبه 27 ارديبهشت 1395 ساعت: 10:46

صفحه بندی