nasar

I did find it earlier on - but without documentation or examples on how to download and excel file and how to save it back onto sharepoint ...I am stuck....I am a newbie at this having only started a few months ago....so any help would be most appreciated.

Please help - ive been searching long and hard but still unabale to get code that will help me read an excel file that is saved within a SharePoint folder and once i update it - i want to be able to save it back into the same SharePoint folder. My code works when the xls file is on a local drive but I am not able to get this to work when reading or saving to a web address. Please help - anybody...

Thanks danwilli!!!!!. This is what i have done with the code now... ....it seems to be saving fine- just that it takes longer than earlier...not sure if it is because of the way the excel object is being created and all that....again excuse the terrible lengthy lines of simplified coding...I also commented out the global constant cos it kept giving error cannot redefine constant/variable...still seems to be working though. #include <GuiConstantsEx.au3> #include <AVIConstants.au3> #include <TreeViewConstants.au3> #include <misc.au3> #include <String.au3> #include <ExcelCOM_UDF.au3> #include <date.au3> #include <DateTimeConstants.au3> #include <WindowsConstants.au3> ;~ Global Const $xlNormal = -4143 Global $WinMain, $ii, $SaveButton, $MyExcel, $CellRange, $aArray, $oExcelDoc, $oDocument, $iRowCount, $varDate, $handle, $user, $varDateData, $varTime, $varTimeData, $radio, $radioData, $radioDataText, $radio1, $radio2, $radio3, $radio4, $radio5, $radio6, $radioGroup, $Clients, $GetOut, $Dropdown, $radioclear, $Remarks, $RemarksText ; GUI $WinMain = GUICreate("Service Request Logger", 400, 400) $handle = WinGetHandle ( "Service Request Logger") $user = @UserName ; BUTTON $SaveButton = GUICtrlCreateButton("Save", 10, 330, 100, 30) ; PIC ;GUICtrlCreateLabel("Service Request Logger", 75, 1, 153, 15) GUICtrlSetBkColor(-1, 0xFFFFFF) GUICtrlSetColor(-1, 0x000000) ; LIST GUICtrlCreateLabel("Clients:",5,10,60,30) $Clients = GUICtrlCreateList("", 5, 30, 155, 250) GUICtrlSetData(-1, "Client1|Client2|Client3", "") ; GROUP WITH RADIO BUTTONS $radioGroup = GUICtrlCreateGroup("Service Type", 170, 7,225,268) $radio1 = GUICtrlCreateRadio("Test1", 175, 25, 95) ;GUICtrlSetState(-1, $GUI_CHECKED) $radio2 = GUICtrlCreateRadio("Test2", 175, 45, 95) $radio3 = GUICtrlCreateRadio("Test3", 175, 65, 95) $radio4 = GUICtrlCreateRadio("Test4", 175, 85, 95) $radio5 = GUICtrlCreateRadio("Test5", 175, 105, 95) $radio6 = GUICtrlCreateRadio("Test6", 175, 125, 95) $radio7 = GUICtrlCreateRadio("Test7", 175, 145, 95) $radio8 = GUICtrlCreateRadio("Test8", 175, 165, 95) $radio9 = GUICtrlCreateRadio("Test9", 175, 185, 95) $radio10 = GUICtrlCreateRadio("Test10", 175, 205, 95) $radio11 = GUICtrlCreateRadio("Test11", 175, 225, 95) $radio12 = GUICtrlCreateRadio("Test12", 175, 245, 95) $radio13 = GUICtrlCreateRadio("Test13", 290, 25, 95) $radio14= GUICtrlCreateRadio("Test14", 290, 45, 95) $radio15 = GUICtrlCreateRadio("Test15", 290, 65, 95) $radio16 = GUICtrlCreateRadio("Test16", 290, 85, 95) $radio17 = GUICtrlCreateRadio("Test17", 290, 105, 95) $radio18 = GUICtrlCreateRadio("Test18", 290,125,95) $radio19 = GUICtrlCreateRadio("Test19", 290,145,99) $radio20 = GUICtrlCreateRadio("Test20", 290,165,95) $radio21 = GUICtrlCreateRadio("Test21", 290,185,95) $radio22 = GUICtrlCreateRadio("Test22", 290,205,95) $radio23 = GUICtrlCreateRadio("Test23", 290, 225, 95) GUICtrlCreateGroup("", -99, -99, 1, 1) ;close group ; COMBO $Dropdown = GUICtrlCreateCombo("", 170, 280, 140, 100) GUICtrlSetData(-1,"New Case|Follow-up","New Case") ; INPUT GUICtrlCreateLabel("Remarks:", 170,305) $Remarks = GUICtrlCreateInput("", 168, 320, 225, 70) ; DATE $varDate = GUICtrlCreateDate("", 5, 280, 130, 20) ;$varTime = GUICtrlCreateDate("", 140, 280, 70, 20, $DTS_TIMEFORMAT) GUISetState(@SW_SHOW) While 1 Switch GUIGetMsg() Case $GUI_EVENT_CLOSE Exit Case $SaveButton GUISetState(@SW_DISABLE, $WinMain) Local $FileName = @ScriptDir & "\Worksheet1.xls" Local $sPassword = 'test123' If Not FileExists($FileName) Then ; Just a check to be sure.. MsgBox(0, "Excel Data Test", "Error: Can't find file " & $FileName) Exit EndIf Local $oExcel = ObjCreate("Excel.Application") If @error Or (Not IsObj($oExcel)) Then ; Check again if everything went well Return EndIf ;Set to not show excel $oExcel.Visible = 0 ;Save current alert settings Local $fDisplayAlerts = $oExcel.Application.DisplayAlerts Local $fScreenUpdating = $oExcel.Application.ScreenUpdating ;Set alert settings to not show $oExcel.Application.DisplayAlerts = 0 $oExcel.Application.ScreenUpdating = 0 ;Open the file using a password $oExcel.WorkBooks.Open($FileName, Default, 0, Default, $sPassword, Default) ;Select the first sheet in the current workbook $oExcel.ActiveWorkbook.Sheets(1).Select() ;Perform your actions $GetOut = "N" $iRowCount = $oExcel.ActiveSheet.UsedRange.Rows.Count $iRowCount = $iRowCount + 1 $varDateData = GUICtrlRead ($varDate) $varTimeData = GUICtrlRead ($varTime) If GUICtrlRead($radio1)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio1,1) ElseIf GUICtrlRead($radio2)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio2,1) ElseIf GUICtrlRead($radio3)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio3,1) ElseIf GUICtrlRead($radio4)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio4,1) ElseIf GUICtrlRead($radio5)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio5,1) ElseIf GUICtrlRead($radio6)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio6,1) ElseIf GUICtrlRead($radio7)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio7,1) ElseIf GUICtrlRead($radio8)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio8,1) ElseIf GUICtrlRead($radio9)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio9,1) ElseIf GUICtrlRead($radio10)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio10,1) ElseIf GUICtrlRead($radio11)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio11,1) ElseIf GUICtrlRead($radio12)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio12,1) ElseIf GUICtrlRead($radio13)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio13,1) ElseIf GUICtrlRead($radio14)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio14,1) ElseIf GUICtrlRead($radio15)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio15,1) ElseIf GUICtrlRead($radio16)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio16,1) ElseIf GUICtrlRead($radio17)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio17,1) ElseIf GUICtrlRead($radio18)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio18,1) ElseIf GUICtrlRead($radio19)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio19,1) ElseIf GUICtrlRead($radio20)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio20,1) ElseIf GUICtrlRead($radio21)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio21,1) ElseIf GUICtrlRead($radio22)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio22,1) ElseIf GUICtrlRead($radio23)=$GUI_CHECKED Then $radioData=GUICtrlRead ($radio23,1) Else Msgbox (0,"Not Saving","Please select service type") $GetOut = "Y" EndIf If GUICtrlRead ($Clients) ="" Then Msgbox (0,"Not Saving","Please select Customer") $GetOut = "Y" EndIf $RemarksText = GUICtrlRead($Remarks) If $GetOut = "N" Then WITH $oExcel.ActiveSheet .range("A"& $iRowCount & ":A" & $iRowCount).value = $user .range("B"& $iRowCount & ":B" & $iRowCount).value = $varDateData ;~ .range("C"& $iRowCount & ":C" & $iRowCount).value = $varTimeData .range("D"& $iRowCount & ":D" & $iRowCount).value = $radioData .range("E"& $iRowCount & ":E" & $iRowCount).value = GUICtrlRead ($Clients,1) .range("F"& $iRowCount & ":F" & $iRowCount).value = GUICtrlRead ($Dropdown) .range("G"& $iRowCount & ":G" & $iRowCount).value = _NowDate() .range("H"& $iRowCount & ":H" & $iRowCount).value = _NowTime(5) .range("I"& $iRowCount & ":I" & $iRowCount).value = $RemarksText $oExcel.Windows(1).Visible = True ; Otherwise the worksheet window will be saved 'hidden' ;Save the file with a password ;~ $oExcel.ActiveWorkBook.SaveAs($FileName, $xlNormal, $sPassword, Default, Default, Default, 1, 2) $oExcel.ActiveWorkBook.Save ;Close the workbook $oExcel.ActiveWorkbook.Close() Msgbox (0,"Saved","Data saved") ;Return original alert settings $oExcel.Application.DisplayAlerts = $fDisplayAlerts $oExcel.Application.ScreenUpdating = $fScreenUpdating ;Quit the application and release the resources $oExcel.Application.Quit() $oExcel = '' ENDWITH GUICtrlSetState($radio1, $GUI_UNCHECKED) GUICtrlSetState($radio2, $GUI_UNCHECKED) GUICtrlSetState($radio3, $GUI_UNCHECKED) GUICtrlSetState($radio4, $GUI_UNCHECKED) GUICtrlSetState($radio5, $GUI_UNCHECKED) GUICtrlSetState($radio6, $GUI_UNCHECKED) GUICtrlSetState($radio7, $GUI_UNCHECKED) GUICtrlSetState($radio8, $GUI_UNCHECKED) GUICtrlSetState($radio9, $GUI_UNCHECKED) GUICtrlSetState($radio10, $GUI_UNCHECKED) GUICtrlSetState($radio11, $GUI_UNCHECKED) GUICtrlSetState($radio12, $GUI_UNCHECKED) GUICtrlSetState($radio13, $GUI_UNCHECKED) GUICtrlSetState($radio14, $GUI_UNCHECKED) GUICtrlSetState($radio15, $GUI_UNCHECKED) GUICtrlSetState($radio16, $GUI_UNCHECKED) GUICtrlSetState($radio17, $GUI_UNCHECKED) GUICtrlSetState($radio18, $GUI_UNCHECKED) GUICtrlSetState($radio19, $GUI_UNCHECKED) GUICtrlSetState($radio20, $GUI_UNCHECKED) GUICtrlSetState($radio21, $GUI_UNCHECKED) GUICtrlSetState($radio22, $GUI_UNCHECKED) GUICtrlSetState($radio23, $GUI_UNCHECKED) GUICtrlSetState($Clients, $GUI_UNCHECKED) GUICtrlSetState ($Dropdown,$GUI_UNCHECKED) GUICtrlSetData ( $Remarks, "") GUISetState(@SW_ENABLE, $WinMain) WinActivate($handle) EndIf EndSwitch WEnd

