Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can VBA Code change itself?
I have a spreadsheet I want to distribute to various people. When it runs,
it pulls data from an outside spreadsheet into itself and processes it. Each person that I distribute it to will have the data to be pulled in a folder on their machine. And the spreadsheet I give them will probably be in the same folder. Can I set a variable in the code with an initial null value, then put a textbox on the menu form, have them enter the folder name, then permanently change the variable value to what they enter, so that the next time they open the spreadsheet, the folder name they entered last time is already there. -- Bill @ UAMS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can VBA Code change itself?
I would create a new text file in the users network document and settings
Directory. Give it your own extension and create startup excel information. When the macro is run have it open the text file and get needed info. "BillCPA" wrote: I have a spreadsheet I want to distribute to various people. When it runs, it pulls data from an outside spreadsheet into itself and processes it. Each person that I distribute it to will have the data to be pulled in a folder on their machine. And the spreadsheet I give them will probably be in the same folder. Can I set a variable in the code with an initial null value, then put a textbox on the menu form, have them enter the folder name, then permanently change the variable value to what they enter, so that the next time they open the spreadsheet, the folder name they entered last time is already there. -- Bill @ UAMS |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can VBA Code change itself?
Have a look at GetSetting and SaveSetting in VBA. If you are positive
that the folder containing the files will also be the same folder where the workbook containing the sode will be stored, you could just grab the directory of the code workbook and use that. BillCPA wrote: I have a spreadsheet I want to distribute to various people. When it runs, it pulls data from an outside spreadsheet into itself and processes it. Each person that I distribute it to will have the data to be pulled in a folder on their machine. And the spreadsheet I give them will probably be in the same folder. Can I set a variable in the code with an initial null value, then put a textbox on the menu form, have them enter the folder name, then permanently change the variable value to what they enter, so that the next time they open the spreadsheet, the folder name they entered last time is already there. -- Bill @ UAMS |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can VBA Code change itself?
Bill,
In your spreadsheet, use code like this Dim myPath As String myPath = ThisWorkbook.Path ChDir myPath It will change your default folder for searches, etc. to the folder that the ss file is stored in. HTH, Bernie MS Excel MVP "BillCPA" <Bill @ UAMS wrote in message ... I have a spreadsheet I want to distribute to various people. When it runs, it pulls data from an outside spreadsheet into itself and processes it. Each person that I distribute it to will have the data to be pulled in a folder on their machine. And the spreadsheet I give them will probably be in the same folder. Can I set a variable in the code with an initial null value, then put a textbox on the menu form, have them enter the folder name, then permanently change the variable value to what they enter, so that the next time they open the spreadsheet, the folder name they entered last time is already there. -- Bill @ UAMS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change code with code? | Excel Programming | |||
Change code by code. Can you do that? | Excel Programming | |||
Can I use code/macro to change code/macro in an existing file? | Excel Programming | |||
Code Conflicts With Worksheet Change Code | Excel Programming | |||
Change to Code | Excel Programming |