Excel提示用户输入要在宏中使用的数字 [英] Excel prompt user for number to use in macro

问题描述

我有一个录制的宏，可以从Web服务中提取选择字段，URL类似于 http: //xxxx.com/reportviewer.aspx?ids= 123456789012 我想提示用户输入该数字作为变量，并将该数字传递到宏中使用该数字的2个位置.

I have a recorded macro to pull select fields from a web service, the URL is something like http://xxxx.com/reportviewer.aspx?ids=123456789012 I would like to prompt the user to input that number as a variable and have the number be passed to the 2 locations were that number is used in the macro.

我知道我必须创建另一个宏才能让用户输入一个值，之后我不确定如何将其填充到正确的位置?

I know that i have to create another macro to have the user input a value for after that I'm unsure of how pass that along to be filled in the correct location?

到目前为止，这是我的代码

Here is my code so far

Sub Macro2()
 '
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+r
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://xxxx.com/reportviewer.aspx?ids=123456789012", _
        Destination:=Range("$A$1"))
        .Name = "reportviewer.aspx?ids=123456789012"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "30,33,37,38,46"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub
    Range("A2").Select

推荐答案

您可以使用InputBox来收集用户的输入. 收集之后，您可以在继续执行代码之前测试他们的输入. 这是输入/验证示例:

You can use InputBox to collect the user's input. After collecting, you can test their input before continuing to execute the code. here's the input/validation example:

'-store user input in 'sUserInput' variable
Dim sUserInput As String
sUserInput = InputBox("Enter Number:", "Collect User Input")

'test input before continuing to validate the input
If Not (Len(sUserInput) > 0 And IsNumeric(sUserInput)) Then
    MsgBox "Input not valid, code aborted.", vbCritical
    Exit Sub
End If

您的代码可以通过引用完整版本的sUserInput来引用变量:

Your code can reference the variable by refering to the sUserInput here's a complete version:

Sub Macro2()
 '
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+r
'
    '-store user input in 'sUserInput' variable
    Dim sUserInput As String
    sUserInput = InputBox("Enter Number:", "Collect User Input")

    'test input before continuing to validate the input
    If Not (Len(sUserInput) > 0 And IsNumeric(sUserInput)) Then
        MsgBox "Input not valid, code aborted.", vbCritical
        Exit Sub
    End If

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://network.construction.com/reportviewer.aspx?ids=" & sUserInput, _
        Destination:=Range("$A$1"))
        .Name = "reportviewer.aspx?ids=" & sUserInput
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "30,33,37,38,46"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

这篇关于Excel提示用户输入要在宏中使用的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！