Astuces Excel
  1. Astuces Excel
  3. Excel VBA exportant un set Java
Intereting Posts
Changez la couleur des cellules en fonction de la couleur d'un autre appel. Exceller J'ai des problèmes avec la boucle de mon vba Obtenir la cellule de valeur de la cellule avec une valeur minimale dans une plage Transposer toutes les 2 colonnes à la nouvelle ligne Désactivation de la propriété de modification dans Excel Écrire à la feuille de calcul excel depuis un object personnalisé Problème face à la boîte de dialog Cliquez sur OS Recherche de valeurs dans un tableau dynamic (vba) Est-il possible via le côté server d'appliquer l'ajout d'Excel à .csv? Jasper annonce des problèmes de sortie Excel SSIS 2005: abréviations de mois valides Obtenir des données des colonnes adjacentes Valeurs moyennes par rangée drop down menu relationnel dans excel Créer un hyperlink vers une autre cellule

Excel VBA exportant un set Java

J'ai le suivant à partir d'un code source html: 

<html> <head> <link rel="stylesheet" href="../lib/edb.css"> <title>SEARCH SPEC</title> <script type="text/javascript"> function TableToExcel2(tableId) { var x=MyTable.rows var xls = new ActiveXObject("Excel.Application") xls.visible = true xls.Workbooks.Add for (i = 0; i < x.length; i++) { var y = x[i].cells for (j = 0; j < y.length; j++) { xls.Cells( i+1, j+1).Value = y[j].innerText } } } </script> </HEAD> <BODY> <h1>Search Veh Specifications </H1> <PRE> <b>Signature :</b> VTNA<br><b>Identity :</b> <br><b>Product Class :</b> 04<br><b>Variants :</b> <br><b>Build time :</b> 1601-1602<br><b>Spec Week :</b> -<br><b>Model :</b> <br><b>Number of orders:</b> 318<br><b>Total quantity :</b> 1267<br><BR><BR><a href='tmp/1489149209.csv'>RESULTS IN EXCEL FILE (CSV)</a> <b>Signature Identity <span title="Build week YYYYWW">Build</span> <span title="Spec week YYYYWW">Spec W.</span> <span title="Last update YYYYWWD">Updated</span> Model Qty Customer <span title="AXLE ARRANGEMENT">DAX </span><span title="REAR AXLE LOAD">F1X </span><span title="REAR AXLE TYPE">R1X </span><span title="REAR AXLE">R2X </span><span title="REAR AXLE CASING WIDTH">6MA </span><span title="BRAKE LINING MATERIAL DRIVE">MAX </span><span title="DRIVE AXLE BRAKE DUST SHIELD">UEX </span><span title="BRAKE DIMENSION, DRIVE">U8X </span><span title="BRAKE TYPE, REAR">V5A </span><span title="ABS SENSOR & MODULATOR">9GA </span><span title="REAR AXLE ARRANGEMENT">FDX </span><span title="AXLE INSTALLATION">F2X </span><span title="AXLE MANUFACTURER">FWX </span><span title="AXLE TYPE CAPACITY">FXX </span><span title="AXLE CONCEPT">FUX </span><span title="TYPE, FRONT">V4A </span><span title="DIMENSION, FRONT">U5X </span><span title="LINING MATERIAL FRONT">LQX </span><span title="DUST SHIELD">UDX </span></b> VTNA <A target=_blank HREF="http://**************/vehspec.cgi?func=1&signature=VTNA&identity=969375&funcflag=1&funcflag=1&short_spec=1">969375</A> 201602 201602 2016044 V64T 1 NEW CUSTOMER 6*4 RAL21 SRED-M RT2173SM RACWD-ST BLMD-M13 UDBRDUST RBRA220 RWBRT-S ABS6S6M RAA22 <font color=red>F2X-R2X </font>FATYPM-V FATYPE91 FRAX-UNI FWBRT-D3 FBRA-D43 BLMF-T1 FBRDUST <a target=_blank href="http://**************/_identity=969375&find_flag=on&time_from=1">Restr</a> VTNA <A target=_blank HREF="http://**************/vehspec.cgi?func=1&signature=VTNA&identity=965745&funcflag=1&funcflag=1&short_spec=1">965745</A> 201602 201602 2016023 V64T 8 NEW CUSTOMER 6*4 RAL17 SRED-M RTS1857C RACWD-ST BLMD-M8 UDBRDUST RBRA175 RWBRT-S2 ABS4S4M RAA22 RADD-A4V FATYPM-V FATYPE55 FRAX-BAS FWBRT-S FBRA125 BLMF-M11 UFBRDUST <a target=_blank href="http://**************/_identity=965745&find_flag=on&time_from=1">Restr</a> VTNA <A target=_blank HREF="http://**************/vehspec.cgi?func=1&signature=VTNA&identity=965801&funcflag=1&funcflag=1&short_spec=1">965801</A> 201602 201602 2016023 V64T 4 NEW CUSTOMER 6*4 RAL17 SRED-M RTS1857C RACWD-ST BLMD-M8 UDBRDUST RBRA175 RWBRT-S2 ABS6S4M RAA22 RADD-A4V FATYPM-V FATYPE57 FRAX-UNI FWBRT-S FBRA125 BLMF-M11 UFBRDUST <a target=_blank href="http://**************/_identity=965801&find_flag=on&time_from=1">Restr</a> VTNA <A target=_blank HREF="http://**************/vehspec.cgi?func=1&signature=VTNA&identity=967849&funcflag=1&funcflag=1&short_spec=1">967849</A> 201602 201602 2016023 V64T 1 NEW CUSTOMER 6*4 RAL17 SRED-M RTS1857C RACWD-ST BLMD-E13 UDBRDUST RBRA175 RWBRT-S2 ABS6S4M RAA22 RADD-A4V FATYPM-H FATYPE57 FRAX-BAS FWBRT-S FBRA125 BLMF-E14 UFBRDUST <a target=_blank href="http://**************/_identity=967849&find_flag=on&time_from=1">Restr</a> VTNA <A target=_blank HREF="http://**************/vehspec.cgi?func=1&signature=VTNA&identity=967854&funcflag=1&funcflag=1&short_spec=1">967854</A> 201602 201602 2016022 V64T 2 NEW CUSTOMER 6*4 RAL17 SRED-M RTS1857C RACWD-ST BLMD-M8 DBRDUST RBRA175 RWBRT-S2 ABS6S4M RAA22 RADD-A4V FATYPM-V FATYPE60 FRAX-UNI FWBRT-S FBRA125 BLMF-M11 FBRDUST <a target=_blank href="http://**************/_identity=967854&find_flag=on&time_from=1">Restr</a> VTNA <A target=_blank HREF="http://**************/vehspec.cgi?func=1&signature=VTNA&identity=967858&funcflag=1&funcflag=1&short_spec=1">967858</A> 201602 201602 2016022 V64T 2 NEW CUSTOMER 6*4 RAL17 SRED-M RTS1857C RACWD-ST BLMD-M8 DBRDUST RBRA175 RWBRT-S2 ABS6S4M RAA22 RADD-A4V FATYPM-R FATYPE60 FRAX-BAS FWBRT-S FBRA125 BLMF-M11 UFBRDUST <a target=_blank href="http://**************/_identity=967858&find_flag=on&time_from=1">Restr</a> VTNA <A target=_blank HREF="http://**************/vehspec.cgi?func=1&signature=VTNA&identity=967875&funcflag=1&funcflag=1&short_spec=1">967875</A> 201602 201602 2016032 V64T 1 NEW CUSTOMER 6*4 RAL18 SRED-M RTS1857C RACWD-ST BLMD-E1 UDBRDUST RBRA175 RWBRT-S2 ABS6S6M RAA22 RADD-A4V FATYPM-H FATYPE60 FRAX-BAS FWBRT-D FBRA-D43 BLMF-B UFBRDUST <a target=_blank href="http://**************/_identity=967875&find_flag=on&time_from=1">Restr</a> VTNA <A target=_blank HREF="http://**************/vehspec.cgi?func=1&signature=VTNA&identity=967883&funcflag=1&funcflag=1&short_spec=1">967883</A> 201602 201602 2016034 V64T 1 NEW CUSTOMER 6*4 RAL17 SRED-M RTS1857C RACWD-ST BLMD-E13 DBRDUST RBRA175 RWBRT-S2 ABS6S4M RAA22 RADD-A4V FATYPM-V FATYPE57 FRAX-UNI FWBRT-S FBRA125 BLMF-E14 FBRDUST <a target=_blank href="http://**************/_identity=967883&find_flag=on&time_from=1">Restr</a> VTNA <A target=_blank HREF="http://**************/vehspec.cgi?func=1&signature=VTNA&identity=970569&funcflag=1&funcflag=1&short_spec=1">970569</A> 201602 201602 2016033 V64T 2 NEW CUSTOMER 6*4 RAL17 SRED-M RTS1857C RACWD-ST BLMD-M8 DBRDUST RBRA175 RWBRT-S2 ABS6S6M RAA22 RADD-A4V FATYPM-R FATYPE60 FRAX-BAS FWBRT-S FBRA125 BLMF-M11 UFBRDUST <a target=_blank href="http://**************/_identity=970569&find_flag=on&time_from=1">Restr</a> VTNA <A target=_blank HREF="http://**************/vehspec.cgi?func=1&signature=VTNA&identity=970583&funcflag=1&funcflag=1&short_spec=1">970583</A> 201602 201602 2016025 V64T 1 NEW CUSTOMER 6*4 RAL17 SRED-M RTS1857C RACWD-ST BLMD-M8 UDBRDUST RBRA175 RWBRT-S2 ABS6S4M RAA22 RADD-A4V FATYPM-R FATYPE57 FRAX-BAS FWBRT-S FBRA125 BLMF-M11 UFBRDUST <a target=_blank href="http://**************/_identity=970583&find_flag=on&time_from=1">Restr</a> VTNA

