Script to launch excel file

[bsmith0183]

Taken my code from posting #14 I stripped off the unnecessary lines:

#include <Excel.au3>

Global $sLocalFile = "C:\temp\book1.xls"
Global $sCDFile       = "book1.xls"    

; Check if there is a local copy of the file. 
;   yes: open it. 
;   no:  copy the file from the CD to the local disk and open it
If Not FileExists($sLocalFile) Then 
    If FileCopy($sCDFile,$sLocalFile,8) = 0 Then
        MsgBox(0, "Error!", "Unable to create the local copy of " & $sCDFile)
        Exit
    EndIf
EndIf
$oExcel = _ExcelBookOpen($sLocalFile,1,False,"ReadPassword")
If @error = 1 Then
    MsgBox(16, "Error!", "Unable to Create the Excel Object!")
    Exit
ElseIf @error = 2 Then
    MsgBox(16, "Error!", $sLocalFile & " does not exist!")
    Exit
EndIf

Hi Water

Everything is working great except one thing, When the Excel file is copied it marks it as read only. Which is an issue because, of the file names which have to stay the same.

Meaning after the excel file is copied and opened using the script/exe then the user enters their info and then goes to file to save, a message appears saying that they have

to save it using a different name because the file is read only. Is there any way to uncheck the read only before it copies it from the CD or while it copies the file? The file

has to stay the same so if the user makes changes then saves it I want the CD to be able to pull up the excel file that has there changes made to it.

Thanks for all your help

[water]

After the copy set the attribute of the local file:

#include <Excel.au3>

Global $sLocalFile = "C:\temp\book1.xls"
Global $sCDFile     = "book1.xls"   

; Check if there is a local copy of the file. 
; yes: open it. 
; no: copy the file from the CD to the local disk and open it
If Not FileExists($sLocalFile) Then 
    If FileCopy($sCDFile,$sLocalFile,8) = 0 Then
        MsgBox(0, "Error!", "Unable to create the local copy of " & $sCDFile)
        Exit
    EndIf
    ; Remove Read-Only attribute of local copy
    If Not FileSetAttr($sLocalFile,"-R") Then
        MsgBox(4096,"Error", "Problem setting attributes of " &$sLocalFile)
        Exit
    EndIf
EndIf
$oExcel = _ExcelBookOpen($sLocalFile,1,False,"ReadPassword")
If @error = 1 Then
    MsgBox(16, "Error!", "Unable to Create the Excel Object!")
    Exit
ElseIf @error = 2 Then
    MsgBox(16, "Error!", $sLocalFile & " does not exist!")
    Exit
EndIf

Edited October 19, 2009 by water

[bsmith0183]

Hey Water this project kind of got put off and now its urgent again.

I just tried to make this cd again and its still opening the document as read only

is there a way to copy the file and set the attribute at the same time?

Its almost like the line for the change attribute isnt working right....

Or maybe i am not doing something right

I get an error when I use the last code you gave me after I entered all of the

correct data as well as the password.

Here is my code

#include <Excel.au3>

Global $sLocalFile = "C:\PersonalPropertyPro\PersonalPropertyPROworksheet.XLS"

Global $sCDFile = "PersonalPropertyPROworksheet.XLS"

; Check if there is a local copy of the file.

; yes: open it.

; no: copy the file from the CD to the local disk and open it

If Not FileExists($sLocalFile) Then

If FileCopy($sCDFile,$sLocalFile,8) = 0 Then

MsgBox(0, "Error!", "Unable to create the local copy of " & $sCDFile)

Exit

EndIf

; Remove Read-Only attribute of local copy

If Not FileSetAttr($sLocalFile,"-R") Then

MsgBox(4096,"Error", "Problem setting attributes of " &$sLocalFile)

Exit

EndIf

EndIf

$oExcel = _ExcelBookOpen($sLocalFile,1,true,"PPP001")

If @error = 1 Then

MsgBox(16, "Error!", "Unable to Create the Excel Object!")

Exit

ElseIf @error = 2 Then

MsgBox(16, "Error!", $sLocalFile & " does not exist!")

Exit

EndIf

Thanks

Edited November 13, 2009 by bsmith0183

[water]

Hi,

was my fault . Please change line

If Not FileSetAttr($sLocalFile, "-R") Then

to

If Not FileSetAttrib($sLocalFile, "-R") Then

