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屋!