Forums
New posts
Articles
Product Reviews
Policies
FAQ
Log in
Register
What's new
Search
Search
Search titles only
By:
New posts
Menu
Log in
Register
Install the app
Install
Forums
Apple Computing Products:
macOS - Apps and Programs
script problem excel
JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser
.
Reply to thread
Message
<blockquote data-quote="f.bongers" data-source="post: 1265649" data-attributes="member: 207858"><p>I have an excelsheet with the foolowing script but it doesn't work on a mac.</p><p>Option Explicit</p><p></p><p>Private Sub CommandButton1_Click()</p><p></p><p>Dim clngvbNullString As Long</p><p>Dim avntJersey As Variant</p><p>Dim avntStanding As Variant</p><p>Dim avntStage As Variant</p><p>Dim iavntJersey As Long</p><p>Dim iavntStanding As Long</p><p>Dim iavntStage As Long</p><p>Dim lngCol As Long</p><p>Dim lngRow As Long</p><p>Dim objHTML1 As Object</p><p>Dim objHTML2 As Object</p><p>Dim objIE As Object</p><p>Dim objWorksheet As Object</p><p>Dim strStage As String</p><p></p><p>With Application</p><p> .Calculation = xlCalculationManual</p><p> .DisplayAlerts = False</p><p> .ScreenUpdating = False</p><p>End With</p><p></p><p>avntStanding = Array("G", "Algemeen", "E", "Etappe") 'id's in html-broncode en tab-namen</p><p>avntJersey = Array("IT", "Individueel", "IP", "Punten", "ET", "Ploegen", "IM", "Berg", "IJ", "Jongeren") 'id's in html-broncode en tab-namen</p><p></p><p>For Each objWorksheet In Worksheets 'wis alle bladen behalve "Invoer"</p><p> If objWorksheet.Name <> "Invoer" Then</p><p> objWorksheet.Delete</p><p> End If</p><p>Next</p><p></p><p>Set objIE = CreateObject("InternetExplorer.Application") 'maak verbinding met internet explorer</p><p>objIE.Navigate "http://www.letour.fr/" & CStr(Range("C2").Value) & "/TDF/LIVE/us/" & CStr(100 * Range("C3").Value) & "/classement/index.html" 'maak verbinding met website</p><p>objIE.Visible = True 'maak internet explorer zichtbaar</p><p>Do While objIE.Busy Or objIE.readyState <> 4 'wacht tot internet explorer klaar is</p><p> DoEvents</p><p>Loop</p><p></p><p>Set objHTML1 = objIE.document 'lees document</p><p>For iavntStanding = 0 To UBound(avntStanding) Step 2 'doorloop rangschikkingen</p><p> objHTML1.getElementbyid(avntStanding(iavntStanding)).Click 'klik rangschikking</p><p> Application.Wait (Now + TimeValue("0:00:0" & Range("C4").Value)) 'wacht</p><p> Set objHTML2 = objHTML1.getElementbyid("detailDiv").document 'lees document</p><p> For iavntJersey = 0 To UBound(avntJersey) Step 2 'doorloop trui</p><p> objHTML2.getElementbyid(avntJersey(iavntJersey)).Click 'klik trui</p><p> Application.Wait (Now + TimeValue("0:00:0" & Range("C4").Value)) 'wacht</p><p> Do</p><p> strStage = objHTML1.getElementbyid("contentDetailDyn").innertext 'lees text in html-broncode</p><p> Loop Until strStage <> vbNullString And strStage <> "Loading..." 'niet leeg en niet "Loading"</p><p> avntStage = Split(strStage, vbCrLf) 'opdelen</p><p> With Worksheets.Add(, Worksheets(Worksheets.Count)) 'voeg tabblad toe</p><p> .Name = avntStanding(iavntStanding + 1) & "_" & avntJersey(iavntJersey + 1) 'stel naam van tabblad in</p><p> lngRow = 1 'rij am_2011</p><p> lngCol = 0 'kolom</p><p> clngvbNullString = 0 'aantal lege regels na elkaar</p><p> For iavntStage = 0 To UBound(avntStage) 'doorloop text</p><p> Select Case avntStage(iavntStage)</p><p> Case Is = "<div class='errormess'><activez_javascript:></div>" 'einde (sub)tabel</p><p> lngRow = lngRow + 1 'volgende rij</p><p> lngCol = 0 'kolom</p><p> Case Is <> vbNullString</p><p> lngCol = lngCol + 1 'volgende kolom</p><p> .Cells(lngRow, lngCol).Value = avntStage(iavntStage) 'schrijf data</p><p> clngvbNullString = 0 'aantal lege regels na elkaar</p><p> Case Is = vbNullString</p><p> clngvbNullString = clngvbNullString + 1 'aantal lege regels na elkaar</p><p> End Select</p><p> If clngvbNullString = 3 Then 'wanneer het aantal lege regels na elkaar 3 is, is de rij kompleet</p><p> lngRow = lngRow + 1 'volgende rij</p><p> lngCol = 0 'kolom</p><p> clngvbNullString = 0 'aantal lege regels na elkaar</p><p> End If</p><p> Next</p><p> .Rows(1).Delete 'verwijder eerste rij (individual points team climber youth)</p><p> .Columns("A:F").AutoFit 'automatisch kolombreedte</p><p> End With</p><p> Next</p><p>Next</p><p></p><p>objIE.Quit</p><p></p><p>Set objWorksheet = Nothing</p><p>Set objHTML2 = Nothing</p><p>Set objHTML1 = Nothing</p><p>Set objIE = Nothing</p><p></p><p>With Application</p><p> .Calculation = xlCalculationAutomatic</p><p> .DisplayAlerts = True</p><p> .ScreenUpdating = True</p><p>End With</p><p></p><p>End Sub</p><p></p><p>Can anyone tell me what is wrong with this script so that i can fix it.</p></blockquote><p></p>
[QUOTE="f.bongers, post: 1265649, member: 207858"] I have an excelsheet with the foolowing script but it doesn't work on a mac. Option Explicit Private Sub CommandButton1_Click() Dim clngvbNullString As Long Dim avntJersey As Variant Dim avntStanding As Variant Dim avntStage As Variant Dim iavntJersey As Long Dim iavntStanding As Long Dim iavntStage As Long Dim lngCol As Long Dim lngRow As Long Dim objHTML1 As Object Dim objHTML2 As Object Dim objIE As Object Dim objWorksheet As Object Dim strStage As String With Application .Calculation = xlCalculationManual .DisplayAlerts = False .ScreenUpdating = False End With avntStanding = Array("G", "Algemeen", "E", "Etappe") 'id's in html-broncode en tab-namen avntJersey = Array("IT", "Individueel", "IP", "Punten", "ET", "Ploegen", "IM", "Berg", "IJ", "Jongeren") 'id's in html-broncode en tab-namen For Each objWorksheet In Worksheets 'wis alle bladen behalve "Invoer" If objWorksheet.Name <> "Invoer" Then objWorksheet.Delete End If Next Set objIE = CreateObject("InternetExplorer.Application") 'maak verbinding met internet explorer objIE.Navigate "http://www.letour.fr/" & CStr(Range("C2").Value) & "/TDF/LIVE/us/" & CStr(100 * Range("C3").Value) & "/classement/index.html" 'maak verbinding met website objIE.Visible = True 'maak internet explorer zichtbaar Do While objIE.Busy Or objIE.readyState <> 4 'wacht tot internet explorer klaar is DoEvents Loop Set objHTML1 = objIE.document 'lees document For iavntStanding = 0 To UBound(avntStanding) Step 2 'doorloop rangschikkingen objHTML1.getElementbyid(avntStanding(iavntStanding)).Click 'klik rangschikking Application.Wait (Now + TimeValue("0:00:0" & Range("C4").Value)) 'wacht Set objHTML2 = objHTML1.getElementbyid("detailDiv").document 'lees document For iavntJersey = 0 To UBound(avntJersey) Step 2 'doorloop trui objHTML2.getElementbyid(avntJersey(iavntJersey)).Click 'klik trui Application.Wait (Now + TimeValue("0:00:0" & Range("C4").Value)) 'wacht Do strStage = objHTML1.getElementbyid("contentDetailDyn").innertext 'lees text in html-broncode Loop Until strStage <> vbNullString And strStage <> "Loading..." 'niet leeg en niet "Loading" avntStage = Split(strStage, vbCrLf) 'opdelen With Worksheets.Add(, Worksheets(Worksheets.Count)) 'voeg tabblad toe .Name = avntStanding(iavntStanding + 1) & "_" & avntJersey(iavntJersey + 1) 'stel naam van tabblad in lngRow = 1 'rij am_2011 lngCol = 0 'kolom clngvbNullString = 0 'aantal lege regels na elkaar For iavntStage = 0 To UBound(avntStage) 'doorloop text Select Case avntStage(iavntStage) Case Is = "<div class='errormess'><activez_javascript:></div>" 'einde (sub)tabel lngRow = lngRow + 1 'volgende rij lngCol = 0 'kolom Case Is <> vbNullString lngCol = lngCol + 1 'volgende kolom .Cells(lngRow, lngCol).Value = avntStage(iavntStage) 'schrijf data clngvbNullString = 0 'aantal lege regels na elkaar Case Is = vbNullString clngvbNullString = clngvbNullString + 1 'aantal lege regels na elkaar End Select If clngvbNullString = 3 Then 'wanneer het aantal lege regels na elkaar 3 is, is de rij kompleet lngRow = lngRow + 1 'volgende rij lngCol = 0 'kolom clngvbNullString = 0 'aantal lege regels na elkaar End If Next .Rows(1).Delete 'verwijder eerste rij (individual points team climber youth) .Columns("A:F").AutoFit 'automatisch kolombreedte End With Next Next objIE.Quit Set objWorksheet = Nothing Set objHTML2 = Nothing Set objHTML1 = Nothing Set objIE = Nothing With Application .Calculation = xlCalculationAutomatic .DisplayAlerts = True .ScreenUpdating = True End With End Sub Can anyone tell me what is wrong with this script so that i can fix it. [/QUOTE]
Verification
Post reply
Forums
Apple Computing Products:
macOS - Apps and Programs
script problem excel
Top