 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Developer Toolkits / October 2006Windows variables in macros
At the moment, I have macros that run the transfer text command. The action argument File Name is set to c:\temporary_data.txt. I need to change this location from c:\ to the user's application data folder. I understand the windows variable for this is %appdata%. My question is how to put this in the File Name action argument of MSAccess XP. TIA, Kathy You can use the Environ function to retrieve that value: it would be Environ("appdata"). To be really sure, though (since environment variables are trivial to reset or delete), you'd be best off using VBA rather than a macro, and use the SHGetSpecialFolderLocation API, as illustrated in http://www.mvps.org/access/api/api0054.htm at "The Access Web"  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > At the moment, I have macros that run the transfer text command. > The action argument File Name is set to c:\temporary_data.txt. [quoted text clipped - 6 lines] > TIA, > Kathy Thank you. I referenced the article you recommended, but its way over my head. I have thus far accomplished everything through macros, so I don't know how to write VBA, but I am brave, and good at following examples. Can you point me to some examples, or hold my hand a little? :-) I'm assuming at that point in the macro I need to use the run code action to find the value of the user's application folder, then somehow insert that value into the File Name argument of the TransferText command. Thanks again, Kathy > You can use the Environ function to retrieve that value: it would be > Environ("appdata"). [quoted text clipped - 14 lines] >> TIA, >> Kathy Simply create a new module, copy everything between Code Start and Code End into it and save it (don't name the module fGetSpecialFolderLocation: modules cannot be named the same as procedures within them!) You don't need RunCode: you simply put fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA) where you want to know the path (assuming that's the actual path you want: you may have to play with some of the other constants to get the actual one you want). SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Thank you. I referenced the article you recommended, but its way over my > head. [quoted text clipped - 27 lines] >>> TIA, >>> Kathy Thank you. Could I somehow gather that value when the database launches, like with an autoexec macro or something? Then somehow call on that value whenever I need it? Since I'm not clear conceptually on what is happening, sorry if I'm asking dumb questions. > Simply create a new module, copy everything between Code Start and Code > End into it and save it (don't name the module fGetSpecialFolderLocation: [quoted text clipped - 36 lines] >>>> TIA, >>>> Kathy There are a couple of options. You could store it in a Public variable (although that's not a great solution). You could keep a form open at all times (say, your switchboard) and store the value in a text box on that form. You could then refer to that text box when you needed the value. (The text box needn't be visible. For that matter, the form doesn't need to be visible either) However, unless you're using the value an awful lot of times, simply calling the function when you need it shouldn't be an issue. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Thank you. Could I somehow gather that value when the database launches, > like with an autoexec macro or something? Then somehow call on that value [quoted text clipped - 42 lines] >>>>> TIA, >>>>> Kathy Thank you. I *do* use the technique you describe quite a bit. I keep a lot of values in my switchboard in non-visible fields. That is the way I have been able to "work around" a lot of obstacles I come across (those obstacles being my limited knowledge... :-) ) I will be calling this value a lot, so I will store it there. Now, I know how to get a value from a table or another form and put it in that invisible field in the switchboard, but how do I get this SpecialFolderLocation into that field? Again, I'm sorry if I'm dense, but whenever I hear the word MODULE or CODE I break into a cold sweat! -Kathy > There are a couple of options. > [quoted text clipped - 56 lines] >>>>>> TIA, >>>>>> Kathy One way is to set the control's ControlSource property to =fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA) (complete with equal sign) Another is to use VBA in the form's Load event. Something like: Private Sub Form_Load() Me.MyTextBox = fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA) End Sub (where you replace MyTextBox with the name of the actual text box) SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Thank you. I *do* use the technique you describe quite a bit. I keep a > lot of values in my switchboard in non-visible fields. That is the way I [quoted text clipped - 68 lines] >>>>>>> TIA, >>>>>>> Kathy The first way seemed clean and easy. So I did that, I set the control's ControlSource property to =fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA). When I opened the form, I got a #Name? showing in the field. I even tried making it the default value of the field, but got the same result. BTW, Access changed the string adding brackets to this: =fGetSpecialFolderLocation([CSIDL_LOCAL_APPDATA]) -kathy > One way is to set the control's ControlSource property to > [quoted text clipped - 85 lines] >>>>>>>> TIA, >>>>>>>> Kathy I tried your second way, the form's Load event, and got "variable not defined" and it highlighted CSIDL_LOCAL_APPDATA. Do I still need to paste all that between Code Start and Code Endfrom the article you referenced? I'm sorry, I thought these were alternatives to that code. Have I been even dumber than I thought? -kathy > One way is to set the control's ControlSource property to > [quoted text clipped - 85 lines] >>>>>>>> TIA, >>>>>>>> Kathy Yes, you need that code from between Code Start and Code End. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) >I tried your second way, the form's Load event, and got > "variable not defined" and it highlighted CSIDL_LOCAL_APPDATA. [quoted text clipped - 93 lines] >>>>>>>>> TIA, >>>>>>>>> Kathy Well, help is needed here! -I pasted the code from code start to code end in a new module and saved the module as "UserPaths". -I made a field in the switchboard and set its control source to fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA) -The field is displaying #Name? -I tried setting the default value of the field to fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA), but same result: #Name? Sorry to be so needy! -kathy > Yes, you need that code from between Code Start and Code End. > [quoted text clipped - 96 lines] >>>>>>>>>> TIA, >>>>>>>>>> Kathy >-I made a field in the switchboard and set its control source to >fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA) > >-The field is displaying #Name? Use =fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA) This assumes that CSIDL_LOCAL_APPDATA is the name of a control on the switchboard form which contains the information that fGetSpecialFolderLocation needs. John W. Vinson[MVP] Kathy, With respect to Doug and John, might I offer the following. I have been following this post because I have never had a need to us the Transfer Text action and was wondering how it might be used. I kno you said you wanted to use it, but, in what way is somewhat confusing t me. Rather than to get into the what's and why's of my confusion let m say: Based on your required filename .txt, I am assuming you want you user(s) to have access to a NotePad document. So..... Try this and see if it can work for you. Add a command button to the form using the wizard. Select th Application and Run App optons. Browse looking fo c:\windows\notepad.exe. Then when the user clicks the button, notepad will open in the user directory. Have the user first "open" and select the ....txt file or "save", "sav as" or whatever. Like you I am somewhat limited so I couldn't figure out how to ope notepad to an existing file or have it create the file name if i didn't already exist. I tried editing the "code" to show "c:\windows\notepad.exe temp.txt but it still just opened an "untitled" doc. It was in the folder m database was in though. Otherwise Doug and John have far more knowledge than I. Patric -- padd Posted from - http://www.officehelp.i Hmm, I'm trying to display the user apps folder name in a field on the switchboard form, just to see if it is even receiving the information. The name of the field on the switchboard is [UserPathLocation]. I already tried the equal sign, with the same #Name? result. >>-I made a field in the switchboard and set its control source to >>fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA) [quoted text clipped - 10 lines] > > John W. Vinson[MVP] >Hmm, I'm trying to display the user apps folder name in a field on the >switchboard form, just to see if it is even receiving the information. The >name of the field on the switchboard is [UserPathLocation]. I already tried >the equal sign, with the same #Name? result. What is CSIDL_LOCAL_APPDATA? What datatype is it, what's its value, where does that value come from, and what does the function expect? John W. Vinson[MVP] If you look back at this thread, you will see Doug Steele's references to it, and the code where it came from. It should be the equivalent of the c:\documents and settings\username\application data folder. >>Hmm, I'm trying to display the user apps folder name in a field on the >>switchboard form, just to see if it is even receiving the information. [quoted text clipped - 7 lines] > > John W. Vinson[MVP] What did you name the module, Kathy? SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Hmm, I'm trying to display the user apps folder name in a field on the > switchboard form, just to see if it is even receiving the information. [quoted text clipped - 15 lines] >> >> John W. Vinson[MVP] Hi Doug. Glad you are back. I named it UserPaths. After pasting the code in, it seems to have added "Option Compare Database" above the first commented line of the code. > What did you name the module, Kathy? > [quoted text clipped - 17 lines] >>> >>> John W. Vinson[MVP] What happens if you try to compile your application? (While you're in the VB Editor, it's the first option on the Debug menu) SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Hi Doug. Glad you are back. > I named it UserPaths. [quoted text clipped - 22 lines] >>>> >>>> John W. Vinson[MVP] I did it, it seemed to work. Now the option is greyed out. > What happens if you try to compile your application? (While you're in the > VB Editor, it's the first option on the Debug menu) [quoted text clipped - 25 lines] >>>>> >>>>> John W. Vinson[MVP] If it didn't pop up any error messages when you tried to compile, then the option will be greyed out until you make a change and the application needs to be recompiled. In any case, that implies that you copied the code correctly. What happens if you go to the Immediate window (Ctrl-G), type ?fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA) and hit Enter? Do you get the correct path? If you do, then it would appear you've set the field up incorrectly on your switchboard. Where exactly did you put =fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA) for that field? (It needs to be in the ControlSource property) SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) >I did it, it seemed to work. Now the option is greyed out. > [quoted text clipped - 27 lines] >>>>>> >>>>>> John W. Vinson[MVP] With the switchboard on screen, and my cursor blinking in the field, I hit Ctrl+G, get the immediate window, and paste your string and press enter. It displays the correct path (so far this is very exciting!) I double checked, and repasted your value below...it is the ControlSource property of my txtBox called [Text44] in the switchboard. But it is still displaying #Name?. Hey-ulp! (distress call from the southern belle semi-programmer) > If it didn't pop up any error messages when you tried to compile, then the > option will be greyed out until you make a change and the application [quoted text clipped - 43 lines] >>>>>>> >>>>>>> John W. Vinson[MVP] Sorry, but I'm running out of ideas! Just for kicks, try adding a new text box to your form, and setting its ControlSource property to =fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA) (complete with equal sign) SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > With the switchboard on screen, and my cursor blinking in the field, I hit > Ctrl+G, get the immediate window, and paste your string and press enter. [quoted text clipped - 53 lines] >>>>>>>> >>>>>>>> John W. Vinson[MVP] No go...so I put it in the form's OnLoad event, which is one of your original suggestions, and it works like a champ. Thank you for sticking with me on this! You're a doll! > Sorry, but I'm running out of ideas! > [quoted text clipped - 59 lines] >>>>>>>>> >>>>>>>>> John W. Vinson[MVP] One last thing... I know this isn't officially in your realm, but since you've gotten me this far... How do I pass that information to Word? Because after MSAccess saves its query result to the user's appsdata folder as "AccessData.txt", MSAccess stores a form file in the same folder called "mergeform.doc". Word then takes those 2 files and merges them. The data source of "mergeform.doc" is "AccessData.txt". The prior folder where all this was stored was c:\msAccessApp. So my Word macro currently reads: Documents.Open FileName:="C:\msAccessApp\mergeform.doc", ConfirmConversions:= _ False, ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _ PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _ WritePasswordTemplate:="", Format:=wdOpenFormatAuto With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .Execute ...and the data source for mergform.doc was c:\msAccessApp\accessdata.txt Again, my eternal thanks. > No go...so I put it in the form's OnLoad event, which is one of your > original suggestions, and it works like a champ. Thank you for sticking [quoted text clipped - 63 lines] >>>>>>>>>> >>>>>>>>>> John W. Vinson[MVP] Sorry, I've never tried automating Word, so I won't be able to help you on this one. One approach might be to include the user information in the query you're using to create AccessData.txt. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > One last thing... > I know this isn't officially in your realm, but since you've gotten me [quoted text clipped - 91 lines] >>>>>>>>>>> >>>>>>>>>>> John W. Vinson[MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.