创建数组时出现自动化错误 -2146232576 (80131700) [英] automation error -2146232576 (80131700) on creating an array
问题描述
我有一个宏在一台计算机上运行得很好,但是当我尝试在另一台计算机上运行它时,我收到以下错误:
I have a macro that's working on one computer just fine, however when I try to run it on another computer I get the following error:
运行时错误'-2146232576 (80131700)'自动化错误
Run-time error '-2146232576 (80131700)' Automation error
代码如下:
Sub Kaivuri()
Dim i As Long
Dim arvoKohta As Integer
Dim etuKohta As Integer
Dim sukuKohta As Integer
Dim yritysKohta As Integer
Dim tulosMaara As Integer
Dim ws As Worksheet
Dim hakuSana As String
Dim arvo As String
Dim etunimiLista As Object
Dim sukunimiLista As Object
Dim riviLista As Object
Dim kaupunkiLista As Object
Dim lisarivit As Integer
Set kaupunkiLista = CreateObject("System.Collections.ArrayList")
Set riviLista = CreateObject("System.Collections.ArrayList")
Set etunimiLista = CreateObject("System.Collections.ArrayList")
Set sukunimiLista = CreateObject("System.Collections.ArrayList")
Set browserIE = CreateObject("InternetExplorer.Application")
browserIE.Top = 0
browserIE.Left = 800
browserIE.Width = 800
browserIE.Height = 1200
browserIE.Visible = True
Set ws = ThisWorkbook.Worksheets("etsintä")
i = 174958
etuKohta = 1
sukuKohta = 2
yritysKohta = 5
arvoKohta = 20
browserIE.navigate ("https://www.linkedin.com/sales/search? facet=G&count=25&start=0&updateHistory=true&searchHistoryId=4444924553")
Do While browserIE.ReadyState <> 4 And browserIE.Busy: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:5"))
browserIE.document.getelementsbyclassname("facet G") (0).getelementsbyclassname("plus-icon-container")(0).getelementsbyclassname("small-icon")(0).Click
browserIE.document.getelementbyid("G-input").Value = "Finland"
'Application.SendKeys ("~ (tilde)")
'Application.Wait (Now + TimeValue("0:00:1"))
r = 0
Do While i <= 175000
If r = 100 Then
ActiveWorkbook.Save
r = 0
End If
lisarivit = 0
kaupunkiLista.Add (Cells(i, 8).Value)
riviLista.Add (i)
etunimiLista.Add (Cells(i, etuKohta).Value)
sukunimiLista.Add (Cells(i, sukuKohta).Value)
Debug.Print i
'Debug.Print Cells(i + 1, yritysKohta).Value
If Cells(i, yritysKohta).Value <> Cells(i + 1, yritysKohta).Value Then
hakuSana = Cells(i, yritysKohta).Value
browserIE.document.getelementsbyclassname("facet CC")(0).getelementsbyclassname("plus-icon-container")(0).Click
browserIE.document.getelementbyid("CC-input").Value = hakuSana
'Application.SendKeys ("~ (tilde)")
'Application.Wait (Now + TimeValue("0:00:1"))
tulosMaara = browserIE.document.getelementsbyclassname("spotlight-result-count")(0).innertext
If tulosMaara <> 0 Then
p = 1
Do While p <= tulosMaara
l = 0
Do While l <= 24 And p <= tulosMaara
nimi = browserIE.document.getelementsbyclassname("result loading member")(l).getelementsbyclassname("name-and-badge-container")(0).getelementsbyclassname("name")(0).innertext
Debug.Print nimi
Debug.Print "t: " & tulosMaara & " p: " & p
If nimi <> "LinkedIn Member" Then
etunimi = Left(nimi, InStr(nimi, " ") - 1)
sukunimi = Right(nimi, (Len(nimi) - InStr(nimi, " ")))
onko = True
h = 0
pituus = sukunimiLista.Count
Do While h < pituus
If etunimi = etunimiLista(h) And sukunimi = sukunimiLista(h) Then
onko = False
Exit Do
End If
h = h + 1
Loop
If onko = True Then
arvo = browserIE.document.getelementsbyclassname("result loading member")(l).getelementsbyclassname("info")(0).getelementsbyclassname("info-value")(0).innertext
Debug.Print arvo
e = 0
Do While browserIE.document.getelementsbyclassname("result loading member")(l).getelementsbyclassname("info")(0).getelementsbyclassname("info-label")(e).innertext <> "Location: "
e = e + 1
Loop
Sijainti = browserIE.document.getelementsbyclassname("result loading member")(l).getelementsbyclassname("info")(0).getelementsbyclassname("info-value")(e).innertext
Debug.Print Sijainti
If InStr(Sijainti, " Area,") <> 0 Then
Sijainti = Left(Sijainti, InStr(Sijainti, " Area,") - 1)
End If
Debug.Print Sijainti
' rivin lisäys ja värjäys
f = riviLista(0)
Rows(f).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
i = i + 1
lisarivit = lisarivit + 1
Cells(f, 8).Value = Sijainti
If Sijainti = "Helsinki" Then
h = 0
Do While h < kaupunkiLista.Count
If kaupunkiLista(h) = "Espoo" Or kaupunkiLista(h) = "ESPOO" Or kaupunkiLista(h) = "Helsinki" Or kaupunkiLista(h) = "HELSINKI" Or kaupunkiLista(h) = "VANTAA" Or kaupunkiLista(h) = "Vantaa" Then
Cells(f, 6).Value = Cells(riviLista(h) + lisarivit, 6).Value
Cells(f, 7).Value = Cells(riviLista(h) + lisarivit, 7).Value
Cells(f, 8).Value = Cells(riviLista(h) + lisarivit, 8).Value
Cells(f, 9).Value = Cells(riviLista(h) + lisarivit, 9).Value
Cells(f, 10).Value = Cells(riviLista(h) + lisarivit, 10).Value
Rows(f).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Exit Do
End If
h = h + 1
Loop
Else
h = 0
sijainti1 = LCase(Sijainti)
Do While h < kaupunkiLista.Count
vanhasijaintitieto = LCase(kaupunkiLista(h))
If sijaint1 = vanhasijaintitieto Then
Cells(f, 6).Value = Cells(riviLista(h) + 1, 6).Value
Cells(f, 7).Value = Cells(riviLista(h) + 1, 7).Value
Cells(f, 8).Value = Cells(riviLista(h) + 1, 8).Value
Cells(f, 9).Value = Cells(riviLista(h) + 1, 9).Value
Cells(f, 10).Value = Cells(riviLista(h) + 1, 10).Value
Rows(f).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Exit Do
End If
h = h + 1
Loop
End If
Debug.Print arvo
If InStr(arvo, " at ") <> 0 Then
arvo = Left(arvo, InStr(arvo, " at ") - 1)
End If
Cells(f, etuKohta).Value = etunimi
Cells(f, sukuKohta).Value = sukunimi
domain = Right(Cells(f + 1, 3).Value, Len(Cells(f + 1, 3).Value) - InStr(Cells(f + 1, 3).Value, "@") + 1)
Cells(f, 3).Value = etunimi & "." & sukunimi & domain
Cells(f, 4).Value = "notBlocked"
Cells(f, 5).Value = Cells(f + 1, 5).Value
Cells(f, 11).Value = Cells(f + 1, 11).Value
Cells(f, 12).Value = Cells(f + 1, 12).Value
Cells(f, 13).Value = Cells(f + 1, 13).Value
Cells(f, 14).Value = Cells(f + 1, 14).Value
Cells(f, 15).Value = Cells(f + 1, 15).Value
Cells(f, 16).Value = Cells(f + 1, 16).Value
Cells(f, 17).Value = Cells(f + 1, 17).Value
Cells(f, 18).Value = Cells(f + 1, 18).Value
Cells(f, 20).Value = arvo
arvo = ""
End If
End If
Debug.Print "lopussa: " & p & " : " & tulosMaara
l = l + 1
p = p + 1
If p = tulosMaara + 1 Then Exit Do
Loop
If p = tulosMaara + 1 Then Exit Do
On Error Resume Next
browserIE.document.getelementsbyclassname("next-pagination page-link")(0).getelementsbyclassname("artdeco-icon")(0).Click
Do While browserIE.ReadyState <> 4 And browserIE.Busy: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:4"))
On Error GoTo 0
Loop
End If
riviLista.Clear
kaupunkiLista.Clear
etunimiLista.Clear
sukunimiLista.Clear
browserIE.document.getelementsbyclassname("facet CC") (0).getelementsbyclassname("dismiss-selection")(0).Click
End If
i = i + 1
Loop
End Sub
错误出现在第 16 行:
The error appears on line 16:
Set kaupunkiLista = CreateObject("System.Collections.ArrayList")
知道为什么会这样吗?
推荐答案
我正在回答一个两年半前的问题,但我花了很长时间才在别处找到答案,这仍然出现在附近谷歌对此问题的最高结果.
I am answering a question that is two and a half years old, but took me way too long to find an answer for this elsewhere, and this still appears near the top of google's results for this issue.
我的解决方案是安装 .NET 3.5,即使我的机器上安装了 .NET 4.0.您特别需要 3.5 版本才能使用 System.Collections.ArrayList
.安装3.5,然后完全关闭excel,再试一次.
My solution was to install .NET 3.5 even though I had .NET 4.0 installed on my machine. You specifically need the 3.5 version to use System.Collections.ArrayList
. Install 3.5 and then close down excel completely and try again.
以下是将库添加到 PC 的方法:
Here is how to add the library to a PC:
- 搜索打开或关闭 Windows 功能".出现时选择该选项.
- 选择/选中 .NET Framework 3.5 的复选框(您不一定需要展开和勾选子项).
- 让 Windows 下载并安装所需的文件.
不要忘记关闭并重新打开 Excel(或您正在使用的任何应用程序).
Don't forget to close and reopen Excel (or whatever application you're using).
这篇关于创建数组时出现自动化错误 -2146232576 (80131700)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!