Jump to content

ADODB: UPDATE Excel from Access database


Recommended Posts

An example of updating an excel file with a join between excel range and access tables.

#include <Excel.au3>
#include <Array.au3>
#include <MsgBoxConstants.au3>
;#include <WinAPIFiles.au3>

;Permitir unha única instancia da aplicación
#include <Misc.au3>
;_Singleton("ADO_Update_Excel_From_Access", 0)
If _Singleton("ADO_Update_Excel_From_Access", 1) = 0 Then
   MsgBox($MB_SYSTEMMODAL, "Warning", "An occurrence of test is already running")
   Exit
EndIf

Opt("MustDeclareVars", 1)
Opt("TrayIconDebug", 1)
OnAutoItExitRegister("OnAutoItExit")

#===== CONFIG =====
Global $sFilePath = @ScriptDir & "\test.xlsx"
Global $sFilePath2 =  @ScriptDir & "\test.mdb"
Global $testmdb = "[;Database=" & $sFilePath2 & ";PWD=123]"
;~ Global $excel = "[Excel 12.0 Xml;HDR=NO;IMEX=1;DATABASE=" & $sFilePath& "]"
;Global $testsqlserver = "[odbc;Driver={SQL Server};SERVER=10.0.0.99;DATABASE=MyDatabaseName;UID=MyUser;PWD=MyPassword]"

#===== ADODB =====
Global $cn, $rst, $sSQL, $SubSQL

;Help: COM Error Handling
;_ErrADODB From spudw2k
;https://www.autoitscript.com/forum/topic/105875-adodb-example/
Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB")

Global Const $iCursorType = 3 ;0 adOpenForwardOnly, 3 adOpenStatic
Global Const $iLockType = 3 ;1 adLockReadOnly, 3 adLockOptimistic
Global Const $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call ; 2 adCmdTable
$cn = ObjCreate("ADODB.Connection") ; Create a connection object
$rst = ObjCreate("ADODB.Recordset") ; Create a recordset object
;Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath2 & ";Jet OLEDB:Database Password=123"
;Global $sADOConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $sFilePath2 & ";Jet OLEDB:Database Password=123"
;Global $sADOConnectionString = 'Driver={Microsoft Access Driver (*.mdb)};Dbq=' & $sFilePath2 & ';uid=;pwd=MyPassword;'
;~ ;Global $sADOConnectionString = 'Provider=SQLOLEDB;Data Source=10.0.0.99;Initial Catalog=MyDatabaseName;User Id=MyUser;Password=MyPassword;'
;~ ;Or if you’re using native client:
;~ ;stConnect = "Provider=SQLNCLI10;Data Source=...

;http://www.connectionstrings.com/
;Xlsx files: Excel 2007 (and later) files with the Xlsx file extension
;[Also valid for] Using the Office 2007 OLEDB driver (ACE 12.0) to connect to older 97-2003 Excel workbooks
;cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & RutaXls & ";Extended Properties=Excel 12.0 Xml;"
Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
;Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Extended Properties=""Excel 12.0;HDR=NO;IMEX=1"";"
;Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Extended Properties=Excel 8.0;"
;Global $sADOConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & $sFilePath & ";ReadOnly=0;"

;https://www.w3schools.com/asp/prop_rs_cursorlocation.asp
;A Recordset object inherits this setting from the associated Connection object.
;This property is read-only on an open Recordset object, and read/write on a Connection object or on a closed Recordset object.
$cn.CursorLocation = 2 ;2 adUseServer, 3 adUseClient
$cn.CommandTimeout = 30

;https://stackoverflow.com/questions/31941487/open-adodb-connection-to-excel-file-in-read-only-mode
;try Mode = adModeRead instead
;By the way, do not put adModeRead in the connections string, but just before openning your connection, add this line: rsConn.Mode = adModeRead
;I tried your suggestion, however since in VBA we do not have direct access to the ADODB built-in constants, I set rsCon.Mode = 1
;as defined in the file adovbs.inc located in the folder "C:\Program Files\Common Files\System\ado"
;and although I watched the rsCon.Mode value being set to adModeRead while debugging, I still have the same problem and the application tries to access the file in Write/Edit mode.
;https://www.w3schools.com/asp/prop_rec_mode.asp
;$cn.Mode = 1 ;Read-only

