Using code to add shortcut icon to desktop

[ABabeNChrist]

Is it possible to add a short cut icon of new folder to deck top or a selected folder?
Here is the code I wish to apply too.

Code: Select all

MsgBox ("You are now starting a New Report")
If Application.Dialogs(xlDialogSaveAs).Show(arg2:=xlOpenXMLWorkbookMacroEnabled) Then
    MsgBox ("You may now select OPEN REPORT and begin working on your report")
Else
    MsgBox ("You have selected cancel")
End If

[HansV]

Could you try to explain that again? What do you want to add a shortcut icon to? To your custom ribbon section, or ...?
And what does the code have to do with it?

[ABabeNChrist]

I’m sorry HansV
I probably got a little head of myself.
When the Save as dialog is opened and the new workbook name is given and then saved. Is it possible to have a Short cut Icon of newly name workbook put on deck top.
Or can I do this using my custom ribbon, either way would be OK

[HansV]

Try code like this:

Code: Select all

Sub CreateDesktopShortcut()
  Dim WSHShell As Object
  Dim strDesktopPath As String
  Set WSHShell = CreateObject("WScript.Shell")
  ' Read desktop path using WshSpecialFolders object
  strDesktopPath = WSHShell.SpecialFolders("Desktop")
  If Not Right(strDesktopPath, 1) = "\" Then
    strDesktopPath = strDesktopPath & "\"
  End If
  With WSHShell.CreateShortcut(strDesktopPath & "Shortcut to workbook.lnk")
    ' Set shortcut object properties and save it
    .TargetPath = ActiveWorkbook.FullName
    .Save
  End With
  Set WSHShell = Nothing
End Sub

[StuartR]

Just as a matter of pedantry, it might be better to use Application.PathSeparator instead of "\"

[HansV]

You're correct, of course, but this code will only work on Windows, so I don't think the path separator will ever be different from "\".

[ABabeNChrist]

Thank you HansV
It worked great, but I made a couple changes
I changed

Code: Select all

 With WSHShell.CreateShortcut(strDesktopPath & "Shortcut to workbook.lnk")

to

Code: Select all

 With WSHShell.CreateShortcut(strDesktopPath & "\" & ActiveWorkbook.Name & ".lnk")

So that the shortcut would have the same name as Active Workbook
and then added a message at the end

Code: Select all

  Dim WSHShell As Object
  Dim strDesktopPath As String
  Set WSHShell = CreateObject("WScript.Shell")
  ' Read desktop path using WshSpecialFolders object
  strDesktopPath = WSHShell.SpecialFolders("Desktop")
  If Not Right(strDesktopPath, 1) = "\" Then
    strDesktopPath = strDesktopPath & "\"
  With WSHShell.CreateShortcut(strDesktopPath & "\" & ActiveWorkbook.Name & ".lnk")
    ' Set shortcut object properties and save it
    .TargetPath = ActiveWorkbook.FullName
    .Save
  End With
  Set WSHShell = Nothing
MsgBox "A shortcut icon for this report has been placed on your desktop"
End If

I hope I did it right, it seems to work

[HansV]

The code I posted was only intended as an example, you are free to modify it of course - in fact, I encourage you to do so, because it'll give you a feeling for what can and can't be done.

However, you have wrongly moved down the End If belonging to

If Not Right(strDesktopPath, 1) = "\" Then

It should be immediately below the line

strDesktopPath = strDesktopPath & "\"

for the following code (starting with With) should be executed whether the condition was true or not.

[ABabeNChrist]

Thank you HansV
Greatly Appreciated.
I made the correction, I do understand the true or false about using End if. I just have to let it sink in so I can be aware of it next time.
My I ask, why was the code able to run without showing an error I used Compile VBA Projects to check.

[HansV]

When you moved the "End If" down, the code was still syntactically correct, so you could compile it without error, and it would run without problems too.

But what you told it to do was: if the path to the user's desktop does not end in a backslash, add the backslash and create the shortcut; otherwise do nothing at all (i.e. don't create the shortcut).

It should be: if the path to the user's desktop does not end in a backslash, add the backslash. After that, whether you had to add the backslash or not, create the shortcut.

[ABabeNChrist]

Its like learning a new language, I believe I understand what you are saying
End If separates the line of code

[HansV]

To get a feeling for how If ... End If works, try this example:

Code: Select all

Sub Test1()
  Dim n As Integer
  n = InputBox("Enter a whole number")
  If n > 10 Then
    MsgBox "You should see this only if you entered a number greater than 10."
  End If
  MsgBox "You should see this whatever you entered."
End Sub

Copy the code into a standard module in the Visual Basic Editor in Word or Excel.
Run this macro, and enter a number below 10, for example 5. You should see one message box.
Run the macro again and enter a number over 10, for example 13. You should see two message boxes, one after another.

Next, try this:

Code: Select all

Sub Test2()
  Dim n As Integer
  n = InputBox("Enter a whole number")
  If n > 10 Then
    MsgBox "You should see this only if you entered a number greater than 10."
  Else
    MsgBox "You should see this only if you entered a number less than or equal to 10."
  End If
  MsgBox "You should see this whatever you entered."
End Sub

When you run the code, you should see two message boxes whether you enter a number under 10 or over 10, but the first message box will be different depending on what you entered.

[ABabeNChrist]

First Code
So if the first value is met, which happens to be >(greater than 10) then first MsgBox will be displayed first. It the value is less <(than the number 10) then the first message is not displayed, only the second

[HansV]

That is correct! If you enter a value less than (or equal to) 10 in the first macro, the first message box is skipped because of the If ... End If. The second message box will always be displayed because it comes after the End If..

[ABabeNChrist]

The second code
So by adding “Else” betweens messages/code if the value greater than 10 is met then the first Message / Code
will run, if the value is not met then the second message / code that follows ‘Else”
and the last message is after End If which will allowing be displayed

[HansV]

Yep.

As you see, the placement of the Else and End If lines is not arbitrary, they determine which code will be executed (or not) depending on the condition in the If ... Then line.

[ABabeNChrist]

Thank you for the lesson
I'm like a sponge, soak it up
Plus I enjoy it
Thanks HansV