I tested the statement and it successfully removed the Read-Only Attribute from a file on C:\temp

Hope this helps!

[lordicast]

Sorry but wont it make more sense that instead of copying it to a temp location you run right off the disk?

if you have your EXE at the root of your disk use $sLocalFile for the path and use @ScriptDir. I just think thats easier!?

expandcollapse popup

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <Excel.au3>
$sLocalFile = @ScriptDir & '\ExcelDir\PersonalPropertyPROworksheet.XLS' ;Location where file is on disk
Global $sLicenseText =  "This is your License Text Line 1" & @CRLF & _
                        "This is your License Text Line 2" & @CRLF & _
                        "This is your License Text Line 3"
#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("User License Agreement", 633, 454, 265, 118)
$Edit1 = GUICtrlCreateEdit("", 8, 8, 617, 401, BitOr($ES_READONLY,$WS_VSCROLL,$WS_HSCROLL))
GUICtrlSetData(-1, $sLicenseText)
$Accept = GUICtrlCreateButton("Accept", 8, 416, 129, 25, 0)
$Decline = GUICtrlCreateButton("Decline", 488, 416, 137, 25, 0)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE, $Decline 
            MsgBox(0,"","Decline selected")
            Exit
        Case $Accept
            MsgBox(0,"","Accept selected")
            ExitLoop
     EndSwitch
WEnd

; Accept Button has been pressed
GUIDelete($Form1)
; Open the Excel Workbook readonly and provide a read password
$oExcel = _ExcelBookOpen($sLocalFile,1,True,"PPP001")
If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object!")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist!")
    Exit
EndIf

[bsmith0183]

Hey Water

I put that new line in and it doesn't error out but its still read only when it opens? Not sure y though here is the code again with the new line

#include <Excel.au3>

Global $sLocalFile = "C:\PersonalPropertyPro\PersonalPropertyReplacement.XLS"

Global $sCDFile = "PersonalPropertyReplacement.XLS"

; Check if there is a local copy of the file.

; yes: open it.

; no: copy the file from the CD to the local disk and open it

If Not FileExists($sLocalFile) Then

If FileCopy($sCDFile,$sLocalFile,8) = 0 Then

MsgBox(0, "Error!", "Unable to create the local copy of " & $sCDFile)

Exit

EndIf

; Remove Read-Only attribute of local copy

If Not FileSetAttrib($sLocalFile, "-R") Then

MsgBox(4096,"Error", "Problem setting attributes of " &$sLocalFile)

Exit

EndIf

EndIf

$oExcel = _ExcelBookOpen($sLocalFile,1,true,"PPP001")

If @error = 1 Then

MsgBox(16, "Error!", "Unable to Create the Excel Object!")

Exit

ElseIf @error = 2 Then

MsgBox(16, "Error!", $sLocalFile & " does not exist!")

Exit

EndIf

It sounds like it should work but like I said above it still is read only. ANY IDEAS?

Thanks

[bsmith0183]

Sorry but wont it make more sense that instead of copying it to a temp location you run right off the disk?

if you have your EXE at the root of your disk use $sLocalFile for the path and use @ScriptDir. I just think thats easier!?

expandcollapse popup

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <Excel.au3>
$sLocalFile = @ScriptDir & '\ExcelDir\PersonalPropertyPROworksheet.XLS' ;Location where file is on disk
Global $sLicenseText =  "This is your License Text Line 1" & @CRLF & _
                        "This is your License Text Line 2" & @CRLF & _
                        "This is your License Text Line 3"
#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("User License Agreement", 633, 454, 265, 118)
$Edit1 = GUICtrlCreateEdit("", 8, 8, 617, 401, BitOr($ES_READONLY,$WS_VSCROLL,$WS_HSCROLL))
GUICtrlSetData(-1, $sLicenseText)
$Accept = GUICtrlCreateButton("Accept", 8, 416, 129, 25, 0)
$Decline = GUICtrlCreateButton("Decline", 488, 416, 137, 25, 0)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE, $Decline 
            MsgBox(0,"","Decline selected")
            Exit
        Case $Accept
            MsgBox(0,"","Accept selected")
            ExitLoop
     EndSwitch
WEnd

; Accept Button has been pressed
GUIDelete($Form1)
; Open the Excel Workbook readonly and provide a read password
$oExcel = _ExcelBookOpen($sLocalFile,1,True,"PPP001")
If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object!")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist!")
    Exit
