excel - 带有 excel 和 IE 的 VBA 脚本

Question

我正在尝试使用 A 列中的值打开一个 .xlsx 文件(比如说 A1:A1000)。

我需要脚本从 A1 中选择值并将其添加到 URL。然后它需要打开 URL 并更改该特定站点上的设置。然后它应该确认更改并继续下一个值 A2 并执行相同操作直到最后一个值(循环)。

我想出了他以下内容:

Set IE = CreateObject("InternetExplorer.Application")    
 IE.Visible = 1    
 IE.navigate "http://xxxx/edit_product.php?Prod_ID=45306"    
 Do While (IE.Busy)    
     WScript.Sleep 100    
 Loop    
 Set Helem = IE.document.getElementByID("Status")    
 Helem.Value = "The change I need" 
 Do While (IE.Busy)    
     WScript.Sleep 100   
Loop     
Set Shell = WScript.CreateObject("WScript.Shell")
Shell.SendKeys "{TAB}"
Shell.SendKeys "{TAB}"
Shell.SendKeys "{TAB}"
Shell.SendKeys "{TAB}"
Shell.SendKeys "{TAB}"
Shell.SendKeys "{TAB}"
Shell.SendKeys "{TAB}"
Shell.SendKeys "{TAB}"
Shell.SendKeys "{TAB}"
Shell.SendKeys "{TAB}"
Shell.SendKeys "{TAB}"
Shell.SendKeys "{TAB}"
Shell.SendKeys "{TAB}"
Shell.SendKeys "{TAB}"
Shell.SendKeys "{TAB}"
Shell.SendKeys "{TAB}"
Shell.SendKeys "{TAB}"
Shell.SendKeys "{ENTER}"

在此代码中，URL 中的 prod_ID“45306”是来自单元格的值，因此它应该是一个变量值。

网站上的操作有效，但如何打开 Excel 文件并循环操作(并使 URL 变量)？

Answer 1

是否要从另一个工作簿运行此代码，然后打开值所在的另一个工作簿？

尝试这个:

    Dim lr As Long, prodID as string
    Dim wb As Workbook, ws As Worksheet

    Set wb = Workbooks.Open(Filename:="c:\temp\attachment.xlsx") 'Set path to your file
    Set ws = wb.Sheets("Sheet1") 'Set name of your worksheet
    lr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row 'Last row used in column A

        For i = 1 To lr
            prodID = ws.Range("A" & i).Value

            IE.navigate "http://xxxx/edit_product.php?Prod_ID=" & prodID
            Do While (IE.Busy)
                WScript.Sleep 100
            Loop
            Set Helem = IE.document.getElementByID("Status")
            Helem.Value = "The change I need"
            Do While (IE.Busy)
                 WScript.Sleep 100
            Loop
            Set Shell = WScript.CreateObject("WScript.Shell")
            Shell.SendKeys "{TAB}"
            Shell.SendKeys "{TAB}"
            Shell.SendKeys "{TAB}"
            Shell.SendKeys "{TAB}"
            Shell.SendKeys "{TAB}"
            Shell.SendKeys "{TAB}"
            Shell.SendKeys "{TAB}"
            Shell.SendKeys "{TAB}"
            Shell.SendKeys "{TAB}"
            Shell.SendKeys "{TAB}"
            Shell.SendKeys "{TAB}"
            Shell.SendKeys "{TAB}"
            Shell.SendKeys "{TAB}"
            Shell.SendKeys "{TAB}"
            Shell.SendKeys "{TAB}"
            Shell.SendKeys "{TAB}"
            Shell.SendKeys "{TAB}"
            Shell.SendKeys "{ENTER}"
        Next