$cn.Open($sADOConnectionString) ; Open the connection
;MsgBox(0, "", $cn.ConnectionString)

$sSQL = "UPDATE (([Sheet1$A2:C11] a" _
  & " INNER JOIN " & $testmdb & ".[Order_Details] b ON a.F1 = b.ID)" _
  & " INNER JOIN " & $testmdb & ".[Orders] c ON b.ID = c.ID)" _
  & " INNER JOIN " & $testmdb & ".[Customers] d ON c.CustomerID = d.ID" _
  & " SET a.F2 = c.OrderDate, a.F3 = d.CompanyName;"
$cn.Execute($sSQL, Default, 1 + 0x80)  ;adCmdText = 1 , adExecuteNoRecords = 0x80

$sSQL = "SELECT F2, F3, Sum(Quantity * UnitPrice) As Amount" _
   & " FROM [Sheet1$A2:C11] AS a INNER JOIN " & $testmdb & ".[Order_Details] b ON a.F1 = b.ID" _
   & " GROUP BY F2, F3" _
   & " ORDER BY F2;"
$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query

If Not $rst.EOF = True Then
   Local $rstArray = $rst.GetRows()
   _ArrayDisplay($rstArray, "Test", "", $ARRAYDISPLAY_NOROW, "", "F1|F2|F3")
   $rst.Close
   $rst = 0 ;Release the recordset object
   ;$cmd = 0
   $cn.Close ;Close the connection
   $cn = 0  ;Release the connection object

   Global $RecCount = UBound($rstArray)

   #===== EXCEL =====
   Global $oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ;Install a custom error handler
   Global $iEventError ; to be checked to know if com error occurs. Must be reset after handling.

   ;_DebugSetup()
   ;_DebugCOMError()
   ;water: force the Excel UDF to always start up a new instance by using: _Excel_Open(False, Default, Default, Default, True)
   ;Global $oAppl = _Excel_Open(True, False, False, Default, True)
   Global $oAppl = _Excel_Open() ;_Excel_Open(Default, Default, False, Default, Default)
   ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
   ;https://www.autoitscript.com/forum/topic/185789-solved-excel_bookopen-without-wait/?do=findComment&comment=1334509

   ;Restaurar en cada arquivo (algún Application.Run pudo cambiar)
   ;$oAppl.EnableEvents = False
   $oAppl.DisplayAlerts = False

   ;~ ;Arquivo non bloqueado
   ;~ Global $iFileExists
   ;~ For $j = 0 To 60
   ;~    $iFileExists = FileExists($sFilePath2)
   ;~    If $iFileExists Then
   ;~     While _WinAPI_FileInUse($sFilePath2)
   ;~        Sleep(1000)
   ;~     WEnd
   ;~     ExitLoop
   ;~    Else
   ;~     Sleep(1000)
   ;~    EndIf
   ;~ Next

   ;Create a new workbook with only 1 worksheet
   Global $oWorkbook = _Excel_BookNew($oAppl, 1)
   ;If @error Then Exit MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
   ;MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Workbook has been created successfully with only 1 worksheets.")

   ;~ Global $oWorkbook = _Excel_BookOpen($oAppl, $sFilePath5, False, True)
   ;~ ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookOpen: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended)
   ;~ ;Create a new workbook with only 1 worksheet
   ;~ ;Global $oWorkbook = _Excel_BookNew($oAppl, 1)
   ;~ ;If @error Then Exit MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
   ;~ ;MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Workbook has been created successfully with only 1 worksheets.")

   ;Sleep(3000)
   ;~ Global $sMessage, $sMessage2
   ;~ SplashTextOn("TitleFoo", $sMessage, 580, 60, 900, 840, 1 + 4, "", 16)
   ;~ For $i = 1 To 10
   ;~    $sMessage = $sMessage & "."
   ;~    $sMessage2 = @TAB & "Pausa " & $sMessage
   ;~    ControlSetText("TitleFoo", "", "Static1", $sMessage2)
   ;~    Sleep(1000)
   ;~ Next

   $oWorkbook.UpdateLinks = 2 ;xlUpdateLinksNever

   ;Global $oSheets = $oWorkbook.Sheets
   Global $oSheet = $oWorkbook.ActiveSheet
   ;Global $oSheet = $oWorkbook.Sheets("Sheet1")
   ;MsgBox(0, "", $oSheet.Name)

   $oSheet.Range("A1:C1").Font.Bold = True
   $oSheet.Range("A1:A" & $RecCount + 1).NumberFormat = "dd/mm"
   $oSheet.Range("B1:B" & $RecCount + 1).NumberFormat = "@"

   Global $oPageSetup = $oSheet.PageSetup
   With $oPageSetup
      .PrintTitleRows = "$1:$1"
      .PrintTitleColumns = ""
      .PrintArea = ""
      .LeftHeader = "&D"
      .CenterHeader = "Report"
      .RightHeader = "&P of &N"
      ;.LeftFooter = "&F {&A}"
      .CenterFooter = ""
      .RightFooter = ""
      .LeftMargin =  28
      .RightMargin =  28
      .TopMargin =  28
      .BottomMargin =  28
      .HeaderMargin =  15
      .FooterMargin =  15
      .PrintHeadings = False
      .PrintGridlines = True
      .PrintComments = -4142
      .CenterHorizontally = False
      .CenterVertically = False
      .Orientation = 1 ;2
      .Draft = False
      .FirstPageNumber = -4105
      .Order = 1
      .BlackAndWhite = True
      .Zoom = 100
   EndWith

   ;https://www.autoitscript.com/forum/topic/195252-_excel_rangewrite-doesnt-write-array-from-adodb-getrows/
   Global $TrstArray = $rstArray
   _ArrayTranspose($TrstArray)
   $oSheet.Range("A2:C" & $RecCount + 1).Value = $TrstArray

   Global $aArray2D[1][4] = [["Date", "Client", "Amount"]]
   _Excel_RangeWrite($oWorkbook, $oSheet, $aArray2D, $oSheet.Cells(1, 1))
   ;Global $aArray1D[11] = ["ID", "Udes", "Descrip", "Matricula", "Kilos", "Proveedor", "Corredor", "Fecha", "Contrato", "Restan", "Tanque"]
   ;$oSheet.Range("A1:K1").value = $aArray1D
   ;Global $aArray2D[1][6] = [[$rstArray[$i][1], $rstArray[$i][2], $rstArray[$i][3], $rstArray[$i][4], $rstArray[$i][5], $rstArray[$i][6]]]
   ;_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray2D, $oSheet.Cells($UltimaFila, 1).Resize(1, 6))
   ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_RangeWrite: " & @CRLF & "@error = " & @error & ", @extended = " & @extended)
   ;Global $aArray2D[3][5] = [[11, 12, 13, 14, 15], [21, 22, 23, 24, 25], [31, 32, 33, 34, 35]]
   ;_Excel_RangeWrite($oWorkbook, Default, $aArray2D, "B1")
   ;Local $aArray1D[13] = ["Ped", "Archivo", "Abono", "NomCli", "H+I", "ACIDEZ", "CERAS", "E+U", "aa", "aa", "aa", "aa", "aa"]
   ;$oSheet.Range("A1:M1").value = $aArray1D
   ;$oSheet.Cells(1, 1).Resize(1, 13).value = $aArray1D

   ;_Excel_BookSaveAs($oWorkbook, $sFilePath, $xlOpenXMLWorkbook, True) ;$xlOpenXMLWorkbook  51  ;$xlExcel8  56
   ;_Excel_BookClose($oWorkbook, False)
   ;_Excel_BookClose($oWorkbook, True)
   ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookClose: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended)

   ;~ While _WinAPI_FileInUse($sFilePath5)
   ;~    Sleep(1000)
   ;~ Wend
   ;~ Sleep(3000)

   ;$oAppl.EnableEvents = True
   $oAppl.DisplayAlerts = True

   ;https://www.autoitscript.com/forum/topic/136414-excel-close-problem/?do=findComment&comment=953433
   ;$oAppl.Application.Quit
   ;$oAppl = ""
   ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/
   ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1262478
   ;Run(@ComSpec & " /c " & 'taskkill /im excel.exe /f /t', "", @SW_HIDE)
   ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1262830
   ;water / Ok. Let's see if the problem is caused by open/close or by working with a workbook. Could you please try:
   ;#include <Excel.au3>
   ;$oExcel = _Excel_Open(False, False, False, False, True)
   ;$oExcelClose = _Excel_Close($oExcel, False, True)
   ;~ _Excel_Close($oAppl, False, Default)
   ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended)

   ;~ Sleep(1000)

   ;~ ;Check excel closed
   ;~ Local $aProcesses = ProcessList("Excel.exe")
   ;~ ;_ArrayDisplay($aProcesses)
   ;~ If $aProcesses[0][0] > 0 Then
   ;~    ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1263191
   ;~    ;@water, thanks for your help so far, at least we pinned down that it's not a UDF bug. :)
   ;~    ;For now I will use a crude workaround by closing the most recent Excel.exe instance:
   ;~    ProcessClose($aProcesses[$aProcesses[0][0]][1])
   ;~    Sleep(100) ;just to allow some time for the process to definitely close (if it does close)
   ;~ EndIf
