How to display the Username of the idea user?

Hi Sridhar,

Sorry I haven't had a chance to get back to you yet.  Hopefully this evening I will have time to dig out the code for you.

Brian

Hi Sridhar,

Thanks for the reminder.  I will try to get to it tonight.

Brian

Hi Sridhar

Here you go.  The script will first ask you to select an IDEA file.  It then connects to the project overview database which is a SDF database (microsoft), I use a program called CompactView to view the file.  I then use a SQL query to access the database, for some strange reason when I try and use the WHERE command to only get that file it gives me an error so instead I am loading the entire database into memory and then using a loop to find the filename.  I exclude task type 6 from the loop as that indicate that the IDEA file has been deleted so I ignore those.  Once it finds a match it returns the username and exits the loop.

Hopefully you can adapt this for your own project.

Option Explicit
Dim sFilename As String
Dim sUsername As String
Dim sProjectFolder As String

Sub Main
	sProjectFolder = client.WorkingDirectory
	Call GetFile()
	sUsername =  GetUsername(sFilename)
	MsgBox sUsername
End Sub

Function GetUsername(sTempFile As String) As String
	Dim connStr As String
	Dim objConn As Object
	Dim rs As Object
	Dim eqn As String
	Dim sTempUsername As String
	connStr = "PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5; Data Source=" & sProjectFolder & "ProjectOverview.sdf"
	eqn = "SELECT * "
	eqn = eqn & " FROM Overview "
	'eqn = eqn & " WHERE Filename = 'period 1.IMD'"

	Set objConn = CreateObject("ADODB.Connection")
		objConn.open connStr
		Set rs = objConn.execute(eqn)
			Do While Not rs.EOF
				If rs.Fields("Filename") = getFileName(sTempFile, 0) And rs.Fields("TaskType") <> 6 Then
					sTempUsername = rs.Fields("UserName")
					Exit Do
				End If
				rs.MoveNext
			Loop
		Set rs = Nothing
		
	Set objConn =  Nothing
	GetUsername = sTempUsername
End Function

Function GetFile()
	Dim obj As Object

	Set obj = Client.CommonDialogs

		sFilename = obj.FileExplorer()

	Set obj = Nothing
End Function

Function getFileName(temp_filename As String, temp_type As Boolean) '1 if get the name with any folder info, 0 if only the name
	Dim temp_length As Integer
	Dim temp_len_wd As Integer
	Dim temp_difference As Integer
	Dim temp_char As String
	Dim tempfilename As String
	
	If temp_type Then
		temp_len_wd  = Len(sProjectFolder)  + 1'get the lenght of the working directory
		temp_length = Len(temp_filename) 'get the lenght of the file along with the working directory
		temp_difference = temp_length - temp_len_wd  + 1'get the lenght of just the filename
		getFileName = Mid(temp_filename, temp_len_wd, temp_difference)	
	Else
		temp_length  = Len(temp_filename )
		Do 
			temp_char = Mid(temp_filename, temp_length , 1)
			temp_length = temp_length  - 1 
			If temp_char <> "\" Then
				tempfilename = temp_char & tempfilename
			End If
		Loop Until temp_char = "\" Or temp_length = 0
		getFileName = tempfilename
	End If
End Function