56 Ya 發問於 電腦及互聯網軟體 · 8 年前

Pass arguments within Excel

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 ?

更新:

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
    Lv 6
    8 年前
    最愛解答

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

還有問題嗎?立即提問即可得到解答。