… …

J'aimerais simplement extraire le tableau dans un classur Excel.
J'ai essayé quelques solutions à partir d'autres publications … exemple: Excel importe datatables de la table java

Cela a été inutile. Mon code est très long en raison de beaucoup d'autres choses se passant avec d'autres forms et telles, cependant, il y a une base: 

 Sub submitForm() '**********Setup Timer************* Dim oPM As PerformanceMonitor Set oPM = New PerformanceMonitor Dim T As Double oPM.StartCounter '********************************** Dim dbsheet As Worksheet Dim x As Long Dim oRow As Object, oCell As Object Dim data Dim i As Long, strText As Ssortingng Dim y As Long, z As Long, wb As Excel.Workbook, ws As Excel.Worksheet Dim rng As Range Dim rowOffset As Long Set wb = Excel.ActiveWorkbook Set ws = Sheets("Sheet1") Set rng = ws.UsedRange Dim xobj As HTMLDivElement Dim ele As Object Dim j As Long Dim htmldoc As MSHTML.IHTMLDocument 'Document object Dim eleColtr As MSHTML.IHTMLElementCollection 'Element collection for tr tags Dim eleColtd As MSHTML.IHTMLElementCollection 'Element collection for td tags Dim eleRow As MSHTML.IHTMLElement 'Row elements Dim eleCol As MSHTML.IHTMLElement 'Column elements Dim ie2URL As Ssortingng 'URL '************************************************* Set dbsheet = ThisWorkbook.Sheets("Sheet1") Application.ScreenUpdating = False Set ie2 = New InternetExplorerMedium 'Set ie2 = WScript.CreateObject("InternetExplorer.application") ie2.Visible = True ie2.Navigate "http://***********************************************.htm" Application.StatusBar = "FILLING IN DATA!" ' Wait while ie2 loading... While ie2.Busy DoEvents Wend ' **************************************************************** ie2.Document.forms(0).Reset ie2.Document.getElementsByName("func")(0).SelectedIndex = 2 ie2.Document.getElementsByName("auto_complete")(0).Checked = True ie2.Document.getElementById("form_submit").Click 'delay 5 '***************************************************************** While ie2.Busy DoEvents Wend Dim TblFound As Boolean Dim iOff As Long ''*********************************************** Set htmldoc = ie2.Document 'Document webpage ' Set eleColtr = htmldoc.getElementsByTagName("a") 'Find all tr tags ''This section populates Excel ' i = 0 'start with first value in tr collection ' ' If rng.Rows.Count = 1 Then ' rowOffset = 1 ' Else ' rowOffset = rng.Rows.Count + 2 ' End If 'MsgBox rowOffset ' ' TblFound = False ' For Each eleRow In eleColtr 'for each element in the tr collection ' Set eleColtd = htmldoc.getElementsByTagName("a")(i) '.getElementsByTagName("td") 'get all the td elements in that specific tr ' j = 0 'start with the first value in the td collection ' For Each eleCol In eleColtd 'for each element in the td collection ' If eleCol.innerText = "VSPEC" Then ' TblFound = True ' iOff = i ' End If ' If TblFound = True Then ' Sheets("Sheet1").Range("A" & rowOffset).offset(i - iOff, j).Value = eleCol.innerText 'paste the inner text of the td element, and offset at the same time ' j = j + 1 'move to next element in td collection ' End If ' Next eleCol 'rinse and repeat ' i = i + 1 'move to next element in td collection ' Next eleRow 'rinse and repeat 'Sheets("Sheet1").Clear 'For Each ele In objIE.Document.getElementById("myTable").getElementsByTagName("tr") '*********************************************** '**************Stop Timer**************** 'MsgBox oPM.TimeElapsed T = oPM.TimeElapsed Set oPM = Nothing '**************************************** End Sub

Je dois cacher le http pour des raisons de security.

En outre, je montre actuellement le bloc vers le bas comme indiqué, car cela ne fonctionne pas pour un tableau. J'ai pu get cela sur une autre feuille de calcul pour une table HTML régulière.

Le HTML possède un lien pour extraire le CSV, mais cela ouvre une window contextuelle pour laquelle je ne veux pas … J'aimerais simplement saisir le tableau et le mettre dans une feuille.

Toute aide serait grandement appréciée.