- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我经常使用下面的网站来跟踪往返的航空里程。最近,该网站在 IE 中停止工作,所以我的代码也是如此。由于我在工作计算机上使用它,我无法下载我在搜索中找到的许多其他解决方案,而且我无法在没有经过漫长的过程来获得网站批准的情况下使用其他网站。有没有一种方法可以在 Chrome 中执行相同的任务而无需任何其他下载?
Dim ele As Object
Dim IE As New InternetExplorer
IE.Visible = True
IE.navigate "http://www.distancefromto.net"
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
'step 1
With IE
.document.getElementsByName("distance")(0).Value = Range("B2").Value
.document.getElementsByName("distance")(1).Value = Range("B3").Value & Range("E3").Value
.document.getElementById("hae").Click
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Application.Wait (Now + TimeValue("0:00:02"))
Dim a As String
a = Trim(.document.getElementById("totaldistancemiles").Value)
Dim aa As Variant
aa = Split(a, " ")
Range("C2").Value = aa(0)
'step 2
.document.getElementsByName("distance")(0).Value = Range("B4").Value & Range("E4").Value
.document.getElementById("hae").Click
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Application.Wait (Now + TimeValue("0:00:02"))
Dim b As String
b = Trim(.document.getElementById("totaldistancemiles").Value)
Dim bb As Variant
bb = Split(b, " ")
Range("C3").Value = bb(0)
'step 3
.document.getElementsByName("distance")(1).Value = Range("B2").Value
.document.getElementById("hae").Click
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Application.Wait (Now + TimeValue("0:00:02"))
Dim c As String
c = Trim(.document.getElementById("totaldistancemiles").Value)
Dim cc As Variant
cc = Split(c, " ")
Range("C4").Value = cc(0)
End With
IE.Quit
任何帮助,即使是明确的“不,这是不可能的”,我们也将不胜感激
谢谢
最佳答案
Chrome:
要使用 Chrome - 不。您需要下载 selenium basic或者使用不同的编程语言,例如 python。
不同站点:
您可以切换到使用不同的网站(请注意,由于网站的原因,您之前的数据可能会有一些细微的差异,但从技术上讲,距离不应该有太大变化!)。我注意到你说这会有问题。冒着听起来像跟踪狂的风险,您使用了 freemaptools之前,所以这可能是一个可以接受的选择?
API:
如果您找到一个提供 API 服务的站点,您或许可以放弃上述所有内容并发出 XMLHTTP request .我看不到您的网站提供 API 服务,否则那将是显而易见的下一个选择。@RahulChalwa 提到“[OP 使用的站点本身使用围绕谷歌地图 API 的包装器:https://maps.googleapis.com/maps/api/js/GeocodeService.Search。用户可以注册 API 并执行 POST 请求]”;所以这可能是前进的方向。主要文档 here .
例如API 站点:个人和小规模使用 API - wheretocredit.com
当前设置调试:
确定 IE 在您当前的设置中不再工作的原因也是可取的,也许可以联系网站开发人员并提出您的问题。
使用 Vincenty's formula 执行计算(如网站所做)或者,像其他网站一样,使用 Haversine 公式:
正弦:
Vicenty 的(包括示例代码):
How to Calculate Distance in Excel
Vicenty 的代码来自 Contextures .我已经归因于但如果这不应该包括在这里我会删除。
'*************************************************************
Private Const PI = 3.14159265358979
Private Const EPSILON As Double = 0.000000000001
Public Function distVincenty(ByVal lat1 As Double, ByVal lon1 As Double, _
ByVal lat2 As Double, ByVal lon2 As Double) As Double
'INPUTS: Latitude and Longitude of initial and
' destination points in decimal format.
'OUTPUT: Distance between the two points in Meters.
'
'======================================
' Calculate geodesic distance (in m) between two points specified by
' latitude/longitude (in numeric [decimal] degrees)
' using Vincenty inverse formula for ellipsoids
'======================================
' Code has been ported by lost_species from www.aliencoffee.co.uk to VBA
' from javascript published at:
' https://www.movable-type.co.uk/scripts/latlong-vincenty.html
' * from: Vincenty inverse formula - T Vincenty, "Direct and Inverse Solutions
' * of Geodesics on the Ellipsoid with application
' * of nested equations", Survey Review, vol XXII no 176, 1975
' * https://www.ngs.noaa.gov/PUBS_LIB/inverse.pdf
'Additional Reference: https://en.wikipedia.org/wiki/Vincenty%27s_formulae
'======================================
' Copyright lost_species 2008 LGPL
' https://www.fsf.org/licensing/licenses/lgpl.html
'======================================
' Code modifications to prevent "Formula Too Complex" errors
' in Excel (2010) VBA implementation
' provided by Jerry Latham, Microsoft MVP Excel Group, 2005-2011
' July 23 2011
'======================================
Dim low_a As Double
Dim low_b As Double
Dim f As Double
Dim L As Double
Dim U1 As Double
Dim U2 As Double
Dim sinU1 As Double
Dim sinU2 As Double
Dim cosU1 As Double
Dim cosU2 As Double
Dim lambda As Double
Dim lambdaP As Double
Dim iterLimit As Integer
Dim sinLambda As Double
Dim cosLambda As Double
Dim sinSigma As Double
Dim cosSigma As Double
Dim sigma As Double
Dim sinAlpha As Double
Dim cosSqAlpha As Double
Dim cos2SigmaM As Double
Dim C As Double
Dim uSq As Double
Dim upper_A As Double
Dim upper_B As Double
Dim deltaSigma As Double
Dim s As Double ' final result, will be returned rounded to 3 decimals (mm).
'added by JLatham to break up "Too Complex" formulas
'into pieces to properly calculate those formulas as noted below
'and to prevent overflow errors when using
'Excel 2010 x64 on Windows 7 x64 systems
Dim P1 As Double ' used to calculate a portion of a complex formula
Dim P2 As Double ' used to calculate a portion of a complex formula
Dim P3 As Double ' used to calculate a portion of a complex formula
'See https://en.wikipedia.org/wiki/World_Geodetic_System
'for information on various Ellipsoid parameters for other standards.
'low_a and low_b in meters
' === GRS-80 ===
' low_a = 6378137
' low_b = 6356752.314245
' f = 1 / 298.257223563
'
' === Airy 1830 === Reported best accuracy for England and Northern Europe.
' low_a = 6377563.396
' low_b = 6356256.910
' f = 1 / 299.3249646
'
' === International 1924 ===
' low_a = 6378388
' low_b = 6356911.946
' f = 1 / 297
'
' === Clarke Model 1880 ===
' low_a = 6378249.145
' low_b = 6356514.86955
' f = 1 / 293.465
'
' === GRS-67 ===
' low_a = 6378160
' low_b = 6356774.719
' f = 1 / 298.247167
'=== WGS-84 Ellipsoid Parameters ===
low_a = 6378137 ' +/- 2m
low_b = 6356752.3142
f = 1 / 298.257223563
'====================================
L = toRad(lon2 - lon1)
U1 = Atn((1 - f) * Tan(toRad(lat1)))
U2 = Atn((1 - f) * Tan(toRad(lat2)))
sinU1 = Sin(U1)
cosU1 = Cos(U1)
sinU2 = Sin(U2)
cosU2 = Cos(U2)
lambda = L
lambdaP = 2 * PI
iterLimit = 100 ' can be set as low as 20 if desired.
While (Abs(lambda - lambdaP) > EPSILON) And (iterLimit > 0)
iterLimit = iterLimit - 1
sinLambda = Sin(lambda)
cosLambda = Cos(lambda)
sinSigma = Sqr(((cosU2 * sinLambda) ^ 2) + _
((cosU1 * sinU2 - sinU1 * cosU2 * cosLambda) ^ 2))
If sinSigma = 0 Then
distVincenty = 0 'co-incident points
Exit Function
End If
cosSigma = sinU1 * sinU2 + cosU1 * cosU2 * cosLambda
sigma = Atan2(cosSigma, sinSigma)
sinAlpha = cosU1 * cosU2 * sinLambda / sinSigma
cosSqAlpha = 1 - sinAlpha * sinAlpha
If cosSqAlpha = 0 Then 'check for a divide by zero
cos2SigmaM = 0 '2 points on the equator
Else
cos2SigmaM = cosSigma - 2 * sinU1 * sinU2 / cosSqAlpha
End If
C = f / 16 * cosSqAlpha * (4 + f * (4 - 3 * cosSqAlpha))
lambdaP = lambda
'the original calculation is "Too Complex" for Excel VBA to deal with
'so it is broken into segments to calculate without that issue
'the original implementation to calculate lambda
'lambda = L + (1 - C) * f * sinAlpha * _
(sigma + C * sinSigma * (cos2SigmaM + C * cosSigma * _
(-1 + 2 * (cos2SigmaM ^ 2))))
'calculate portions
P1 = -1 + 2 * (cos2SigmaM ^ 2)
P2 = (sigma + C * sinSigma * (cos2SigmaM + C * cosSigma * P1))
'complete the calculation
lambda = L + (1 - C) * f * sinAlpha * P2
Wend
If iterLimit < 1 Then
MsgBox "iteration limit has been reached, something didn't work."
Exit Function
End If
uSq = cosSqAlpha * (low_a ^ 2 - low_b ^ 2) / (low_b ^ 2)
'the original calculation is "Too Complex" for Excel VBA to deal with
'so it is broken into segments to calculate without that issue
'the original implementation to calculate upper_A
'upper_A = 1 + uSq / 16384 * (4096 + uSq * _
(-768 + uSq * (320 - 175 * uSq)))
'calculate one piece of the equation
P1 = (4096 + uSq * (-768 + uSq * (320 - 175 * uSq)))
'complete the calculation
upper_A = 1 + uSq / 16384 * P1
'oddly enough, upper_B calculates without any issues - JLatham
upper_B = uSq / 1024 * (256 + uSq * (-128 + uSq * (74 - 47 * uSq)))
'the original calculation is "Too Complex" for Excel VBA to deal with
'so it is broken into segments to calculate without that issue
'the original implementation to calculate deltaSigma
'deltaSigma = upper_B * sinSigma * (cos2SigmaM + upper_B / 4 * _
(cosSigma * (-1 + 2 * cos2SigmaM ^ 2) _
- upper_B / 6 * cos2SigmaM * (-3 + 4 * sinSigma ^ 2) * _
(-3 + 4 * cos2SigmaM ^ 2)))
'calculate pieces of the deltaSigma formula
'broken into 3 pieces to prevent overflow error that may occur in
'Excel 2010 64-bit version.
P1 = (-3 + 4 * sinSigma ^ 2) * (-3 + 4 * cos2SigmaM ^ 2)
P2 = upper_B * sinSigma
P3 = (cos2SigmaM + upper_B / 4 * (cosSigma * (-1 + 2 * cos2SigmaM ^ 2) _
- upper_B / 6 * cos2SigmaM * P1))
'complete the deltaSigma calculation
deltaSigma = P2 * P3
'calculate the distance
s = low_b * upper_A * (sigma - deltaSigma)
'round distance to millimeters
distVincenty = Round(s, 3)
End Function
Function SignIt(Degree_Dec As String) As Double
'Input: a string representation of a lat or long in the
' format of 10° 27' 36" S/N or 10~ 27' 36" E/W
'OUTPUT: signed decimal value ready to convert to radians
'
Dim decimalValue As Double
Dim tempString As String
tempString = UCase(Trim(Degree_Dec))
decimalValue = Convert_Decimal(tempString)
If Right(tempString, 1) = "S" Or Right(tempString, 1) = "W" Then
decimalValue = decimalValue * -1
End If
SignIt = decimalValue
End Function
Function Convert_Degree(Decimal_Deg) As Variant
'source: https://support.microsoft.com/kb/213449
'
'converts a decimal degree representation to deg min sec
'as 10.46 returns 10° 27' 36"
'
Dim degrees As Variant
Dim minutes As Variant
Dim seconds As Variant
With Application
'Set degree to Integer of Argument Passed
degrees = Int(Decimal_Deg)
'Set minutes to 60 times the number to the right
'of the decimal for the variable Decimal_Deg
minutes = (Decimal_Deg - degrees) * 60
'Set seconds to 60 times the number to the right of the
'decimal for the variable Minute
seconds = Format(((minutes - Int(minutes)) * 60), "0")
'Returns the Result of degree conversion
'(for example, 10.46 = 10° 27' 36")
Convert_Degree = " " & degrees & "° " & Int(minutes) & "' " _
& seconds + Chr(34)
End With
End Function
Function Convert_Decimal(Degree_Deg As String) As Double
'source: https://support.microsoft.com/kb/213449
' Declare the variables to be double precision floating-point.
' Converts text angular entry to decimal equivalent, as:
' 10° 27' 36" returns 10.46
' alternative to ° is permitted: Use ~ instead, as:
' 10~ 27' 36" also returns 10.46
Dim degrees As Double
Dim minutes As Double
Dim seconds As Double
'
'modification by JLatham
'allow the user to use the ~ symbol instead of ° to denote degrees
'since ~ is available from the keyboard and ° has to be entered
'through [Alt] [0] [1] [7] [6] on the number pad.
Degree_Deg = Replace(Degree_Deg, "~", "°")
' Set degree to value before "°" of Argument Passed.
degrees = Val(Left(Degree_Deg, InStr(1, Degree_Deg, "°") - 1))
' Set minutes to the value between the "°" and the "'"
' of the text string for the variable Degree_Deg divided by
' 60. The Val function converts the text string to a number.
minutes = Val(Mid(Degree_Deg, InStr(1, Degree_Deg, "°") + 2, _
InStr(1, Degree_Deg, "'") - InStr(1, Degree_Deg, "°") - 2)) / 60
' Set seconds to the number to the right of "'" that is
' converted to a value and then divided by 3600.
seconds = Val(Mid(Degree_Deg, InStr(1, Degree_Deg, "'") + _
2, Len(Degree_Deg) - InStr(1, Degree_Deg, "'") - 2)) / 3600
Convert_Decimal = degrees + minutes + seconds
End Function
Private Function toRad(ByVal degrees As Double) As Double
toRad = degrees * (PI / 180)
End Function
Private Function Atan2(ByVal X As Double, ByVal Y As Double) As Double
' code nicked from:
' https://en.wikibooks.org/wiki/Programming:Visual_Basic_Classic
' /Simple_Arithmetic#Trigonometrical_Functions
' If you re-use this watch out: the x and y have been reversed from typical use.
If Y > 0 Then
If X >= Y Then
Atan2 = Atn(Y / X)
ElseIf X <= -Y Then
Atan2 = Atn(Y / X) + PI
Else
Atan2 = PI / 2 - Atn(X / Y)
End If
Else
If X >= -Y Then
Atan2 = Atn(Y / X)
ElseIf X <= Y Then
Atan2 = Atn(Y / X) - PI
Else
Atan2 = -Atn(X / Y) - PI / 2
End If
End If
End Function
'======================================
关于vba - 使用 VBA 打开 Chrome 并填写表格,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/51887401/
我有一个 VBA 脚本,可以将数据从一张表复制到另一张表。复制的数据被放入公式中,计算出的数量被复制回原始工作表。我正在尝试获取它,以便 VBA 脚本为每一行执行此操作。我有 1000 行数据。 Su
如何让 excel 在我的“临时”表上列出所有可用的环境变量?下面的代码没有为我返回任何东西...... Sub ListEnvironVariables() Dim strEnviron A
好的,这就是我想要完成的事情:我正在尝试将所有 VBA 代码从“Sheet2”复制到“Sheet 3”代码 Pane 。我不是指将模块从一个模块复制到另一个模块,而是指 Excel 工作表对象代码。
我正在做一个项目来使用 rule-triggered 处理一些传入的 Outlook 邮件。 VBA 代码。 但是,我不想在代码需要更改的任何时候手动更新每个用户收件箱的代码。所以我的想法是把一个文本
我想从另一个代码 VBA 中评论包含 Msg Box 的行。我正在尝试使用 Library VBA EXTENSIBILITY,但我没有找到解决方案。 欢迎任何帮助。 这是我的代码: Sub Comm
我正在尝试编写程序的最后一部分,我需要从 Access 文档中提取数据并将其打印到新的工作簿中。 首先,我将获取产品供应商的名称并创建一个包含每个供应商名称的工作表,然后我想遍历每个工作表并打印每个供
我有一个要求,我试图查找数据中的日期是否大于或等于当前日期,那么它应该显示"is"。 这是我的代码, RDate = Application.WorksheetFunction.if(RSDate>=
我试图想出一个宏来检查单元格中是否存在任何数字值。如果存在数字值,请复制该行的一部分并将其粘贴到同一电子表格内的另一个工作表中。 Sheet1 是包含我所有数据的工作表。我正在尝试查看 R 列中是否有
我有一个具有密码保护(防止未经授权访问宏)的 VBA 宏,它按预期运行。用户单击按钮,宏运行。内容大致如下: Sub sample() ActiveSheet.Unprotect Pass
我想通过VBA删除工作表中包含的VBA代码。目前,我有一个代码可以将工作表复制到新工作簿并从中删除所有图像。但是,这些图像被设置为在代码中的 Worksheet_Activate 上执行操作,每当我轻
我有一个 vba 代码,它指定要查看的特定工作表名称,例如工作表 2, 但是,如果有人忘记将工作表名称更改为sheet2,我可以添加一段动态代码来自动更改调用工作表名称的vba代码吗?例如,从左边算起
VBAExcel 2016 如果执行某些代码后该范围的列数较少,我将尝试动态调整该范围的大小。引用了 MS 文件和各种在线示例,但没有成功。 https://msdn.microsoft.com/en
我在任何地方都找不到这个问题。在 Visual Basic (excel) 中,我可以按 F8 并循环浏览每一行。但是假设我想开始子程序,然后在执行前两行之后,我想跳到第 200 行。到目前为止,我一
这是我昨天的问题的补充,所以我开始一个新问题。基本上,我在 excel 的工作表上得到不同范围的数据,并且数据范围每周都不同,因此最后使用的列和最后使用的行会有所不同。 我想根据名称合并第 3 行和第
我的想法是创建一个函数来传递这样的双数组: Function pass(a() As Double, b() as double) As Boolean Dim i As Integer, j As
我正在使用 vlookup 运行 VBA 代码,但是,它需要几秒钟才能完成,尽管具有行的工作表只有不到 150 行。 滞后主要出现在 col 23 的生成期间。 包含此代码的主工作表有大约 2300
我在 VBA 中有一个小问题,我想将 Range 函数的行和列以 String 格式放置,如下所示: debut = "BH" & LTrim(Str(i)) fin = "DB" &
我正在尝试使用 Visual Basic 编写 Webcrawler。我有一个包含链接的列表,存储在 Excel 中(第 1 列)。然后宏应打开每个链接并将网站中的某些信息添加到 excel 文件中。
我正在尝试自动生成报告(请原谅我缺乏 Excel 经验),但遇到了这个错误。在单元格中显示#NAME。代码应为工作簿另一页上的所有列 E 选择单元格和 COUNTIF <1。这是一个简单的语法错误吗?
我正在使用“Sheet1”上的命令按钮使用 VBA 创建图表,但是该图表正在添加到另一个工作表(“Sheet2”)。 添加图表后,我使用以下代码根据 DataLabel 值对条形图进行着色并更改 Da
我是一名优秀的程序员,十分优秀!