After some advice about a project using Excel

[llolslim]

Hi guys,

I'm really enjoying autoit and all the time its saving me.I successfully created a gui program which saves me time and money

Now I wanted to look into the real reason for using it.

Basically... i work with adults with learning disabilities (around 40 of them) and they use our computers (6 computers, 6 peopel at a time). What I would like is to make an autoit program which they would type in their username and password and then it would map their network drive as a result. - basically everything on the computer will be hidden apart from their work/drive.

(We can't afford a proper domain, so I'm hoping this will suffice. It doesnt matter about being insecure etc as they are only locally connected and no info is held on the computers.)

My (very) basic idea is I have a spreadsheet with username | password | networkdrive (Also, i read advice about using an ini file, would this be possible?)

if the username and password are correct, then the drive is mounted using the "networkdrive" as a variable (??). ( I plan to do this via the command prompt)

I am not asking for someone to do this for me, just whether it is possible, and a point in the right direction

i have looked at and downloaded "Microsoft Excel COM UDF library for AutoIt v3" by Locodarwin but I havent really looked at it yet or know how to implement it.

I've also been playing around with gui bits and bobs

So .. is this possible in theory? And

[Kerros]

This should be possible, as you can map or disconnect network drives from a system.

Using an INI file is also possible, If I were to use an INI file I would set it up something like

[Passwords]

username=password

[Networkdrive]

username=path

A successful username password match would then allow you to map the network drives and unmap all the other ones.

Check out INIread in the helpfile for the ini section, DriveMapAdd and DriveMapDel for mapping network drives.

[muncherw]

This should be pretty easy to do. I'd be happy to help you out with this. I've made some drive mapping programs for a previous job and I'm just now learning that Excel Udf. I've used it enough to be able to do what you are asking I think.

[llolslim]

This should be possible, as you can map or disconnect network drives from a system.

Using an INI file is also possible, If I were to use an INI file I would set it up something like

[Passwords]

username=password

[Networkdrive]

username=path

A successful username password match would then allow you to map the network drives and unmap all the other ones.

Check out INIread in the helpfile for the ini section, DriveMapAdd and DriveMapDel for mapping network drives.

thanks, I'll look into that. I've just reformatted and havent reinstalled yet, but will check it out. - i'll hold back from asking questions yet

This should be pretty easy to do. I'd be happy to help you out with this. I've made some drive mapping programs for a previous job and I'm just now learning that Excel Udf. I've used it enough to be able to do what you are asking I think.

Oh brilliant, thanks. I'm pretty confident I can program the mounting and unmounting, that seems like the easy part!! The excel udf I downloaded but havent had a chance to look at it. Again, I'll take a look and come back once I've re-installed.

thank you both! Really looking forward to this

[llolslim]

thanks, I'll look into that. I've just reformatted and havent reinstalled yet, but will check it out. - i'll hold back from asking questions yet

Oh brilliant, thanks. I'm pretty confident I can program the mounting and unmounting, that seems like the easy part!! The excel udf I downloaded but havent had a chance to look at it. Again, I'll take a look and come back once I've re-installed.

thank you both! Really looking forward to this

well, muncherw has come up trumps with a brilliant bit of coding..

which I have played about with and altered very slightly, the only difference being so the drive mapped is the username.

ie login username = "llolslim"

and the folder to be mapped is called "llolslim"

Unfortunately, I dont really understand the main part of it

If $whatever[0][0] = 1 then
    
    For $x = 1 to $whatever[0][0]
            
        $excelPassword = "B" & $whatever[$x][3]
        $field =_ExcelReadCell($oExcel, $excelPassword, 1)
        $driveMapPath = "\\Pc3\"  
        If $field = $password Then
        ; Map X drive to \\Pc3\PC3
            DriveMapAdd("X:", $driveMapPath & $username);the drive will be mapped to the username which is the folder name
        Else
            MsgBox(0,"","Password is incorrect.") 
        EndIf
    
    Next
else
    MsgBox(0,"","Username is incorrect.")
EndIf

I'm now stuck as to how to make it loop if incorrect details are input.

I said to muncherw that i would post here as it doesnt seem fair to keep pestering him, so I'm afraid i will be pestering everyone!

thanks again

loginexample.au3

login_altered.au3

[muncherw]

I'm now stuck as to how to make it loop if incorrect details are input.

I said to muncherw that i would post here as it doesnt seem fair to keep pestering him, so I'm afraid i will be pestering everyone!

thanks again

You can put it in a while loop

CODE

while 1

;main body of code goes here

;if the drive gets mapped then exit the loop

Wend

[llolslim]

You can put it in a while loop

CODE

while 1

;main body of code goes here

;if the drive gets mapped then exit the loop

Wend

apologies for showing my lack of knowledge, but when I put the code in a while loop, it seems to get stuck bringing up the username error box.

I am 99% sure this is due to my lack of knowing where to put Wend, however working on my own logic (due to lack of understanding of the code) nowhere else in the code seems to work.

