Using Dialog to select a Workbook, Worksheet and Range

[Tony Starr]

I am writing an Excel Macro that will automate the process of consolidating
ranges from a number of workbooks into one workbook.

On a worksheet the user enters the workbook name, sheet name, and range for
each source workbook
C:\Abc.xls Sheet1 a1:z100
C:\Invoices\Invoices.xls May2005 g15:m25
C:\Sales\Sales.xls Current B23:AA45


In the source workbook column the user can type the name of the workbook or
click on the Find button. The find button uses the vba function
GetOpenFilename() to open a dialog box and allow the user to select an Excel
spreadsheet without opening it and puts the selected file name in the
appropriate column.

Any ideas how I can expand this function to allow the user to open the file,
select a worksheet and a range of cells then close the workbook returning
the workbook name, worksheet name and the range address back to my original
workbook.

I hope the above makes sense.

TIA
Tony

 

[Bob Phillips]

Hi Tony,

Try this

Dim fileToOpen
Dim rng As Range

fileToOpen = Application _
.GetOpenFilename("Microsoft Excel Files (*.xls), *.xls")
If fileToOpen <> False Then
Workbooks.Open fileToOpen
Set rng = Application.InputBox("Select a range from any worksheet",
Type:=8)
If Not rng Is Nothing Then
MsgBox "Workbook: " & ActiveWorkbook.FullName & vbNewLine & _
"Worksheet: " & rng.Parent.Name & vbNewLine & _
"Range: " & rng.Address
End If
ActiveWorkbook.Close savechanges:=False
End If

 

[Tony Starr]

Thanks Bob

I will give it a try.

Hi Tony,

Try this

Dim fileToOpen
Dim rng As Range

fileToOpen = Application _
.GetOpenFilename("Microsoft Excel Files (*.xls), *.xls")
If fileToOpen <> False Then
Workbooks.Open fileToOpen
Set rng = Application.InputBox("Select a range from any worksheet",
Type:=8)
If Not rng Is Nothing Then
MsgBox "Workbook: " & ActiveWorkbook.FullName & vbNewLine & _
"Worksheet: " & rng.Parent.Name & vbNewLine & _
"Range: " & rng.Address
End If
ActiveWorkbook.Close savechanges:=False
End If