Code to Create FileHandle on Users’ Desktop

August 29, 2008 at 03:25 · Filed under VBA Advanced, VBA Examples

by Selva V Pasupathy, HSBC Global Resourcing, Hyderabad
While at my workplace, I came across a situation when to get input from users, we had to create a file but without giving physical access to the users to that file. The code below helps to create a vbs file on desktop and you may ask the users to click on the vbs file to open the workbook and they can start the input through userform. I set application visible property as false, so user will be able to see only userform, and it will make you get a feel of a standalone application. I am not sure whether this will be a successful attempt, but there is nothing wrong in giving this a try. 

Sub Create_Opener()
Dim fso, txt As Object
Dim myFile As String
Dim i As Integer
  Call Get_CodeLines
  myFile = File_on_DeskTop
  Set fso = CreateObject("scripting.filesystemobject")
  Set txt = fso.createtextfile(myFile, 2, True)
  For i = 1 To 7
    txt.WriteLine CodeLines(i)
  Next i
  txt.Close
  Set txt = Nothing
  Set fso = Nothing
  MsgBox "A Handle for this File has been " & _
        "successfully created on desktop " & _
        "with filename " & vbNewLine & _
        File_on_DeskTop
End Sub

Private Sub Get_CodeLines()
CodeLines(1) = "'  The following lines of " & _
                  the codes act as an Opener to this file"
CodeLines(2) = "dim xlApp"
CodeLines(3) = "dim myFile"
CodeLines(4) = "myFile=" & Chr(34) & ThisWorkbook.Path & "\" & _
                  ThisWorkbook.Name & Chr(34)
CodeLines(5) = "set xlApp= CreateObject(" & Chr(34) & _
                 "excel.application" & Chr(34) & ")"
CodeLines(6) = "xlApp.workbooks.open(myFile)"
CodeLines(7) = "xlApp.VISIBLE=false"
End Sub

Function File_on_DeskTop() As String
File_on_DeskTop = CreateObject("WScript.Shell"). _
                   SpecialFolders("Desktop") & _
                   "\" & "DataInput.vbs"
End Function

Leave a Comment

You must be logged in to post a comment.