While 1
If $whatever[0][0] = 1 then
    
    For $x = 1 to $whatever[0][0]
            
        $excelPassword = "B" & $whatever[$x][3]
        $field =_ExcelReadCell($oExcel, $excelPassword, 1)
        $driveMapPath = "\\Pc3\"  
        If $field = $password Then
    ; Map X drive to \\Pc3\PC3
            DriveMapAdd("X:", $driveMapPath & $username);the drive will be mapped to the username which is the folder name
            ExitLoop
        Else
            MsgBox(0,"","Password is incorrect.") 
        EndIf
    Next
else
    MsgBox(0,"","Username is incorrect.")
EndIf   
WEnd

i am trying to figure it out!! Failing unfortunately :>sorry

Edited June 10, 2008 by llolslim

[Kerros]

You placed an exitloop in your For Next loop, which is the only exit there is in the script. You need to place some type of condition to exit the first loop(while loop)

Try changing

while 1

To:

$field = 1
$password = 2
While $field <> $password

[muncherw]

You placed an exitloop in your For Next loop, which is the only exit there is in the script. You need to place some type of condition to exit the first loop(while loop)

Try changing

while 1

To:

$field = 1
$password = 2
While $field <> $password

I told you someone else would come in with much more elegant code. Thanks, Kerros.

Also I apologize for the names of some of the variables. I took it from something I was testing...$whatever is not very descriptive.

[Kerros]

Make sure to close the excel book as well when you are finished.

_ExcelBookClose($oExcel)

[llolslim]

You placed an exitloop in your For Next loop, which is the only exit there is in the script. You need to place some type of condition to exit the first loop(while loop)

Try changing

while 1

To:

$field = 1
$password = 2
While $field <> $password

Right, at first, i thought that would set password to be 2, but now i realise that there are no "" so it doesnt.. What does it do? I tried looking in the helpfile, but I wasn't sure what to look for, I tried looking at the variable info, but there's nothing there.

Also, using that at the beginning and putting Wend after the last EndIf always causes the "Password is incorrect" msgbox to appear, whether correct or incorrect. So again, it's probably down to me putting the Wend in the incorrect place.

I'm thinking I need to do some learning before tackling this project .. agree?!!

Make sure to close the excel book as well when you are finished.

_ExcelBookClose($oExcel)

yey, i did that!

[Kerros]

OK. I made a mistake. I didn't download your whole code, so I was just look at your loop and password wasn't declared in that piece of code. So I declared it again after you had already declared it with the input box.

I put in the $field= 1 just so that $field is something before the loop.

This should basically be your entire code.

Try this. I also put a consolewrite in there to show what $field and $password are. that should help debug what is happening.

#include <ExcelCOM_UDF.au3>
 
 $username = InputBox("Username", "Enter your username:")
 $password = InputBox("Password", "Enter your password:")
 $sFilePath = @WorkingDir & "\book1.xls"  ;Location of Excel spreadsheet
 
;Remember to make Excel spreadsheet hidden when going live.
 $oExcel = _ExcelBookOpen($sFilePath, 0, False, "", "");Open the Excel document
 $sRangeOrRowStart = "A2:A40";This is the field I want the search to start at.
 $whatever = _ExcelFindInRange($oExcel, $username, $sRangeOrRowStart, 1, 800, 800, 0, 2, False, "")
 $field = 1
 While $field <> $password
 If $whatever[0][0] = 1 then
     
     For $x = 1 to $whatever[0][0]
             
         $excelPassword = "B" & $whatever[$x][3]
         $field =_ExcelReadCell($oExcel, $excelPassword, 1)
         ConsoleWrite("ExcelReadCell returned: "&$field&' I typed in: '&$password&@CRLF) 
         $driveMapPath = "\\Pc3\"  
         If $field = $password Then
           ; Map X drive to \\Pc3\PC3
             DriveMapAdd("X:", $driveMapPath & $username);the drive will be mapped to the username which is the folder name
         Else
             MsgBox(0,"","Password is incorrect.") 
         EndIf
     
     Next
 else
     MsgBox(0,"","Username is incorrect.")
 EndIf  

 WEnd
_ExcelBookClose($oExcel)

Edited June 10, 2008 by Kerros

[llolslim]

OK. I made a mistake. I didn't download your whole code, so I was just look at your loop and password wasn't declared in that piece of code. So I declared it again after you had already declared it with the input box.

I put in the $field= 1 just so that $field is something before the loop.

This should basically be your entire code.

Try this. I also put a consolewrite in there to show what $field and $password are. that should help debug what is happening.

hi Kerros,

Many thanks for the input, I have been playing with this non stop!!

.:EDIT:.

To keep this tidy

I am now looking at putting it inside a function, but still i have the problem with it returning username is incorrect.

I guess I need to change to checking somehow, but my oh-so-limited understanding of the code means I can't see why it would be different.

I have tried putting just the checking section of the script in, but for tidyness (and the fact it didnt make a difference) I put it all inside the function.

drivemapWORKING.au3

drivemap_testing.au3

Edited June 13, 2008 by llolslim