Else
   $rst.Close
   $rst = 0 ; Release the recordset object
   $cn.Close ; Close the connection
   $cn = 0 ; Release the connection object
   ;Disconnect

   MsgBox(262144, "", "Empty Recordset", 5)
EndIf

;This is a custom error handler
Func ErrFunc()
   Local $HexNumber = Hex($oMyError.number, 8)
;~    MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _
;~       "Number is: " & $HexNumber & @CRLF & _
;~       "WinDescription is: " & $oMyError.windescription)
   ConsoleWrite("->    We intercepted a COM Error !" & @CRLF & _
   "->    err.number is: " & @TAB & $HexNumber & @CRLF & _
   "->    err.source: " & @TAB & $oMyError.source & @CRLF & _
   "->    err.windescription: " & @TAB & $oMyError.windescription & _
   "->    err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF)

   $iEventError = 1 ; Use to check when a COM Error occurs
EndFunc   ;==>ErrFunc

Func _ErrADODB()
   Msgbox(0,"ADODB COM Error","We intercepted a COM Error !"      & @CRLF  & @CRLF & _
       "err.description is: "    & @TAB & $errADODB.description    & @CRLF & _
       "err.windescription:"     & @TAB & $errADODB.windescription & @CRLF & _
       "err.number is: "         & @TAB & hex($errADODB.number,8)  & @CRLF & _
       "err.lastdllerror is: "   & @TAB & $errADODB.lastdllerror   & @CRLF & _
       "err.scriptline is: "     & @TAB & $errADODB.scriptline     & @CRLF & _
       "err.source is: "         & @TAB & $errADODB.source         & @CRLF & _
       "err.helpfile is: "       & @TAB & $errADODB.helpfile       & @CRLF & _
       "err.helpcontext is: "    & @TAB & $errADODB.helpcontext, 5)

   Local $err = $errADODB.number
   If $err = 0 Then $err = -1

   ;Devolver datos error
   Local $sFilePath = @DesktopDir &  "\error.txt"
   ;Open the file for write access.
   Local $hFileOpen = FileOpen($sFilePath, 2)
   ;If $hFileOpen = -1 Then
     ;MsgBox(0, "", "An error occurred when reading/writing the file.")
   ;EndIf

   FileWrite($hFileOpen, "ADODB COM Error" & Chr(1) & _
          "err.description is: "    & @TAB & $errADODB.description    & Chr(1) & _
          "err.windescription:"     & @TAB & $errADODB.windescription & Chr(1) & _
          "err.number is: "         & @TAB & hex($errADODB.number,8)  & Chr(1) & _
          "err.lastdllerror is: "   & @TAB & $errADODB.lastdllerror   & Chr(1) & _
          "err.scriptline is: "     & @TAB & $errADODB.scriptline     & Chr(1) & _
          "err.source is: "         & @TAB & $errADODB.source         & Chr(1) & _
          "err.helpfile is: "       & @TAB & $errADODB.helpfile       & Chr(1) & _
          "err.helpcontext is: "    & @TAB & $errADODB.helpcontext _
         )

   ;Close the handle returned by FileOpen.
   FileClose($hFileOpen)

   $rst = 0
   ;$cmd = 0
   $cn.Close
   $cn = 0
   ;Disconnect

   Exit
EndFunc

Func OnAutoItExit()
   $rst = 0 ;Release the recordset object
   If IsObj($cn) Then
      If $cn.State > 0 Then $cn.Close ;adStateOpen Close the connection
      $cn = 0 ; Release the connection object
   EndIf
EndFunc

 

example_files.zip

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By mLipok
      I want to present BETA Version of my ADO.au3 UDF.
      This is modifed version of _sql.au3 UDF.
       
      For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH
       
      This is first public release , and still is as BETA
       
       
      DOWNLOAD LINK (in download section): 
       
       
      Have fun,
      mLipok
       
       
      EDIT: 2016-06-03
      Below some interesting topics about databases:
       
       
      EDIT 2016/07/04:
      For more info about ADO look here:
      https://www.autoitscript.com/wiki/ADO
      FOR EXAMPLE DATABASE use AdventureWorksDW2016_EXT.bak from:
      https://github.com/microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksDW2016_EXT.bak
      I will relay on this database in my examples.
       
      Here is link to post which shows how "ODBC Data Source Administrator" looks like.
       
    • By Arual
      We have AutoIt code that performs a connection to an MS SQL database running SQL server 2012, tls 1.0. It has worked successfully for years.
      Today, our IT department migrated our database to a new server that is running SQL server 2016, tls 1.2. Now our connection string is no longer working.
      Here is the original that was working in SQL server 2012
      $sConnStr = "DRIVER={SQL Server};SERVER=servername,port;DATABASE=dbname;UID=user;PWD=pass" When that would run on the new server in SQL server 2016. We are getting the error [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error
      We tried changing the connection string to 
      $sConnStr = "DRIVER={SQL Server Native Client 11.0};SERVER=servername,port;DATABASE=dbname;UID=user;PWD=pass" The new error we received is [Microsoft]
      TCP Provider: An existing connection was forcibly closed by the remote host. We can log in successfully using SSMS using the server name, port and user/pass.
      Any suggestions on what we need to change in order to get the connection to work again?
    • By goku200
      I'm having some issues with my script that's not grabbing the id from the cell and appending it to the url test that I have in my script.
      Here is my script:
      #Include "wd_core.au3" #Include "wd_helper.au3" #Include "wd_core.au3" #Include "File.au3" #Include "Array.au3" #Include "Excel.au3" #Include "INet.au3" Local $sDesiredCapabilities, $sSession _WD_Startup() $Ssession = _WD_CreateSession($sDesiredCapabilities) _WD_Navigate($sSession, "https://127.0.0.1/test.html?id=1");session is created. When tabed on browser it will populate url _WD_LoadWait($sSession) $someUser = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//input[@name='username']") _WD_SetElementValue($sSession, $someUser, "test") $somePassword = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//input[@name='password']") _WD_SetElementValue($sSession, $somePassword, "test") Local $aVar[1] _ArrayAdd ($aVar,_WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//input[@type='submit'][@value='Sign in']")) _WD_ElementAction($sSession, $aVar[1], 'click') _WD_LoadWait($sSession) Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\<Username>\Downloads\test.xlsx") Local $aArray1 = _Excel_RangeRead($oExcel, 1) _ArrayDisplay($aArray1, "Vertical") For $i = 0 To UBound($aArray) - 1 WinActivate("[Class:Chrome]") $testLink = "https://127.0.0.1/test.html?id=" _WD_Navigate($sSession, $testLink) $aArray1 = ClipGet() _WD_NewTab($Ssession, clipPut($testLink), True) Send("{ENTER}") Local $test = InetGet($testLink + $aArray1, "C:\Users\<username>\Downloads\Test" & ".html") Send ("!s");save webpage as html Next Func SetupChrome() _WD_Option('Driver', 'chromedriver.exe') _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}' EndFunc ;==>SetupChrome What I'm having issues is looping through the id column in the excel spreadsheet and adding 1 to the url address when its tabbed by using _WD_NewTab. When it goes to the browser I'm wanting it to display: https://127.0.0.1/test.html?id=1 press Enter and save the file as 1.html and so forth for the others. Not sure what I'm doing wrong here. 
      test.xlsx
    • By mLipok
      In the past there was many questions about how to: "Automatic file upload using without user interaction"

      https://www.autoitscript.com/forum/topic/92907-ie-8-input-namenomfic-typefile-idnomfic/
      https://www.autoitscript.com/forum/topic/116899-cant-automate-input-typefile-tag-in-ie/?tab=comments#comment-815478
      https://www.autoitscript.com/forum/topic/14883-input-typefile/
      https://www.autoitscript.com/forum/topic/188708-how-to-set-the-value-of-an-input-typefile-element/
      https://www.autoitscript.com/forum/topic/91513-how-can-i-auto-set-file-path-for-input-file-in-ie/
      https://www.autoitscript.com/forum/topic/116899-cant-automate-input-typefile-tag-in-ie/
      https://www.autoitscript.com/forum/topic/169190-how-to-script-file-upload-button/
      https://www.autoitscript.com/forum/topic/145327-how-to-deal-with-ie-window-for-upload-a-fileinput-typefile/
      https://www.autoitscript.com/forum/topic/140482-internet-explorer-input-file-problem/
       
      I found solution here: 
      https://stackoverflow.com/questions/33253517/upload-a-file-via-input-input-in-html-form-with-vba
      and:
      https://www.motobit.com/tips/detpg_uploadvbsie/
      And I translate this code to AutoIt3 code:
      ; Upload file using http protocol And multipart/form-data ; v1.01 ; 2001 Antonin Foller, PSTRUH Software Global $oErrorHandler = ObjEvent("AutoIt.Error", _ErrFunc) do_vbsUpload() Func do_vbsUpload() #cs ; We need at least two arguments (File & URL) ConsoleWrite('- ' & @ScriptLineNumber & @CRLF) If $CmdLine[0] < 2 Then InfoEcho() ConsoleWrite('- ' & @ScriptLineNumber & @CRLF) ; Are some required objects missing? If StringInStr(CheckRequirements(), "Error") > 0 Then InfoEcho() ConsoleWrite('- ' & @ScriptLineNumber & @CRLF) Local $s_FileName, $s_DestURL, $s_FieldName $s_FieldName = "FileField" ; Default field name For $i_argCounter = 1 To $CmdLine[0] ConsoleWrite('+ '& $i_argCounter& ' >> ' & $CmdLine[$i_argCounter] & @CRLF) Select Case $i_argCounter = 1 ;~ $s_FileName = $CmdLine[$i_argCounter] $s_FileName = @ScriptFullPath Case $i_argCounter = 2 $s_DestURL = $CmdLine[$i_argCounter] Case $i_argCounter = 3 $s_FieldName = $CmdLine[$i_argCounter] EndSelect Next UploadFile($s_DestURL, $s_FileName, $s_FieldName) #ce UploadFile('http://www.dobeash.com/test.html', @ScriptFullPath, 'fileExample') EndFunc ;==>do_vbsUpload ; ******************* upload - begin ; Upload file using input type=file Func UploadFile($s_DestURL, $s_FileName, $s_FieldName) ; Boundary of fields. ; Be sure this string is Not In the source file Const $Boundary = "---------------------------0123456789012" ; Get source file As a binary data. Local $d_FileContents = GetFile($s_FileName) ; Build multipart/form-data document Local $s_FormData = BuildFormData($d_FileContents, $Boundary, $s_FileName, $s_FieldName) ; Post the data To the destination URL IEPostBinaryRequest($s_DestURL, $s_FormData, $Boundary) EndFunc ;==>UploadFile ; Build multipart/form-data document with file contents And header info Func BuildFormData($d_FileContents, $Boundary, $s_FileName, $s_FieldName) Const $s_ContentType = "application/upload" ; The two parts around file contents In the multipart-form data. Local $s_Pre = "--" & $Boundary & @CRLF & mpFields($s_FieldName, $s_FileName, $s_ContentType) Local $s_Po = @CRLF & "--" & $Boundary & "--" & @CRLF ; Build form data using recordset binary field Const $i_adLongVarBinary = 205 Local $oRS = ObjCreate("ADODB.Recordset") ; https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/append-method-ado?view=sql-server-ver15 $oRS.Fields.Append("b", $i_adLongVarBinary, StringLen($s_Pre) + BinaryLen($d_FileContents) + StringLen($s_Po)) $oRS.Open() $oRS.AddNew() ; Convert Pre string value To a binary data Local $i_LenData = StringLen($s_Pre) $oRS("b").AppendChunk(StringToMB($s_Pre) & StringToBinary(Chr(0))) $s_Pre = $oRS("b").GetChunk($i_LenData) $oRS("b") = "" ; Convert Po string value To a binary data $i_LenData = StringLen($s_Po) $oRS("b").AppendChunk(StringToMB($s_Po) & StringToBinary(Chr(0))) $s_Po = $oRS("b").GetChunk($i_LenData) $oRS("b") = "" ; Join Pre & $d_FileContents & Po binary data $oRS("b").AppendChunk($s_Pre) $oRS("b").AppendChunk($d_FileContents) $oRS("b").AppendChunk($s_Po) $oRS.Update() Local $s_FormData = $oRS("b") $oRS.Close() Return $s_FormData EndFunc ;==>BuildFormData ; sends multipart/form-data To the URL using IE Func IEPostBinaryRequest($s_URL, $s_FormData, $Boundary) ; Create InternetExplorer Local $oIE = ObjCreate("InternetExplorer.Application") ; You can uncoment Next line To see form results $oIE.Visible = True ; Send the form data To $s_URL As POST multipart/form-data request $oIE.Navigate($s_URL, '', '', $s_FormData, _ "Content-Type: multipart/form-data; boundary=" & $Boundary & @CRLF) While $oIE.Busy Wait(1, "Upload To " & $s_URL) WEnd ; Get a result of the script which has received upload ;~ On Error Resume Next Local $s_IE_InnerHTML = $oIE.Document.body.innerHTML MsgBox(0, 'TEST #' & @CRLF & @ScriptLineNumber, $s_IE_InnerHTML) $oIE.Quit() Return $s_IE_InnerHTML EndFunc ;==>IEPostBinaryRequest ; Infrormations In form field header. Func mpFields($s_FieldName, $s_FileName, $s_ContentType) Local $s_MPTemplate = _ ; template For multipart header 'Content-Disposition: form-data; name="{field}";' & _ 'FileName="{file}"' & @CRLF & _ 'Content-Type: {ct}' & @CRLF & @CRLF & _ '' Local $s_Out $s_Out = StringReplace($s_MPTemplate, "{field}", $s_FieldName) $s_Out = StringReplace($s_Out, "{file}", $s_FileName) $s_Out = StringReplace($s_Out, "{ct}", $s_ContentType) Return $s_Out EndFunc ;==>mpFields Func Wait($i_Seconds, $s_Message) MsgBox(64, '', $s_Message, $i_Seconds) EndFunc ;==>Wait ; Returns file contents As a binary data Func GetFile($s_FileName) Local $oStream = ObjCreate("ADODB.Stream") $oStream.Type = 1 ; Binary $oStream.Open() $oStream.LoadFromFile($s_FileName) Local $d_GetFile = $oStream.Read() $oStream.Close() Return $d_GetFile EndFunc ;==>GetFile ; Converts OLE string To multibyte string Func StringToMB($S) Local $I, $B For $I = 1 To StringLen($S) $B &= StringToBinary(Asc(StringMid($S, $I, 1))) Next Return $B EndFunc ;==>StringToMB ; ******************* upload - end ; ******************* Support ; Basic script info Func InfoEcho() Local $sMsg = _ "Upload file using http And multipart/form-data" & @CRLF & _ "Copyright (C) 2001 Antonin Foller, PSTRUH Software" & @CRLF & _ "use" & @CRLF & _ "[cscript|wscript] fupload.vbs file $s_URL [fieldname]" & @CRLF & _ " file ... Local file To upload" & @CRLF & _ " $s_URL ... $s_URL which can accept uploaded data" & @CRLF & _ " fieldname ... Name of the source form field." & @CRLF & _ @CRLF & CheckRequirements() & @CRLF & _ "" ConsoleWrite('! ' & $sMsg & @CRLF) EndFunc ;==>InfoEcho ; Checks If all of required objects are installed Func CheckRequirements() Local $sMsg = _ "This script requires some objects installed To run properly." & @CRLF & _ CheckOneObject("ADODB.Recordset") & @CRLF & _ CheckOneObject("ADODB.Stream") & @CRLF & _ CheckOneObject("InternetExplorer.Application") & @CRLF & _ "" Return $sMsg ; $sMsgBox $sMsg EndFunc ;==>CheckRequirements ; Checks If the one object is installed. Func CheckOneObject($sClassName) Local $sMsg ObjCreate($sClassName) If @error = 0 Then $sMsg = "OK" Else $sMsg = "Error:" & @error EndIf Return $sClassName & " - " & $sMsg EndFunc ;==>CheckOneObject ; ******************* Support - end ; User's COM error function. Will be called if COM error occurs Func _ErrFunc(ByRef $oError) ; Do anything here. ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _ @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _ @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _ @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _ @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _ @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _ @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _ @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _ @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _ @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF) EndFunc ;==>_ErrFunc  
      But I miss something and the code not works as intendend.
      Please join and contribute, in solving this issue, as this will be handy for entire community.
      @mLipok
       
      btw.
      I think that this may be realated to ChrB() which I simply translate to StringToBinary()
      Especialy this :
      StringToBinary(Chr(0))) could be the main issue.
      But for now I'm tired and going to sleep.
      Hope maybe tomorrow somebody solve this issue.
       
    • By Cristin
      Dear all,
      first of all hi.
      I`m in trouble, again 😕
      I can`t find right solution, tried all possible combinations generated by my mind and nothing.
      I need to copy range of values depending by values from two different cells in excel sheet.
      I put an example in attachment.

      I have two values which is never changed (is changed only TEXT). I need to copy all the rows from VALUE 1 to VALUE 2 in new Sheets (Output 1, Output 2, ... , Output X).
      It is possible to do this using Autoit? 🤔
      Also in attachment you can find last example which I have tried and it`s something near, it is copying by _Excel_RangeFind row with needed VALUE 1 but I need to copy whole range from VALUE 1 to VALUE 2 😵 ( see in attachment examples).
      Thank you very much, in advance, for your support, solution really will make my life much easier 🙃
      Have a nice day all of you!
      Best Regards,
      Cristin
      Book1.xlsx something.au3
×
×
  • Create New...