Create disk or param file to auto install Excel VBA tool

[Asher]

Thanks to the help of folks on this board I have been able to create a great working tool. It is a form that sends the data entered onto it to a .csv file and that (in addition to many other functions) also has a button on it to delete the .csv file data so the file can be reused.

The details of these can be found on these two posts: Excel VBA form that will save data in a csv file-reusable and Delete entire row for all rows with data in Excel .csv file

This tool is now going to be used by some folks in other states and I have been asked/told to figure out how to install it on their computers without me being there. It was suggested that I either make an "install disk", or a "parameter file". The issues are these:

1. I don't want people to have access to the code and the .csv file path has to be changed whenever the tool (form) is put on a new computer.

2. I usually have to install a date/time picker file for use of the form since Excel doesn't just have it. It's the Microsoft Date and Time Picker Control 6.0 (SP 4) that requires the MSCOMT2.ocx and mscomct2.bat install.

3. I don't know how to make an install disk that will ask for the new file path (only on install) and add it to the code.

4. If an install disk is too tough, I don't know how to make a "parameter file" that someone can put the new path into and have that file read by the code when it runs.

5. I don't know if there is even a way to add the automatic install of the .ocx file and automatic run of the .bat file for the dtp to the code. (I may just have to walk them through this part on the phone or something).

I'm a bit overwhelmed with this. Can anyone help? My code is in the 2 posts mentioned earlier, but here are the places where the file path is required:

Code: Select all

Private Sub cmdDeleteUploadedRecords_Click()

'Delete all data from TestLog.csv

Dim csvWkbk As String
csvWkbk = "C:\Documents and Settings\mydrive\Desktop\LogFiles\TestLog.csv"

  If MsgBox("You are about to delete all data from " & csvWkbk & vbCrLf & _
      "Are you sure?", vbQuestion + vbYesNo) = vbNo Then
    Exit Sub
  End If

  With Workbooks.Open(Filename:=csvWkbk)
    .Sheets("TestLog").Cells.Delete
    .Close SaveChanges:=True
  End With


End Sub

Code: Select all

Private Sub cmdSubmit_Click() 
...
    'Open file for appending
  
    f = FreeFile
    Open "C:\Documents and Settings\mydrive\Desktop\LogFiles\TestLog.csv" For Append As #f
...
End Sub

Different subs use the path.

Let me know if you want the code in full posted, it's just long.

[HansV]

Perhaps you can use the free installer utility Inno Setup...

[Asher]

I'm not having much luck with the Inno Setup, although it looks like an awesome tool once I can figure it out. However I was think that all I really need is a variable that will equal the path to the users desktop. Is there something in VBA that can give me the file path to a computer's desktop to assign to a variable?

[Asher]

I got this info http://www.ozgrid.com/forum/showthread. ... ht=desktop from OzGrid and altered it to match my requirements so it looks like this:

Code: Select all

Public Sub inputPath()

Dim oWSS As Object
Dim inputPath As String

Set oWSS = CreateObject("WScript.Shell")
        
    With oWSS
        inputPath = .SpecialFolders("Desktop")
                
        Worksheets("NewLogEntry").Cells(2, "B").Value = inputPath
    End With
    
Set oWSS = Nothing
    
End Sub

, and it works.

Thanks for all your help. I'll keep looking in to Inno Setup, I have no doubt that will be extremely useful.

[PJ_in_FL]

It appears you may be making this more complicated than needed. If I understand correctly you need:

1. The workbook to be saved to the user's desktop.

2. The date and time picker control to be installed.

3. The user will run the workbook from their desktop every time they run it and not need to save it somewhere else.

If that's correct:

In your workbook, you can get the user's desktop location programatically:
Get the user's profile location from the environment variables set up by Windows. This short routine shows how to access those variables.

Code: Select all

Sub userprof()
    Dim uprof As String
    Dim udesk As String
    uprof = Environ("USERPROFILE")
    udesk = uprof & "\Desktop"
    MsgBox "User profile is " & uprof
    MsgBox "Desktop folder is " & udesk
End Sub

To see all the variables set by Windows open a command prompt window (START -> RUN and type "cmd" and press ENTER) and type "set" and press enter at the prompt.

After you've incorporated that into the workbook:

1. Save the workbook with the VBA project password protected before you send it out.

2. Package the workbook, .BAT and .OCX files into a ZIP file, possibly even a self-extracting ZIP file to simplify the install.

3. Send the ZIP (or EXE if self-extracting) file to the user(s).

4. Tell them to:
a. Extract all these files to their desktop.
b. Double-click the MSCOMCT2.BAT file.
c. Optionally, they can now delete the .BAT and .OCX files from their desktop.

5. Run the workbook from their desktop (double-click the workbook's icon).