I'm looking for a way to extract a value from an Excel form to complet
path for SaveAs operation. This what I currently have:

Need to save form as :
sPath = "\\Cpgbyfs03\comrfq\GBY\20\MyForm.xls" (where 20 is a folde
for a specific district)

The thing I'm running into is by specifying the district as part o
sPath, I need to change 17 of these forms each time for 6 macros whe
master form is split into districts (17 districts and 6 approva
locations). Obviously, this would not be very productive.

My first thought was to provide user with input box to enter district
but all users are not computer savy and concern over typos exists.
Cell G10 on the form currently contains a value for that district'
number. What I'm looking to do is insert the value from cell G10 o
active form and make it part of the sPath code so each form will b
routed to correct location on a terminal server.

Any suggestions would be greatly appreciated.
tschult

--
Message posted from http://www.ExcelForum.com

If you have the district number in A1, then something like this should work:

sPath="\\Cpgbyfs03\comrfq\GBY\"&Range("A1").Value& "\MyForm.xls"

Or maybe you could use an inputbox to allow the user to type in the district
code:

DistCode=InputBox("Please enter the district code.")
sPath = "\\Cpgbyfs03\comrfq\GBY\"&DistCode&"\MyForm.xl s"
HTH

--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winne the Pooh
"tschultz " wrote in message
...
I'm looking for a way to extract a value from an Excel form to complete
path for SaveAs operation. This what I currently have:

Need to save form as :
sPath = "\\Cpgbyfs03\comrfq\GBY\20\MyForm.xls" (where 20 is a folder
for a specific district)

The thing I'm running into is by specifying the district as part of
sPath, I need to change 17 of these forms each time for 6 macros when
master form is split into districts (17 districts and 6 approval
locations). Obviously, this would not be very productive.

My first thought was to provide user with input box to enter district,
but all users are not computer savy and concern over typos exists.
Cell G10 on the form currently contains a value for that district's
number. What I'm looking to do is insert the value from cell G10 of
active form and make it part of the sPath code so each form will be
routed to correct location on a terminal server.

Any suggestions would be greatly appreciated.
tschultz


---
Message posted from http://www.ExcelForum.com/

Something like

sPath = "\\" & Activesheet.Range("G10") & "20\MyForm Cp" & _
Activesheet.Range("G10") & "fs03\comfrq.xls"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"tschultz " wrote in message
...
I'm looking for a way to extract a value from an Excel form to complete
path for SaveAs operation. This what I currently have:

Need to save form as :
sPath = "\\\GBY\20\MyForm.Cpgbyfs03\comrfqxls" (where 20 is a folder
for a specific district)

The thing I'm running into is by specifying the district as part of
sPath, I need to change 17 of these forms each time for 6 macros when
master form is split into districts (17 districts and 6 approval
locations). Obviously, this would not be very productive.

My first thought was to provide user with input box to enter district,
but all users are not computer savy and concern over typos exists.
Cell G10 on the form currently contains a value for that district's
number. What I'm looking to do is insert the value from cell G10 of
active form and make it part of the sPath code so each form will be
routed to correct location on a terminal server.

Any suggestions would be greatly appreciated.
tschultz


---
Message posted from http://www.ExcelForum.com/

Hi,

district=Range("G10").value
sPath="\\Cpgbyfs03\comrfq\GBY\" & district & "\MyForm.xls"


Paul



"tschultz " wrote in message
...
I'm looking for a way to extract a value from an Excel form to complete
path for SaveAs operation. This what I currently have:

Need to save form as :
sPath = "\\Cpgbyfs03\comrfq\GBY\20\MyForm.xls" (where 20 is a folder
for a specific district)

The thing I'm running into is by specifying the district as part of
sPath, I need to change 17 of these forms each time for 6 macros when
master form is split into districts (17 districts and 6 approval
locations). Obviously, this would not be very productive.

My first thought was to provide user with input box to enter district,
but all users are not computer savy and concern over typos exists.
Cell G10 on the form currently contains a value for that district's
number. What I'm looking to do is insert the value from cell G10 of
active form and make it part of the sPath code so each form will be
routed to correct location on a terminal server.

Any suggestions would be greatly appreciated.
tschultz


---
Message posted from http://www.ExcelForum.com/