Save User defined text into VBA code.... ?

[kabnl]

Can any one tell me if there is a way to place user defined text into vba code - without the user haveing to open the code ?
I have a template in Word that looks at an Access database. The path for where this database is located, is currently hard coded into the template... but with this being used on more locations, the path is different every time. So I wish to automate the updating of the path ??? My problem is not getting the path details, but how to save it back into the code ????
Any suggestions anyone !
Regards
Kab

 

[VulcanJedi]

If I understand correctly you have determined an approach to get the past correct? If so, try:

Set mypath = REAL PATHNAME

than everywhere you need to identify the path mypath should work?. Tranpkp
************************************
- Let me know if this helped/worked!

 

[kabnl]

Hi Tranpkp

Yes, getting the details of the path is simple. What I can't figure out is how I save that information back into my VBA code, so that the next time I use the template, it knows where to find the database ??!!

Any thoughts?

Kab

 

[VulcanJedi]

WEll I think I understand you ? however your task does sound strange? Why would this file and/or database be moving around so volatily?

You'd have to STORE this value somewhere or just open a dialog to prompt the user to identify where it is. I think if you store this XL with your database in the same place you can just use "your database.mdb" and XL auto realizes you are in the same directory. Sorry can't be of more help. Tranpkp
************************************
- Let me know if this helped/worked!

 

[kabnl]

To attempt to make things a little clearer.... (I hope) I have a template in word that all users can use. Depending on which "group/club" the work in, defines which database will store their info. The database location (path) is found in the vba code in the word template - so it knows where to find the relevant database..... (hope you are still with me). What i am attempting to do is write a function to update the path, should it be needed.... It is not that it changes daily/weekly, but it can change...
My problem is how do I get the information stored in my variables into my code ?

Regards
Kab

 

[kylua]

Can you not alter the text on the actual documant in the template, maybe writing in white to hide it? You cannot save runtime changes into the VBA code to the best of my knowledge. I mainly work in xl and would tackle this by writing the new path to a worksheet cell which is obviously saved with the workbook.

 

[cchanslor]

Ooo, yeah! You could write it to a password-protected doc (like "newpath.doc) that your code places on the user's drive if needed. The default would always be in the actual code (template).

I would try something like this:

If target_file not found in default_path then
look for newpath.doc
if newpath.doc not found then
create newpath.doc with correct path info
mypath = correct_path_info
else
open newpath.doc and retrieve path info
mypath = retrieved_path_info
end if 'newpath.doc created

if target_file not found in mypath then
update newpath.doc with correct path
mypath = correct_path_info
end if 'Path info is corrected for next use

end if 'default_path evaluated and secondary measures taken if needed.

open and work with target_file

-----------------------

Hope this is useful for you.

 

[kylua]

Well if writing a separate file will do the job, why not just use a sequential data file:

Mypath = "C:\Datafiles\thepath.kyl"'extension can be anything you want

open mypath for input as #1
read 1, thepath
close 1
'On first use, trap the file not found error and run the
'get and store filepath routine.

linktodb(thepath)'call the sub that pulls the data


If there is no database file, trap the error and:

sub getandstorepath()
do thepath = application.getopenfilename _
("Databases,*.mdb&quot
Mypath = "C:\Datafiles\thepath.kyl"
if dir thepath <> &quot;&quot; then
open mypath for output as #1
write 1, thepath
close 1
Else
msgbox &quot;Try again, and this time
try selecting a relevant file, OK?&quot;
End if

loop until dir thepath <> &quot;&quot;

end sub

I haven't tested this so errors are likely but you get the drift.

I use datafiles, random access and sequential, extensively to transfer and store data. I find them very useful as you can back up all the user input data on a 1&1/2 Meg workbook in 3-4secs in a 25k file. They are just so much faster than using spreadsheets to store and transfer data. (Up to 60 sets of stats with 30 seperate items per day per employee stored by month for 15 employees. All this uploaded and the relevant data extracted for a report in 5 secs.)

 

[kabnl]

Hi kylua/cchanslor
Many thanks for all your suggestions... I will try them all and let you know on monday....
Once again Thanks
Kab