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)

Lulu

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都唔使改。

👍 0 👎 0