Dialog Box to Map Fields

mllestecchino

Forums

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 Fri, 01/26/2018 - 13:50

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

Brian Element Sat, 01/27/2018 - 06:36

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:

import getpass
username = getpass.getuser()
print (username)

Otherwise I would suggest having an input box or a dialog and ask the user to enter their windows usernamd and use that variable to build the local library directory structure.

Sorry, I was hoping for an easy solution.

Brian

groldi Mon, 01/29/2018 - 04:14

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

Brian Element Mon, 01/29/2018 - 06:43

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

Derek Mon, 01/29/2018 - 09:08

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
 

Brian Element Mon, 01/29/2018 - 09:39

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:

 


Sub Main
	Set WSHnet = CreateObject("WScript.Network")
    		Let UserName = WSHnet.UserName
    		localLibraryPath = "C:\Users\" & UserName & "\Documents\My IDEA Documents\Local Library"
    		MsgBox localLibraryPath
	Set WSHnet = Nothing
End Sub

mllestecchino Wed, 01/31/2018 - 08:34

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. 

Brian Element Wed, 01/31/2018 - 11:16

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