I'm very new to scripting, and I'm struggling a bit with understanding how to properly use dialog boxes. What I'd like to do is pop up a dialog box to have the user map the fields in their client's data to a standard naming convention, so that I can run a pre-designed analysis. I've been successful at building the script to handle the analysis part by using the macro recorder and the history, but I'm stumped on the dialog box part.
Could anyone point me to some guidance or help me out on this? I've got a copy of Mueller's Mastering IdeaScript but it doesn't seem to cover this.
Thanks!
Leslie
mllestecchino
Offline
Last seen: 5 years 1 month ago
Joined: 12/02/2015 - 15:21
Hi Leslie,
Many of my script do field mapping. Here is an example of a script that maps three fields - http://ideascripting.com/content/payment-date-vs-due-date
Why don't you have a look at what I did and let me know if you have any questions.
Thanks
Brian
Brian,
This is super helpful, thanks for pointing me to it. One other issue I'm trying to resolve - is there any way to script for a file to be used from the user's Local Library? I need to join in a file that each user will have in their own local library, so I can't use a static location since it includes their individual username. I saw that there is a forum discussion on obtaining usernames which I imagine could be a workaround, but I hoped there might be a simpler way to call up things that are in the Local Library.
Thanks again for all the help!
Leslie
Hi Leslie,
I looked around the registry hoping that it would be in there but it isn't so we can't get it from there. I also couldn't find code in VBA to get the windows username, the code you were looking at gets the username associate with the IDEA file which could be different from the windows username. Now if you have upgraded to 10.3 I did find code to get the username in python:
I had to get the username in one of my Access projects. I used this code:
Public Declare Function GetUsername Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Public Function GetUserName()
Dim strLen As Long
Dim strtmp As String * 256
strLen = 255
GetUsername strtmp, strLen
GetUsername = Mid(strtmp, 1, 8)
End Function
notice: our usernames have only 8 characters, you have to try what you need. Perhaps that's what you need ;)
Gerold
Thanks for sharing this. I think you can also do it for Excel. I was trying to find a solution from Windows as not everyone as MS office installed. I should have asked you and we can have done it that way.
Brian
I used below code to get the windows domain & username:
Set WSHnet = CreateObject("WScript.Network")
Let UserName = WSHnet.UserName
Let UserDomain = WSHnet.UserDomain
On Error Resume Next
Set objUser = GetObject("WinNT://" & UserDomain & "/" & UserName & ",user")
Let UserFullName = objUser.FullName
Hi Derek,
Thanks for the code, I knew there was a way to do it, I just couldn't find it when I was looking. So to get the local library the code would look something like this:
Thanks all for the help! I'm making progress, but appreciate if someone can look at my code and see what I'm doing wrong on the dropdown box. I'm trying to resolve the "field selector" part of the dialog box before I handle the rest. Right now I'm not actually using the collected information in the script, but I'm trying to map to replace the field "SSN" that is used once I start appending fields. My dialog box also has a placeholder for the user to enter their username, but I plan on replacing that with the Local Library path suggestions you've all kindly given me.
Right now, when I run the script, the SSN mapping dropdown box is empty, even though I definitely have character fields in my open database. I'm not sure what I've done wrong there. Appreciate any feedback.
I did a quick look at your script and you have the drop-down being populated after you select a file. The problem is you don't have a button for that option. So how the button integer works is that -1 is Ok, 0 is Cancel (I might have that reveresed), the first button you add returns one, the second button returns 2 and so on. So your drop-down gets populated when you select button 1 but in your dialog you only have the OK and Cancel buttons so this option will never be selected and your drop down will never be populated. Try adding a button and selecting it and see if that works. I also recently created a video on making a simple script with a dialog. It might help you out with your project.
Thanks
Brian
Ah, I think I follow. I want everything to run on the currently open database, no file selection. So maybe I just want to change the button integer, is that right?
Pages