Thank you for your prompt reply - I did explore that a while ago while waiting. But it looks like I may have to move away from creating an object and assign the excel file to it and in which case I then get lost as to the erro handling that is part of teh 'borrowed' code i have used ie. If (not @error) and IsObj($oExcelDoc) then ;

Hi, I have a small application that uses an excel file to store data. At this point my excel file does not have a password but I would like to set one and I am bit lost - so that users do not have access to open the file other than through my application (I am okay to hardcode the password). Since im using the following code to open and write to the file - appreciate your help with this - excuse the terrible programming skills and borrowed code... Also at no point do i want the users to see the excel file opening and all that - ideally if all of this can be done without the users seeing the fiel opening and all that it would be great - I was trying to avoid using the SEND fucntion. GUISetState(@SW_SHOW) While 1 Switch GUIGetMsg() Case $GUI_EVENT_CLOSE Exit Case $SaveButton GUISetState(@SW_DISABLE, $WinMain) ; Stops you from changing anything $FileName=@ScriptDir & "\Worksheet1.xls" if not FileExists($FileName) then ; Just a check to be sure.. Msgbox (0,"Excel Data Test","Error: Can't find file " & $FileName) Exit endif $oExcelDoc = ObjGet($FileName) ; Get an Excel Object from an existing filename If (not @error) and IsObj($oExcelDoc) then ; Check again if everything went well $iRowCount = $oExcelDoc.ActiveSheet.UsedRange.Rows.Count $iRowCount = $iRowCount + 1 $oExcelDoc.saved=1 ; Prevent questions from excel to save the file $oExcelDoc.close EndIf WEnd