Pass arguments within Excel

2013-07-18 7:46 pm
How can I do the following ?

I have some two thousands rows in Excel in following format :

NameID
Dejan 8082
Bernard 8317
Will 8349
.
.

I would like to create some hyperlinks at all IDs so that once I click any of them, the ID and the corresponding Name will be passed as arguments to the following batch file.

echo
echo Dear %1,
echo Your ID which is %2 has been locked.

I can call a batch file through hyperlink but seems passing argument in it is invalid.
I suspect some Visual Basic coding is needed. Can anyone help ?
更新1:

Sorry. The original table above depicts the format incorrectly. All the names should be in column "A" and IDs should be in column "B". Something like the following : |___A___|___B__| |_______|_______| |_Name_|___ID__| |__Joe__|__1567_| |__Ada__|____17_| |__Ben__|____32_|

回答 (1)

2013-07-19 9:23 pm
✔ 最佳答案
  A B C D E 1 NameID         2 Dejan 8082       Will 8349 3 Bernard 8317         4 Will 8349       Will 5         8349
在工作表加入以下VB code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Range("E2"), ActiveCell) Is Nothing Then
If Not ActiveCell = "" Then
Application.EnableEvents = False
Range("E2") = ActiveCell
Application.EnableEvents = True
Cancel = True
End If
End If
End Sub

如上表,double click A4會把value pass 去E2,然後再把E2分析去E4 & E5,跟住先用E4&E5去出你封信。

E4公式 =LEFT(E2,LEN(E2)-5)
E5公式 =RIGHT(E2,4)

看看這方法能否達到你的目的。

2013-07-19 13:59:18 補充:
下面的 VB code可成功pass E4, E5 去 batch file:

Sub test()
Shell ("c:\new.bat " & Range("E4").Value & " " & Range("E5").Value)
End Sub

2013-07-20 00:02:33 補充:
唔緊要,將錯就錯,反正double click 都係只 click 一個 cell,咁就做多一個 column 來combine Name & ID, 假設是放在C column 的話,咁double click C column 的 cell,一樣可以得相同結果。

C2: A2 & " " & B2
C3: A3 & " " & B3
......
連VBA code都唔使改。


收錄日期: 2021-05-03 21:21:14
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20130718000051KK00113

檢視 Wayback Machine 備份