Excel Macro to create a file and open it

[chandrusvc]

I am new to excel macro and VB, pls bare with me.
I have remedy ticket numbers(eg , HD0000001006530) in one column.
I have to create a hyperlink on each cell referencing to itself.Cliking 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 tikcet HD0000001006530 in remedy.
==============================
[Shortcut]
Name = HPD: HelpDesk
Type = 0
Server = remedyprd
Ticket = HD0000001006530 <--- This value will come from excel cell

Any ideas ?

 

[bertie]

Hi and welcome to the forum.

I may be only able to give you a partial solution as I have no means of testing if the output ".artask" file opens the parent program.

From Google, the .artask extension denotes a text file. So here is the code to output a text file with an .artask extension. Fingers crossed...

Assumptions:
We are working with Sheet1.
The cell with the hyperlink only displays the ticket number, i.e., HD0000001006530.
This is the TextToDisplay property.

Press ALt+F11 to open the vba editor window.
Click Insert => Module.
Copy and paste the code below.
Edit where highlighted.

[COLOR=darkblue]Sub[/COLOR] OutputArtaskTextFile([COLOR=darkblue]ByVal[/COLOR] TicketNumber [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR])
   [COLOR=darkblue]Dim[/COLOR] sPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] fNum [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sOutput [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   
   sPath = "[COLOR=#ff0000]C:\temp[/COLOR]\" & TicketNumber & ".artask"
   
   sOutput = "==============================" & vbCrLf _
         & "[Shortcut]" & vbCrLf _
         & "Name = HPD: HelpDesk" & vbCrLf _
         & "Type = 0" & vbCrLf _
         & "Server = remedyprd" & vbCrLf _
         & "Ticket = " & TicketNumber


   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] errExit
   
   fNum = FreeFile
   [COLOR=darkblue]Open[/COLOR] sPath [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Output[/COLOR] [COLOR=darkblue]As[/COLOR] #fNum
      [COLOR=darkblue]Print[/COLOR] #fNum, sOutput
   [COLOR=darkblue]Close[/COLOR] #fNum


errExit:
   [COLOR=darkblue]Close[/COLOR] #fNum
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Double click the Sheet1 module in the Project Window on the left hand side.
Copy and paste the code below.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_FollowHyperlink([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Hyperlink)
   Module1.OutputArtaskTextFile Target.TextToDisplay
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Go back to Excel and click the hyperlink and test the output file.

Hope this helps,
Bertie