Find out workkbook name from Range object base on user defined

[mtranhntb]

Hi,

I try to write a rountine for copying/moving data between different workbooks by asking the user to point to the source and destination for the data. I have tried to use the built-in inputbox to obtain this information but could not figure out how to retrieve the workbook name based on the range object. Eventhoug the inputbox does show the information about the workbook name when user point to a cell in another workbook, how to retrieve it is a big problem for me.

I did get some successful when only two workbooks were open by saving the activebook name info and then iterate thru all the open workbook to find out what is the other open workbook name. With this routine users have to close other workbooks that are not needed in the operation before running the routine. This would somewhat becomes inconvenient when asking user to close other workbook which might important to them at the time for other work.

Is there anyway to retrieve the workbook name based on range object which was obtained from the inputbox function? If not, is there any other built-in function that we can use to obtain the workbook name by asking the user to switching between different windows (open Excell files?

 

[SkipVought]

Hi,

Where is your code and what statement is your problem?

Please post your code

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[mtranhntb]

Actually the routine is about comparing data between 2 workbooks. Could not figure out how to attach the text file so I went ahead to copy it into this area. Sorry for any inconvenience.

Sub Compare_2WB_Data()
'Highlighting the difference between datasets in TWO workbooks.
'Before running the operation, make sure ONLY 2 WORKBOOKS whick will be used for comparison are OPEN!
'The rest of other workbooks need to be CLOSED.

'This routine will compare the value of two datasets in SAME workbook
'However, the datasets can be resigned in same worksheet or different worksheets.
' The cells in the 2nd dataset will be highlighted in red background
''if their value is not the same as 1st dataset.

Dim userSelect As String
Dim time1 As Variant, time2 As Variant
Dim Wb1Name As String, Wb2Name As String
Dim Wb1WsName As String, Wb2WsName As String

Dim i As Long, j As Long
Dim rowMin1 As Long, columnMin1 As Long
Dim rowMin2 As Long, columnMin2 As Long
Dim rowMax1 As Long, columnMax1 As Long
Dim rowMax2 As Long, columnMax2 As Long
Dim DataBoundary1 As String, DataBoundary2 As String
Dim topLeftBoundary1 As String, bottomRightBoundary1 As String
Dim topLeftBoundary2 As String, bottomRightBoundary2 As String
Dim address1 As String, address2 As String
Dim dataSet1 As Range, dataSet2 As Range
userSelect = MsgBox("Only 2 workbooks containing the datasets for comparison are open." _
& vbCrLf & "Others should be CLOSED before CONTINUING this operation." _
& vbCrLf & "Click CANCEL if not READY", vbOKCancel, "Comparing 2 datasets from 2 workbooks")

If userSelect = "1" Then

time1 = Time()


'record the source workbook info
Wb1Name = ActiveWorkbook.Name

'get input from user for 1st SET of data
Set dataSet1 = Application.InputBox(prompt:="Point to the Cell in the FIRST DATASET", Title:="Getting Info for DataSet1", Type:=8)
Wb1WsName = dataSet1.Worksheet.Name
address1 = dataSet1.Address

'get input from user for 2nd SET of data
Set dataSet2 = Application.InputBox(prompt:="Point to the Cell in SECOND DATASET", Title:="Getting Info for DataSet2", Type:=8)
Wb2WsName = dataSet2.Worksheet.Name
address2 = dataSet2.Address




'identify the datasources
For i = 2 To 3
If Workbooks(i).Name <> Wb1Name Then
Wb2Name = Workbooks(i).Name
End If
Next


'record the source workbook info
Workbooks(Wb1Name).Activate
Worksheets(Wb1WsName).Activate
DataBoundary1 = ActiveCell.CurrentRegion.Address
topLeftBoundary1 = Left(DataBoundary1, InStr(1, DataBoundary1, ":") - 1)
bottomRightBoundary1 = Right(DataBoundary1, Len(DataBoundary1) - InStr(1, DataBoundary1, ":"))


'setting the limits of dataset
rowMin1 = Range(topLeftBoundary1).row
columnMin1 = Range(topLeftBoundary1).column
rowMax1 = Range(bottomRightBoundary1).row
columnMax1 = Range(bottomRightBoundary1).column


Workbooks(Wb2Name).Activate
Worksheets(Wb2WsName).Activate
dataSet2.Activate
DataBoundary2 = ActiveCell.CurrentRegion.Address
topLeftBoundary2 = Left(DataBoundary2, InStr(1, DataBoundary2, ":") - 1)
bottomRightBoundary2 = Right(DataBoundary2, Len(DataBoundary2) - InStr(1, DataBoundary2, ":"))

'setting the limits of dataset
rowMin2 = Range(topLeftBoundary2).row
columnMin2 = Range(topLeftBoundary2).column

Dim RowOffset1_2 As Long
Dim ColumnOffset1_2 As Long

RowOffset1_2 = rowMin2 - rowMin1
ColumnOffset1_2 = columnMin2 - columnMin1

For i = rowMin1 To rowMax1

For j = columnMin1 To columnMax1

If Workbooks(Wb1Name).Sheets(Wb1WsName).Cells(i, j).Value <> Workbooks(Wb2Name).Sheets(Wb2WsName).Cells(i + RowOffset1_2, j + ColumnOffset1_2).Value Then
Workbooks(Wb2Name).Sheets(Wb2WsName).Cells(i + RowOffset1_2, j + ColumnOffset1_2).Interior.Color = vbRed
End If

Next j

Next i

time2 = Time()
Dim totalTime As Variant
totalTime = time2 - time1
Dim displayTime As String

displayTime = Minute(totalTime) & ":" & Second(totalTime)
MsgBox "Done in " & displayTime

Else
'exit the macro
Exit Sub
End If

End Sub