EndIf

Hi Lordicast

If there is a way to do this that you think will work better any info is great what is scriptdir? I am very green at all of this and thankfully the other poster named Water has

helped me so much, I am open to any kind of suggestions!

Thanks

[lordicast]

Well I normally recommend you use the top of the line helpfile.

From Help.

Directory containing the running script. (Result does not contain a trailing backslash)

It is the place where your .exe is at.

d:\ or e:\ is the root of your disk Dir.

Ex;

d:\Myexe.exe

;where your exe is at on your disk

d:\ExcelDir\

;Is a folder at the root

d:\ExcelDir\PersonalPropertyPROworksheet.XLS

;Is a File that is in the folder that is in your root

So if you use,

@ScriptDir & '\ExcelDir\PersonalPropertyPROworksheet.XLS'

it means from d:\ & a folder containing this file

[bsmith0183]

Well I normally recommend you use the top of the line helpfile.

From Help.

Directory containing the running script. (Result does not contain a trailing backslash)

It is the place where your .exe is at.

d:\ or e:\ is the root of your disk Dir.

Ex;

d:\Myexe.exe

;where your exe is at on your disk

d:\ExcelDir\

;Is a folder at the root

d:\ExcelDir\PersonalPropertyPROworksheet.XLS

;Is a File that is in the folder that is in your root

So if you use,

@ScriptDir & '\ExcelDir\PersonalPropertyPROworksheet.XLS'

it means from d:\ & a folder containing this file

Hey the exe files are on the root so this might get us somewhere however the way the disc works is it copies the excel documents to the hard drive and then

launches them from that folder just because I need the customer to have a copy of the excel file on their PC, There is also the issue of saving the file once they

have edited it, it has to be named the same file, long story short the whole reason for the disc to do it that way is so that the customer has to accept the user

license agreement in the beginning, meaning when the customer pops the cd in it asks them to accept the agreement which then takes them to another page in the

menu that allows them to open and edit two excel files, these are password protected so the user cannot just browse to the disc and double click on them without

accepting the license agreement. Once the user clicks the open and edit button it copies the excel file to the hard drive to a folder on the root of c:\ and then

marks it not read only (which by the way is still an issue) then they need to save it with the same name as the one on the hard drive. The reason it has to be the

same name is because they will need to go back to this file and edit it later. The only way they can open the excel file is by rerunning the cd and going through

the license agreement again. Every thing works great the file opens and their changes are there and they can add more. The only issue is that its still read only

the new line that the Poster Water gave me got rid of the error I was getting and when I went to the folder where the cd copied the two excel files too they were not

marked read only which is even weirder since when I launch the file from the cd it still says its read only when I go to save.... kind of seems that its still running it

from the cd or that windows cant tell it launched the one off the hard drive that is not read only

Any Ideas would be much helpful

Thanks

[lordicast]

Questions?

So if its Read Only, then why do they need to save anything at all?

If they can only access it using the disk why not leave it on the disk?

Finally, whats to stop them from searching the disk or there PC for the .xls file and skipping the middle man?

Bottom line is if you want them not to be able to change anything on the .xls then lock everything down on the spread sheet not the file it self. If you want to be able to hide the the file within the .exe i suggest you search for Zedna's way of embedding a file to a .exe.

try embedding a file in search.

[lordicast]

I kind of get what your saying but its easier if you just put it in your license agreement to not change the name of the files you will be ok.

expandcollapse popup

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <Excel.au3>


Global $eFile = 'C:\Excel_Temp\PersonalPropertyPROworksheet.XLS'
Global $sLocalFile = @ScriptDir & '\ExcelDir\PersonalPropertyPROworksheet.XLS' ;Location where file is on disk
Global $sLicenseText = "This is your License Text Line 1" & @CRLF & _
        "This is your License Text Line 2" & @CRLF & _
        "This is your License Text Line 3"

FileCheck()
$Form1 = GUICreate("User License Agreement", 633, 454, 265, 118)
$Edit1 = GUICtrlCreateEdit("", 8, 8, 617, 401, BitOR($ES_READONLY, $WS_VSCROLL, $WS_HSCROLL))
GUICtrlSetData(-1, $sLicenseText)
$Accept = GUICtrlCreateButton("Accept", 8, 416, 129, 25, 0)
$Decline = GUICtrlCreateButton("Decline", 488, 416, 137, 25, 0)
GUISetState(@SW_SHOW)


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE, $Decline
            MsgBox(0, "", "Decline selected")
            Exit
        Case $Accept
            MsgBox(0, 'Accepted Agreement', 'All file names and locations must be remain the same per license agreement')  ;Just add this because anyone can change it from read only
            GUIDelete($Form1)
            Accept()
    EndSwitch
