Wednesday, October 7, 2015

Download Oracle e-business suite Images on Windows using WGET

Here is a slightly modified version to download from Oracle edelivery site instead of the update site:

 rem ---------------------------------------------------------------------------  
 rem Usage: wget-batch.bat <inputFile> <email> <password>  
 rem  <inputFile> is a plain text file that has the URL and Output Filename  
 rem    separated by a comma (no spaces)  
 rem Dan Iverson - 2015-06-12  
 rem This script takes 3 parameters: inputFile, email, password.   
 rem  Filename is a text file with URL,Filename from the Oracle script.   
 rem  Email and Password are your Oracle Support login credentials.   
 rem The script assumes you have WGET for Windows installed at   
 rem  J:\downloads\wget  
 rem ---------------------------------------------------------------------------  
 rem Input file with "URL,Filename"  
 set LIST=%1  
 rem SSO username and password  
 set SSO_USERNAME=%2  
 set SSO_PASSWORD=%3  
 rem Path to wget command  
 set WGET=J:\downloads\wget\bin\wget.exe  
 rem Output directory and file  
 set OUTPUT_DIR=J:\Downloads  
 rem Location of cookie file  
 set COOKIE_FILE=%OUTPUT_DIR%\pum.cookies  
 rem Log directory and file  
 set LOGDIR=J:\Downloads  
 set LOGFILE=$%LOGDIR%\wgetlog-pumdownload.log  
 rem End of user configurable variable  
 rem Contact updates site so that we can get SSO Params for logging in  
 %WGET% --user-agent='Mozilla/5.0' --no-check-certificate -o %OUTPUT_DIR%\output.txt   
 rem Find the Location part of the response  
 for /F "delims=" %%a in ('findstr "Location" %OUTPUT_DIR%\output.txt') do @set SSO_RESPONSE=%%a  
 rem Strip the first and last portions (split by a space character)  
 for /f "tokens=1,2,3 delims= " %%a in ("%SSO_RESPONSE%") do @set location=%%b  
 rem Create the SSO_SERVER variable (by finding the first two instances of 'p')  
 for /f "tokens=1-2,3 delims=p" %%a in ("%location%") do @set server1=%%a&@set server2=%%b  
 set SSO_SERVER=%server1%p%server2%  
 rem Create the SSO_TOKEN variable (by finding everything after the '=')  
 for /f "tokens=2 delims==" %%a in ("%location%") do @set SSO_TOKEN=%%a  
 set SSO_AUTH_URL=sso/auth  
 set AUTH_DATA="ssousername=%SSO_USERNAME%&password=%SSO_PASSWORD%&site2pstoretoken=%SSO_TOKEN%"  
 rem The following command to authenticate uses HTTPS. This will work only if the wget in the environment  
 rem where this script will be executed was compiled with OpenSSL. Remove the --secure-protocol option  
 rem if wget was not compiled with OpenSSL  
 rem Depending on the preference, the other options are --secure-protocol= auto|SSLv2|SSLv3|TLSv1  
 %WGET% --user-agent='Mozilla/5.0' --secure-protocol=auto --no-check-certificate --post-data %AUTH_DATA% --save-cookies=%COOKIE_FILE% --keep-session-cookies %SSO_SERVER%%SSO_AUTH_URL% -O sso.txt  
 for /f "delims=, tokens=1,2" %%a in (%LIST%) do (  
      %WGET% --user-agent="Mozilla/5.0" --no-check-certificate --load-cookies=%COOKIE_FILE% --save-cookies=%COOKIE_FILE% --keep-session-cookies "%%a" -O %OUTPUT_DIR%/%%b  
 del sso.txt  

Download PeopleSoft Images on Windows using WGET | "SO_USE"

'via Blog this'

Wednesday, September 2, 2015

Upload files to SharePoint using Excel

This is a minor task I undertook quite a while ago, I think someone might want to do something similar and hence decided to share the vba code, note that some of the code was sourced form somewhere else unfortunately I can't find the original source any more.

Public Sub CallWebService()
 'Set and instantiate our working objects

 Dim fileName As String
 Dim fileName2 As String
 Dim base64_data As String
 fileName = Application.GetOpenFilename("Comma Separated Values (*.pdf),*.pdf")

    If fileName <> "False" Then
        'Workbooks.Open fileName, Format:=2
         'Sheets1.Range("A2").Value = EncodeFile(fileName)
         fileName2 = Dir(fileName, vbDirectory)
         'MsgBox fileName2
         base64_data = EncodeFile(fileName)
    End If

    Dim Req As Object
    Dim sEnv As String
    Dim Resp As New MSXML2.DOMDocument60  '60
    Set Req = CreateObject("MSXML2.XMLHTTP")
    Set Resp = CreateObject("MSXML2.DOMDocument.6.0")
    Req.Open "Post", "", False
    ' need to get the filename part
    ' we create our SOAP envelope for submission to the Web Service
    sEnv = ""
    sEnv = sEnv + ""
    sEnv = sEnv + ""
    sEnv = sEnv + "" + fileName + ""
    sEnv = sEnv + " "
    sEnv = sEnv + "" + fileName2 + ""
    sEnv = sEnv + "
"    sEnv = sEnv + ""
    sEnv = sEnv + ""
    sEnv = sEnv + ""
    sEnv = sEnv + " "
    sEnv = sEnv + " "
    sEnv = sEnv + " "
    sEnv = sEnv + "  "
    sEnv = sEnv + "  "
    sEnv = sEnv + "  
"    sEnv = sEnv + "   " + base64_data + ""
    sEnv = sEnv + "
"    sEnv = sEnv + "
"    sEnv = sEnv + "
     'sEnv = sEnv & ";"
     'sEnv = sEnv & "  "
     'sEnv = sEnv & "  "
     'sEnv = sEnv & "   "
     'sEnv = sEnv & "    username"
     'sEnv = sEnv & "    password"
     ''Looks for SKU in active worksheet cell B3
     'sEnv = sEnv & "    " & Range("B3").Value & ""
     'sEnv = sEnv & "  
"     'sEnv = sEnv & "
"     'sEnv = sEnv & "
"    ' Send SOAP Request
    Req.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
    Req.setRequestHeader "SOAPAction", ""
    Req.send (sEnv)

    '"POST", strSoapURL, false, "SVC_E4SE", "Ep1c@rIC#1.2");
    'oXMLHttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    'oXMLHttp.setRequestHeader("SOAPAction", "" + actionName);

' Display results in MessageBox
    'MsgBox Req.responseText
    Resp.LoadXML Req.responseText
    Range("B6").Value = Resp.XML
  'clean up code
    Set Req = Nothing
    Set Resp = Nothing
End Sub

Public Function EncodeFile(strPicPath As String) As String
    Const adTypeBinary = 1          ' Binary file is encoded

    ' Variables for encoding
    Dim objXML
    Dim objDocElem

    ' Variable for reading binary picture
    Dim objStream

    ' Open data stream from picture
    Set objStream = CreateObject("ADODB.Stream")
    objStream.Type = adTypeBinary
    objStream.LoadFromFile (strPicPath)

    ' Create XML Document object and root node
    ' that will contain the data
    Set objXML = CreateObject("MSXml2.DOMDocument")
    Set objDocElem = objXML.createElement("Base64Data")
    objDocElem.DataType = "bin.base64"

    ' Set binary value
    objDocElem.nodeTypedValue = objStream.Read()

    ' Get base64 value
    EncodeFile = objDocElem.Text

    ' Clean all
    Set objXML = Nothing
    Set objDocElem = Nothing
    Set objStream = Nothing

End Function