Using Dialog to select a Workbook, Worksheet and Range
 

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

Using Dialog to select a Workbook, Worksheet and Range
 

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



--
HTH

Bob Phillips

"Tony Starr" wrote in message
...
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

Using Dialog to select a Workbook, Worksheet and Range
 

Thanks Bob

I will give it a try.



"Bob Phillips" wrote in message
...
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



--
HTH

Bob Phillips

"Tony Starr" wrote in message
...
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