与 FireFox 浏览器交互的 VBA 脚本

VBA script to interact with FireFox browser

提问by Limak

I am trying to create VBA tool, that copy-paste some data from excel workbook to some internal company application, which we are using via browser. The program should "click" several buttons and checkboxes, and paste cells values into textboxes like this presented on following screens:

我正在尝试创建 VBA 工具，将一些数据从 excel 工作簿复制粘贴到我们通过浏览器使用的一些内部公司应用程序。该程序应该“单击”几个按钮和复选框，并将单元格值粘贴到如下屏幕上显示的文本框中：

Unfortunately, there are several restrictions:

不幸的是，有几个限制：

• This site is not working on MS Internet Explorer. I can use it only by FireFox.
• Thanks to security rules in may company, I am not able to install any software. So, I can't use Selenium (requires installing add-on), nor do this task with R or Python.
• Clicking Tabkey is not working, so I can't just SendKeys "{TAB}"multiple times.

• 此站点不适用于 MS Internet Explorer。我只能通过 FireFox 使用它。
• 由于可能公司的安全规则，我无法安装任何软件。因此，我无法使用 Selenium（需要安装附加组件），也无法使用 R 或 Python 执行此任务。
• 单击Tab键不起作用，所以我不能SendKeys "{TAB}"多次单击。

Have someone got any idea how I can resolve this?

有人知道我如何解决这个问题吗？

采纳答案by Sgdva

Workaround:
As you state the only solution (to my knowledge since Firefox does not have an api) would be clicking and waiting a few -is there any window that may pop up to verify that you are done?-.

解决方法：
正如您所说，唯一的解决方案（据我所知，因为 Firefox 没有 api）是单击并等待几下 - 是否有任何可能弹出的窗口来验证您已完成？-。

---

---

Steps:
1.Set your firefox window in a constant position and then get the coordinates
You would need to declare each coordinate, you may use the following code to help you to calculate the coordinate like so:

步骤： 1.
将您的firefox窗口设置在一个恒定位置，然后获取坐标
您需要声明每个坐标，您可以使用以下代码来帮助您像这样计算坐标：

Dim MyPointAPI As POINTAPI
Private Type POINTAPI
  X As Long
  Y As Long
End Type
Private Declare Function GETCURSORPOS Lib "user32" Alias "GetCursorPos" (lpPoint As POINTAPI) As Long
Private Declare Function SetCursorPos Lib "user32" (ByVal X As Long, ByVal Y As Long) As Long
Public Sub MouseCursorGetOurXY()
'this was taken online quite a while ago, left everything as copied from the original source
    Dim L As Long
Application.Wait (Now() + TimeValue("00:00:02"))
    L = GETCURSORPOS(MyPointAPI) 'get our cursor position first
    MsgBox CStr(MyPointAPI.X) & ", " & CStr(MyPointAPI.Y) 'displays cursor X Y coordinates
End Sub

2.Set your code with these coordinates:

2.使用这些坐标设置您的代码：

 Public Declare Function SetCursorPos Lib "user32" (ByVal X As Long, ByVal Y As Long) As Long
Public Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4
Public Const MOUSEEVENTF_RIGHTDOWN As Long = &H8
Public Const MOUSEEVENTF_RIGHTUP As Long = &H10
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Sub Click_Windows()
    Const CheckBoxCoordX As Long = 455
    Const CheckBoxCoordY As Long = 300
    Const InputBoxCoordX As Long = 155
    Const InputBoxCoordY As Long = 255
    Const FireFoxWindowTitle As String = "WebPage Title"
    Dim do_Mydata As DataObject
    Dim lHnd As Long
    Dim i As Long
    lHnd = FindWindow(vbNullString, FireFoxWindowTitle)
    If lHnd > 0 Then 'This means that your program is in memory ' 1. If lHnd > 0
    'Now that You've already checked your app is running then ..
    AppActivate FireFoxWindowTitle
    Else ' 1. If lHnd > 0
    i = 0
    Do Until lHnd > 0
    lHnd = FindWindow(vbNullString, FireFoxWindowTitle)
    If i < 5 Then ' 2. If i < 5
    Application.Wait (Now() + TimeValue("00:00:01"))
    i = i + 1
    Else ' 2. If i < 5
    MsgBox ("It seems that the webpage is not opened!")
    End
    End If ' 2. If i < 5
    Loop
    End If ' 1. If lHnd > 0
        'code to click only
       SetCursorPos CheckBoxCoordX, CheckBoxCoordY
       mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
       mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
       Application.Wait (Now() + TimeValue("00:00:01"))
       'move to the input box and paste data from excel
       SetCursorPos InputBoxCoordX, InputBoxCoordY
       mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
       mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
        Set do_Mydata = New DataObject
        With do_Mydata
            .SetText Range("A1").Value
            .PutInClipboard
        End With
        SendKeys "^v", True
        Set do_Mydata = Nothing
        Application.CutCopyMode = False

    End Sub