WEnd

Func FileCheck()
    If FileExists($eFile) = 0 Then
        FileCopy($sLocalFile, $eFile, 8)
        If FileExists($eFile) = 0 Then
            MsgBox(0, 'Error', 'File Not Found')
            Exit
        EndIf
    EndIf
EndFunc   ;==>FileCheck


Func Accept()
    If FileExists($eFile) Then
        $oExcel = _ExcelBookOpen($eFile, 1, True, 'PPP001')
        If @error = 1 Then
            MsgBox(0, "Error!", "Unable to Create the Excel Object!")
            Exit
        ElseIf @error = 2 Then
            MsgBox(0, "Error!", "File does not exist!")
            Exit
        EndIf
    Else
        FileCheck()
    EndIf
EndFunc   ;==>Accept

[water]

I think I found the problem. Please change line

$oExcel = _ExcelBookOpen($sLocalFile, 1, True, "PPP001")

to

$oExcel = _ExcelBookOpen($sLocalFile, 1, False, "PPP001")

[bsmith0183]

Hi

Need help with this again.... Product is working great but I need to add some features now. I would like the CD to be able to create a folder on the desktop called Personal Property Pro Pictures and then copy the contents of that folder to a flash drive that I plug into any USB port. I have given my flash drive a volume ID and added a folder on that drive that is called pictures. I would like the contents of the folder on the desktop to be copied into that folder. The volume ID for the flash drive I am using is PPPDrive. I am using Autoplay Media Studio to edit the menu part of this project.

I would also like another EXE to be able to copy a folder that resides on the root of c:\ to the flash drive as well.

So to explain things a little clearer

I need the EXE to be able to check and see if the folders already exist on the flash drive first if they dont then copy them, if they do then ask the user if its OK to copy them and

overwrite the files on the flash drive. My other thought would be to have the code create folders with date stamps or incorporate the date/month into the folder name when it copies them on the flash drive so that they could access the older stuff at a later time?????? Is this at all possible?????? I am using a 4GB flash drive so space will not be an issue at the moment.

Folder that needs to be created on Desktop called - Personal Property Pro Pictures ( not sure if spaces are an issue or not) Would like spaces if possible

Folder on Root of C:\ Which already exists thanks to the code that I already was helped with Called - PersonalPropertyPro copied to flash drive as well(whole folder copied not just contents)

If you have any questions or need more info Please just ask!

Any help would be much appreciated Thanks

Where I left off is post #21

Brian

Edited September 9, 2010 by bsmith0183

[water]

First you need to know if your flash drive is plugged in. If yes, get the drive letter.

Global $sFlashDrive
Global $bFlash = False
Global $sFlashID = "PPPDrive"
Global $aDrives = DriveGetDrive("ALL")
For $iIndex = 1 To $aDrives[0]
    If DriveGetlabel($aDrives[$iIndex]) = $sFlashID Then
        $bFlash = True
        $sFlashDrive = $aDrives[$iIndex]
        ExitLoop
    EndIf
Next
If $bFlash Then
    ConsoleWrite("Flash '" & $sFlashID & "' found on drive " & $bFlashDrive & @CRLF)
Else
    ConsoleWrite("Flash '" & $sFlashID & "' not found!" & @CRLF)
EndIf

Then simply copy all required directories to the flash drive:

FileCopy(@Desktop & "\Personal Property Pro Pictures\*.*",$sFlashDrive & "\")

To check for an existing directory please use FileExists.

Edited September 16, 2010 by water

[bsmith0183]

Hi, Water / Everyone

This is still not quite working, The file copy command doesn't copy anything to to flash drive is that line OK, or do I need to put a path to the desktop? Which would change

according to who is logged in correct? I put C:\Documents and Settings\Brian\Desktop and it copied it fine so, I am not sure what that @desktop thingy is doing. also when I just put the code for the flash drive part it stops at line 13 which is the consolewrite line and it tells me that a variable used was not defined, I think it has to do with the $bflashdrive which has no Global line at the top..... I am not sure if I am at all close to being anywhere right on any of this, but I do need to see if I can get this working. I also was wondering why any EXE I create with autoit doesn't seem to run on Windows 7 64bit...... They run fine on Windows 7 32bit, Is this a 64 bit bug, I was looking at some forums that said you may need to create it with a 64bit system in order for it to run.........Does you know anything about that stuff

Also to be able to create that personal property pro folder on the desktop could I some how incorporate that folder creation to happen when they click the accept button like kinda the same way that when they select a worksheet it copies them to the root of C:\

Thanks

Brian

Edited November 22, 2010 by bsmith0183

[water]

... I am not sure what that @desktop thingy is doing ...

It exactly does ... nothing. @Desktop is an undefined macro. My bad for posting an incorrect code snippet

But this is a good example on how to debug code. If your script doesn't do what you expect try

• Check the error messages returned by SciTE. In this case you get: "ERROR: undefined macro."
• Check the AutoIt help file. When you enter "@desktop" you get no hit. You can see @DesktopCommonDir, @DesktopDir and so on
• It is good practice to check the return value and error code of a function. Something like: ConsoleWrite("Return value: " & $iReturnValue & ", @Error: " & @error & ", @Extended: " & @extended & @CRLF)

So in your case simply replace @Desktop with @DesktopDir and you should be fine.

[JLogan3o13]

Not to confuse the matter but, depending on your requirements for password-protecting the spreadsheet, you could do something similar to the following. I've used the script below in the past, as it buries the spreadsheet into the AutoIT script until your conditions are met. Seems to work well.

$var2 = MsgBox(4, "Accept EULA", "Do you agree?")
If $var2 = 6 Then
 FileInstall("C:\Test.xls", "C:\Test.xls")
 Sleep(2000)
 ShellExecute("C:\Test.xls")
Else
   Msgbox(0, "EULA declined", "Program will exit.")
EndIf

[bsmith0183]

It exactly does ... nothing. @Desktop is an undefined macro. My bad for posting an incorrect code snippet

But this is a good example on how to debug code. If your script doesn't do what you expect try

• Check the error messages returned by SciTE. In this case you get: "ERROR: undefined macro."
• Check the AutoIt help file. When you enter "@desktop" you get no hit. You can see @DesktopCommonDir, @DesktopDir and so on
• It is good practice to check the return value and error code of a function. Something like: ConsoleWrite("Return value: " & $iReturnValue & ", @Error: " & @error & ", @Extended: " & @extended & @CRLF)

So in your case simply replace @Desktop with @DesktopDir and you should be fine.

Hi Water

I entered the text you told me and the EXE does nothing, it doesn't error out but doesn't copy the pictures either

Global $sFlashDrive

Global $bFlash = False

Global $sFlashID = "PPPDrive"

Global $aDrives = DriveGetDrive("ALL")

For $iIndex = 1 To $aDrives[0]

If DriveGetlabel($aDrives[$iIndex]) = $sFlashID Then

$bFlash = True

$sFlashDrive = $aDrives[$iIndex]

ExitLoop

EndIf

Next

FileCopy("@DesktopCommonDir & \Personal Property Pro Pictures\*.*",$sFlashDrive & "\")

Also Could you please help me with the line of code that would create a folder on the desktop called Personal Property Pro Pictures so I can

add it into the existing code????

Thanks

Brian

[water]

To copy the files of the current user change your script like:

Global $sFlashDrive
Global $bFlash = False
Global $sFlashID = "PPPDrive"
Global $aDrives = DriveGetDrive("ALL")
For $iIndex = 1 To $aDrives[0]
    If DriveGetLabel($aDrives[$iIndex]) = $sFlashID Then
        $bFlash = True
        $sFlashDrive = $aDrives[$iIndex]
        ExitLoop
    EndIf
Next
If $bFlash Then
    FileCopy(@DesktopDir & "\Personal Property Pro Pictures\*.*", $sFlashDrive & "\")
Else
    MsgBox(16, "Personal Property Pro", "No PPPDrive found!")
EndIf

Also Could you please help me with the line of code that would create a folder on the desktop called Personal Property Pro Pictures so I can

add it into the existing code?

Please use:

$iResult = DirCreate(@DesktopDir & "\Personal Property Pro Pictures")

Edited January 5, 2011 by water