Excel Macro to create a file and open it

Question

I have remedy ticket numbers(eg , HD0000001006530) in one column.

I have to create a hyperlink on each cell referencing to itself. Clicking the hyperlink will run a macro.

The Macro have to create a file of type .artask with content like below and open it. Opening a .artask file will open the ticket HD0000001006530 in remedy.

[Shortcut]
Name = HPD: HelpDesk
Type = 0
Server = remedyprd
Ticket = HD0000001006530 <--- This value will come from excel cell

score 1 · Accepted Answer · edited Feb 05 '19 at 14:54

In your macroenabled excel file, Copy this code to the selected "worksheet" code pane.

           Private Function Createfile(ByVal cellvalue As String)
            Open "c:\" & cellvalue & ".artask" For Output As #1 'your target file name and address. you may change it to the desired folder
            Print #1, "[Shortcut]"
            Print #1, "Name = HPD: HelpDesk"
            Print #1, "Type = 0"
            Print #1, "Server = remedyprd"
            Print #1, "Ticket =" & cellvalue
            Close #1
           End Function

 'EDIT BEGINS:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Dim cell As Range
 If Dir("C:\" & cellvalue & ".artask") = "" Then 'For memory optimization we should first check if the file exists or not

For Each cell In Range("A1:A" & Me.UsedRange.Rows.Count) 'Specify the Range, in that case it is from A1 to end of the column A
   cell.Select
  ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="c:\" & Selection.Text & ".artask", TextToDisplay:=Selection.Text

    Next cell 'Loop through cells

            Createfile (Selection.Value)
        End If
          End If

      End Sub
    'EDIT ENDS

If you had any issues please let me know it.

Thank you. Your code is working great it creates the .artask file. I am still not able to figure out how to create an hyperlink automatically on a cell to reference itself(target to the same cell where hyperlink is placed) — Chandru, Feb 12 '13 at 12:54
Sorry for not being clear. My excel is connected to a DB via ODBC driver and it refreshes every 5 minutes to get new set of data.When it refreshes i want hyperlinks to get applied on the columnA where each cell reference to itself. When user click the hyperlink on a particular cell it has to create .artask file and open the file. Opening an .artask file will invoke Remedy which is the default applcation for .artask file. Thanks in advance. — Chandru, Feb 12 '13 at 13:59
I assumed that the values in the column A, are unique so we dont have duplication in files. If i was wrong you should delete the "If Dir("C:\" & cellvalue & ".artask") = "" Then" and one of the "End if"s. — David Peterson, Feb 12 '13 at 18:00

Excel Macro to create a file and open it

1 Answers1