从ExcelVBA脚本向Web表单发布大量数据-PostingalargeamountofdatatoawebformfromExcelVBAscript

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")

to

至

SetTxt1,Counter1,SetTxt2,CellVSales,SetTxt3

and the second

第二个

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

to

至

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 个解决方案

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

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