从Excel VBA脚本向Web表单发布大量数据

I'm trying to post a large volume of text into specific fields on a tax submission form. In all, there are over 1000 fields. The code I have works fine when testing a handful of entries. But there are a couple thousand lines and when when I try to post all fields, Excel gives me an error stating "Procedure too large". Now I am trying to clean up the code using variables.

我正在尝试将大量文本发布到纳税提交表单上的特定字段中。总共有1000多个领域。在测试一些条目时，我的代码工作得很好。但是有几千行，当我尝试发布所有字段时，Excel给出了一个错误，指出“过程太大”。现在我试图使用变量清理代码。

Here is the original code:

这是原始代码：

Sub Fill_Form()

Set IE = CreateObject("InternetExplorer.Application")
With IE
    .Visible = True
    .navigate URL:="https://ritx-fl-cst2.bswa.net/(S(2kgryl5f1znhcqtjkb3tlvpb))/Schedule1.aspx"
    Do Until .readystate = 4
        DoEvents
    Loop

    Set txtSales1 = .document.all.Item("outerRep__ctl0_txtStateSales")
    txtSales1.Value = Sheets("cst").Range("e2").Value
    ...Code shortened for brevity but there are 548 of these in total...
    Set txtSales548 = 
    .document.all.Item("outerRep__ctl66_innerRep__ctl5_txtLocalSales")
    txtSales548.Value = Sheets("cst").Range("e549").Value


    End With
    End Sub

Since the project uses an Excel sheet, I want to post the values of certain cells into the tax website. Specifically, E2 through E548, which are revenue fields and G2 through G548, which are tax collected fields. Each of these rows is associated to a taxing jurisdiction and I have collected their associated form ID in columns D and F 2 through 548.

由于项目使用Excel工作表，我想将某些单元格的值发布到税务网站。具体来说，E2到E548，这是收入领域，G2到G548，这是税收领域。这些行中的每一行都与一个征税管辖区相关联，我已经在D和F 2到548列中收集了它们的相关表格ID。

The code that I am trying to write 1. Looks up the form ID in column D and posts the related value from column E 2. Looks up the form ID in column F and posts the related value from column G

我正在尝试编写的代码1.在D列中查找表单ID并在E列中发布相关值。在F列中查找表单ID并从G列发布相关值

I think that variables for each portion of the lookup could be automated. For example.

我认为查找的每个部分的变量都可以自动化。例如。

Set txtSales1 = .document.all.Item("outerRep__ct10_txtStateSales")

could be:

可能：

Dim SetTxt1 As String
Dim Counter1 As Integer
Dim SetTxt2 As String
Dim CellVSales As String
Dim SetTxt3 As String

SetTxt1 = "Set txtSales "
Counter1 = Counter1 + 1
SetTxt2 = "= .document.all.Item("""
CellVSales = E1 + 1
SetTxt3 = """)"

For the second line, txtSales1.Value = Sheets("cst").Range("e2").Value, it would have these settings:

对于第二行，txtSales1.Value = Sheets（“cst”）。Range（“e2”）。Value，它将具有以下设置：

Dim SetTxt4 As String
Dim Counter2 As Integer
Dim SetTxt5 As String
Dim CellVTax As String
Dim SetTxt6 As String

SetTxt4 = "txtSales"
Counter2 = Counter2 + 1
SetTxt5 = ".Value = Sheets(""cst"").Range("""
CellVTax = G1 + 1
SetTxt6 = """).Value"

This is where I am completely lost and have no idea where to start. How do I combine the strings and also increment the cell value (e.g. E1 to E2 to E3, and so on)?

这是我完全迷失的地方，不知道从哪里开始。如何组合字符串并增加单元格值（例如E1到E2到E3，依此类推）？

I envision the fist string would change from

我设想第一个字符串会改变

Set txtSales1 = .document.all.Item("outerRep__ct10_txtStateSales")

至

SetTxt1,Counter1,SetTxt2,CellVSales,SetTxt3

and the second

第二个

txtSales1.Value = Sheets("cst").Range("e2").Value

至

SetTxt4,Counter2,SetTxt5,CellVTax,SetTxt6

Please tell me if I am way off base here. I would absolutely appreciate any advise or assistance on how to accomplish this script. Thank you so very much!

请告诉我，如果我离这里不远。我非常感谢有关如何完成此脚本的任何建议或帮助。非常感谢你！

2 个解决方案

#1

'Procedure too large' means your code is too big for the VBA compiler to handle (the number here isn't that great). The only way to let the procedure run is to shorten it.

“过程太大”意味着您的代码太大而无法让VBA编译器处理（这里的数字不是那么好）。让程序运行的唯一方法是缩短它。

The code you pasted above seems to be painfully repeatable. First thing to do in my opinion would be to declare ranges you are working for and use a For Each loop.

你上面粘贴的代码似乎很难重复。在我看来，首先要做的是声明你正在使用的范围并使用For Each循环。

If I understood right, your range goes row by row from E2 to E584. You can simply set it up as Rng variable by using:

如果我理解正确，你的范围将从E2逐行到E584。您可以使用以下命令将其设置为Rng变量：

Dim Rng as Range
Set Rng = Range("E2:E548")

Using .document.all.Item can be replaced by .document.getElementByID which usually works better to my knowledge.

使用.document.all.Item可以替换为.document.getElementByID，这通常对我的知识更有效。

For Each loop is explained here: http://www.homeandlearn.org/for_each.html

For Each循环在这里解释：http：//www.homeandlearn.org/for_each.html

Finally, if you are working with the data from cells that are always situated in the same pattern for each of the cells, you could just use offset to get to them.

最后，如果您正在处理来自每个单元格始终位于相同模式的单元格中的数据，则可以使用offset来获取它们。

So, the for each loop within Rng would be setting up txtSales dynamically by taking the string from offsetting cell position and then just changing the value of the cell adapting the txtSales value.

因此，对于Rng中的每个循环，将通过从偏移单元格位置获取字符串，然后仅更改适应txtSales值的单元格的值来动态设置txtSales。

#2

as per my earlier comment ...

根据我之前的评论......

try this ... you will have to edit your code

试试这个...你必须编辑你的代码

Option Explicit

Sub Fill_Form()

    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .navigate URL:="https://ritx-fl-cst2.bswa.net/(S(2kgryl5f1znhcqtjkb3tlvpb))/Schedule1.aspx"
        Do Until .readystate = 4
            DoEvents
        Loop

        With .document.all
            .Item("outerRep__ctl0_txtStateSales").Value = Sheets("cst").Range("e2").Value

                    '    ...Code shortened for brevity but there are 548 of these in total...

            .Item("outerRep__ctl66_innerRep__ctl5_txtLocalSales").Value = Sheets("cst").Range("e549").Value
        End With
    End With
End Sub

#1