使用Excel VBA填写并提交Google文档表单 [英] Use Excel VBA to fill out and submit Google Docs form

问题描述

我正在尝试像这篇文章，但是使用Excel VBA。每次在Excel加载项上按下按钮时，我都希望在google文档表单上提交回复。该插件将是一个XLA文件并用VBA编写。

I'm trying to do something like this post but with Excel VBA. I would like to submit a response on a google docs form each time a button is pressed on an Excel add-in. The addin will be an XLA file and written in VBA.

我想能够收集用户正在使用的功能。如果有人有更好的解决方案，我打开。

I want to be able to collect what features the users are using. If someone has a better solution, I'm open.

---编辑---

a href =http://spreadsheets.google.com/viewform?hl=zh-CN&pli=1&formkey=dHFTMzkwR2RpY2tzSUNnbVhIcDN3WWc6MA =nofollow noreferrer>这是我正在尝试写入的表单（摘录其中一个字段的代码。）

This is the form I am trying to write to (excerpt of the code for one of the fields.)

<div class="errorbox-good">
    <div class="ss-item ss-item-required ss-text">
        <div class="ss-form-entry">
            <label for="entry_0" class="ss-q-title">
                UserName
                <span class="ss-required-asterisk">*</span>
            </label>
            <label for="entry_0" class="ss-q-help"></label>
            <input type="text" 
                   id="entry_0" 
                   class="ss-q-short" 
                   value="" 
                   name="entry.0.single">
        </div>
    </div>
</div>

- 编辑2--
这是我迄今为止所尝试的，但它仍然不起作用我在错误的行上显示.UserName.Value = Environ（username）我怀疑是因为它没有找到项目.username。

--EDIT 2-- This is what I've tried so far, but it is still not working. I am getting an error on the line that says ".UserName.Value = Environ("username")" I suspect it is because it is not finding the item .username.

Private Sub GoogleForm()
    Dim ie As Object
    Set ie = CreateObject("InternetExplorer.Application")
    On Error GoTo errHandler
    With ie
        .navigate "http://spreadsheets.google.com/viewform?hl=en&cfg=true&formkey=dHFTMzkwR2RpY2tzSUNnbVhIcDN3WWc6MA"
        Do While .busy: DoEvents:  Loop
            Do While .ReadyState <> 4: DoEvents: Loop
                With .document.Forms(1)
                     'Username
                    .UserName.Value = Environ("username")
                     'Key
                    .Key.Value = "00qwe-12ckd"
                    .submit
                End With
                Do While Not CBool(InStrB(1, .document.URL, _
                    "cp_search_response-e.asp"))
                    DoEvents
                Loop
                Do While .busy: DoEvents: Loop
                Do While .ReadyState <> 4: DoEvents: Loop
                MsgBox .document.all.tags("table").Item(11).Rows(1).Cells(7).innerText
    End With
Exit Sub
errHandler:
    ie.Quit: Set ie = Nothing
End Sub

推荐答案

我可以找到的最好的解决方案是使用sendkeys。我知道这不是理想的，但是在这里没有任何其他反馈，而且我知道的有限的，我最好能想出来。我已经接受了这个答案，而且因为赏金的要求，我无法撤销接受，但如果在这里发表了一个更好的主意，并且我将提出并发表评论，说明这是答案。

The best solution I could find was to use sendkeys. I know it is less than ideal, but without any other feedback here, and with my limited knowledge it is best I could come up with. I have accepted this answer, and because of the bounty request I can't undo the acceptance, but if there is a better idea post here and and I will upvote and leave a comment stating it is the answer.

Sub FillOutGoogleForm()
    Application.ScreenUpdating = False
    Dim IE As Object
    Dim uname       As String
    Dim ukey        As String

    uname = Environ("username")
    ukey = "00000-123kd-34kdkf-slkf"

    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True

    While IE.busy
        DoEvents
    Wend

    IE.navigate "http://spreadsheets.google.com/viewform?hl=en&pli=1&formkey=dHFTMzkwR2RpY2tzSUNnbVhIcDN3WWc6MA"

    While IE.busy
        DoEvents
    Wend

    SendKeys uname
    While IE.busy
        DoEvents
    Wend
    SendKeys "{TAB}", True
    SendKeys ukey
    While IE.busy
        DoEvents
    Wend
    SendKeys "{TAB}", True
    SendKeys "{ENTER}", True
    SendKeys "%{F4}"
    Application.ScreenUpdating = True
End Sub

这篇关于使用Excel VBA填写并提交Google文档表单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！