Updating VB for info entered in a sheet
 

Hi There,

Ive got VB that opens a workbook, runs a macro on it and closes the
workbook.
Currently the VB refers to a specific location.

I would like for the user to select from a drop down within the current
book the locaton onto which the VB should run, ie take data entered into
a sheet and transfer it into VB.

An extract of the current VB is

sName = Dir("\\server1\Area1\*.xls")
Do While sName < ""
Set bk = Workbooks.Open("\\server1\Area1\" & sName)

Call Macro 1

So basically I want the user to be able to change Area1 to Area2 etc..

Is this possible..??

Regards

D

*** Sent via Developersdex http://www.developersdex.com ***

Updating VB for info entered in a sheet
 

Set bk = Workbooks.Open("\\server1\" & Range("D2").Value & "\" & sName)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Darin Kramer" wrote in message
...
Hi There,

Ive got VB that opens a workbook, runs a macro on it and closes the
workbook.
Currently the VB refers to a specific location.

I would like for the user to select from a drop down within the current
book the locaton onto which the VB should run, ie take data entered into
a sheet and transfer it into VB.

An extract of the current VB is

sName = Dir("\\server1\Area1\*.xls")
Do While sName < ""
Set bk = Workbooks.Open("\\server1\Area1\" & sName)

Call Macro 1

So basically I want the user to be able to change Area1 to Area2 etc..

Is this possible..??

Regards

D

*** Sent via Developersdex http://www.developersdex.com ***

Updating VB for info entered in a sheet
 

Thanks bob - and how do I define the range to always point to say sheet
master and then d2 ? as several sheets have changed since the first
loop..

Regards

D



*** Sent via Developersdex http://www.developersdex.com ***

Updating VB for info entered in a sheet
 

Set bk = Workbooks.Open("\\server1\" &
Worksheets("Master").Range("D2").Value & "\" & sName)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Darin Kramer" wrote in message
...
Thanks bob - and how do I define the range to always point to say sheet
master and then d2 ? as several sheets have changed since the first
loop..

Regards

D



*** Sent via Developersdex http://www.developersdex.com ***

Updating VB for info entered in a sheet
 

thanks!

*** Sent via Developersdex http://www.developersdex.com ***