Inputbox to return range from another workbook

[madhavansaju]

hi,

i want the user to open an excel workbook of his choice and then
choose a range from that excel workbook.

this, of course, has to happen during the macro run.
and the macro needs to capture the name of the new workbook, the sheet
name and range chosen.

when using inputbox, it works when i choose a range within the same
workbook as is the macro.
but when i try it with another workbook - nothing is returned.

any ideas?

I am using the code that I found on one of the pages on this very
forum...

Sub tester1()


On Error Resume Next

Set rng = Nothing
Set rng = Application.InputBox("Select a cell with the mouse",
Type:=8)
MsgBox rng.Address
rng.Parent.Activate
If Not rng Is Nothing Then
rng(1).Select
Else
MsgBox "You didn't select"
End If


End Sub


Thanks

 

[Dan R.]

Try this:

Sub test()
Dim fName As String, wb As Workbook
Dim rng As Range

fName = Application.GetOpenFilename()
If fName <> "False" Then
Set wb = Workbooks.Open(fName)
Else
Exit Sub
End If
Set rng = Application.InputBox("Select a cell", Type:=8)

MsgBox "Range: " & rng.Address & vbLf & _
"Filename: " & wb.Name & vbLf & _
"Sheet: " & ActiveSheet.Name
wb.Close SaveChanges:=False
End Sub

 

[Dan R.]

Forgot a couple things... try this one:

Sub test()
Dim fName As String, wb As Workbook
Dim rng As Range
fName = Application.GetOpenFilename()
If fName <> "False" Then
Set wb = Workbooks.Open(fName)
Else
Exit Sub
End If
Set rng = Application.InputBox("Select a cell", Type:=8)
If Not rng Is Nothing Then
rng.Parent.Activate
MsgBox "Range: " & rng.Address & vbLf & _
"Filename: " & wb.Name & vbLf & _
"Sheet: " & ActiveSheet.Name
Else
MsgBox "Nothing selected!"
End If
wb.Close SaveChanges:=False
End Sub

 

[madhavansaju]

Hi Dan,

many thx.
Your code works great.

There was just one complication.
For a very specific Excel file - it gives me the error "Run-time error
'13' Type mismatch"

It halts at "Set wb = Workbooks.Open(fName)"

The code works with all other excel files.

Could there be any reasons that you could think of - as to why this
specific excel file gives such an error?

Thanks anyways
Saju

 

[Dan R.]

Saju,

Just add 'On Error Resume Next' to the top of your code under the dim
statements and that should clear it up.