[SOLVED] Excel Macro to create a file and open it

Excel Macro to create a file and open it

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

Solution

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.