Referencing Spreadsheet in VBA
 

I want to create a macro that imports data from a spreadsheet delivered
occasionally.

How can I set up the macro in such a way that the user can input the
name of the spreadsheet from which the data is to be copied over?


--
Timmy Mac1
------------------------------------------------------------------------
Timmy Mac1's Profile: http://www.excelforum.com/member.php...o&userid=15188
View this thread: http://www.excelforum.com/showthread...hreadid=535579

Referencing Spreadsheet in VBA
 

See application.GetOpenfileName()

it puts up the file open dialog and allows the user to select a file. It
returns the users selection as a string and then your code can open that file
with the workbook.open method.

--
Regards,
Tom Ogilvy


"Timmy Mac1" wrote:


I want to create a macro that imports data from a spreadsheet delivered
occasionally.

How can I set up the macro in such a way that the user can input the
name of the spreadsheet from which the data is to be copied over?


--
Timmy Mac1
------------------------------------------------------------------------
Timmy Mac1's Profile: http://www.excelforum.com/member.php...o&userid=15188
View this thread: http://www.excelforum.com/showthread...hreadid=535579

Referencing Spreadsheet in VBA
 

Thanks Tom.:)

Can I do it in such a way that the macro will keep referring to that
file, rather than have it come up each time?

What I want to do is get the User to select the file which will contain
various ranges I want to copy over to the "working" file?


--
Timmy Mac1
------------------------------------------------------------------------
Timmy Mac1's Profile: http://www.excelforum.com/member.php...o&userid=15188
View this thread: http://www.excelforum.com/showthread...hreadid=535579

Referencing Spreadsheet in VBA
 

What Tom suggested will work for you. You will have to assign what the user
inputs in the GetOpenFilename to a variable. The value in the variable will
stay the same until you change it and/or the macro finishes running.

If you want the variable to retain its value between instances of the macro
I would suggest entering whatever the user enters in GetOpenFilename into a
cell in the workbook. You can then refer to that cell whenever you want.
And the value in the cell can be changed by whatever the user types in the
GetOpenFilename if need be.

"Timmy Mac1" wrote:


Thanks Tom.:)

Can I do it in such a way that the macro will keep referring to that
file, rather than have it come up each time?

What I want to do is get the User to select the file which will contain
various ranges I want to copy over to the "working" file?


--
Timmy Mac1
------------------------------------------------------------------------
Timmy Mac1's Profile: http://www.excelforum.com/member.php...o&userid=15188
View this thread: http://www.excelforum.com/showthread...hreadid=535579

Referencing Spreadsheet in VBA
 

Just to elaborate on Williams good answer:

Dim sName as Variant
Dim sh as worksheet
Dim bk as Workbook
set sh = activesheet
sName = application.GetOpenFileName()
if sName = False then exit sub
set bk = Workbooks.Open(sName)

' now you use bk to refer to the workbook

bk.worksheets(1).Range("A1:A10").copy _
destination:=sh.Range("B9")
bk.worksheets(1).Range("F3:G10").copy _
destination:=sh.Range("M5")

'and so forth.
bk.close Savechange:=False

--
Regards,
Tom Ogilvy

"William Horton" wrote:

What Tom suggested will work for you. You will have to assign what the user
inputs in the GetOpenFilename to a variable. The value in the variable will
stay the same until you change it and/or the macro finishes running.

If you want the variable to retain its value between instances of the macro
I would suggest entering whatever the user enters in GetOpenFilename into a
cell in the workbook. You can then refer to that cell whenever you want.
And the value in the cell can be changed by whatever the user types in the
GetOpenFilename if need be.

"Timmy Mac1" wrote:


Thanks Tom.:)

Can I do it in such a way that the macro will keep referring to that
file, rather than have it come up each time?

What I want to do is get the User to select the file which will contain
various ranges I want to copy over to the "working" file?


--
Timmy Mac1
------------------------------------------------------------------------
Timmy Mac1's Profile: http://www.excelforum.com/member.php...o&userid=15188
View this thread: http://www.excelforum.com/showthread...hreadid=535579

Referencing Spreadsheet in VBA
 

Tom / William

Many many thanks for your assistance. You'll have guessed I am a
complete amateur and what you have showed me has given me great
motivation to learn and use VB more.

If I may be so bold I have one further question.

Instead of having the user open a file from windows explorer, how can I
do it so that they are prompted to pick from a list of files already
opened?

many thanks

timmy mac


--
Timmy Mac1
------------------------------------------------------------------------
Timmy Mac1's Profile: http://www.excelforum.com/member.php...o&userid=15188
View this thread: http://www.excelforum.com/showthread...hreadid=535579

Referencing Spreadsheet in VBA
 

The is no built in capability to offer a list to select from. You could do
this by creating a userform. If you want to pursue that, start a new thread
for best response.

Otherwise, you can use the application.Input box function to have user
select a cell (any cell) on the sheet in the destination workbook where they
want to put the data.

Dim MyOpenBook as Workbook
Dim MyRange as Range
Dim MySheet as Worksheet
Dim sh as worksheet

set sh = activesheet

On error resume Next
set MyRange = Application.InputBox("Select a cell in the " & _
"destination sheet with the mouse", Type:=8)
On error goto 0
if MyRange is Nothing then
MsgBox "No range selected. Quitting. . ."
exit sub
end if
set MyBook = rng.parent.parent
set MySheet = rng.Parent
MyBook.worksheets(1).Range("A1:A10").copy _
destination:=sh.Range("B9")
MyBook.worksheets(1).Range("F3:G10").copy _
destination:=sh.Range("M5")


or
MySheet.Range("A1:A10").copy _
Destination:=sh.Range("B9")
MySheet.Range("F3:G10").copy _
Destination:=sh.Range("M5")


--
Regards,
Tom Ogilvy


"Timmy Mac1" wrote
in message ...

Tom / William

Many many thanks for your assistance. You'll have guessed I am a
complete amateur and what you have showed me has given me great
motivation to learn and use VB more.

If I may be so bold I have one further question.

Instead of having the user open a file from windows explorer, how can I
do it so that they are prompted to pick from a list of files already
opened?

many thanks

timmy mac


--
Timmy Mac1
------------------------------------------------------------------------
Timmy Mac1's Profile:
http://www.excelforum.com/member.php...o&userid=15188
View this thread: http://www.excelforum.com/showthread...hreadid=535579