excel macro to execute windows run command?

[Badassack]

I found the following code here on these forums but it errors out .
I guess X is a variable that needs to be defined? Or is there another method to get the run command to come up?


Sub Macro1()x = Shell("cmd.exe /c C:\TestFolder\Book1.pdf", 1)End Sub</pre>

 

[Badassack]

Hey so I was wondering if it was possible to replace the paths with a variable. I want to use "application.getopenfilename" and use a variable from that instead of the paths listed. Any Thoughts?


Dim Str_Reqd As String, x As Double
Str_Reqd = "cmd.exe /c "


Str_Reqd = Str_Reqd & """""D:\ssd programs\games\Steam\SteamApps\common\Starbound\win32\asset_unpacker.exe"" "
Str_Reqd = Str_Reqd & """D:\ssd programs\games\Steam\SteamApps\common\Starbound\assets\packed.pak"" "
Str_Reqd = Str_Reqd & "C:\starbound"""""
'Range("A1") = Str_Reqd ' use this to test the string is assembling properly
x = Shell(Str_Reqd, 1)

 

[Badassack]

I got this so far am I on the right track?

Dim retval As Long
Dim unpacker As String
Dim pakfile As String

unpacker = Application.GetOpenFilename
pakfile = Application.GetOpenFilename

retval = Shell("cmd.EXE """ & unpacker & "pakfile" & "C:\starbound""", 1)

 

[Badassack]

hmmmm, I need to be smarter I think, this is what I want to happen.....

Dim Str_Reqd As String, x As Double
Dim unpacker As String
Dim pakfile As String


Str_Reqd = "cmd.exe /k "
unpacker = Application.GetOpenFilename
pakfile = Application.GetOpenFilename





Str_Reqd = Str_Reqd & """""unpacker"" "
Str_Reqd = Str_Reqd & """pakfile"" "
Str_Reqd = Str_Reqd & "C:\starbound"""""
'Range("A1") = Str_Reqd ' use this to test the string is assembling properly
x = Shell(Str_Reqd, 1)

 

[Teeroy]

I think what you are after is;

Dim Str_Reqd As String, x As Double
Dim unpacker As String
Dim pakfile As String

Str_Reqd = "cmd.exe /k "
unpacker = Application.GetOpenFilename
pakfile = Application.GetOpenFilename

Str_Reqd = Str_Reqd & """""" & unpacker & """ "
Str_Reqd = Str_Reqd & """" & pakfile & """ "
Str_Reqd = Str_Reqd & """C:\starbound"""""
'Range("A1") = Str_Reqd ' use this to test the string is assembling properly
x = Shell(Str_Reqd, 1)

 

[Badassack]

You sir are awesome, your making my programming dreams come true. I had just one more question. You can get a file to open with Application.getopenfilename. What do you use to pick a destination folder or pick a folder in general? Do you have a third party code for that? I have a sub routine that can pick a folder but after you pick your folder it just has a msgbox that pops up and gives you the path. I'm sure it could be altered to store that info in a variable.

 

[Badassack]

I found this it's exactly what i needed. I just changed out the msgbox for a variable and it works great!!
<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;">
Sub SelectFolder()
Dim diaFolder As FileDialog
' Open the file dialog
Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
diaFolder.AllowMultiSelect = False
diaFolder.Show
MsgBox diaFolder.SelectedItems(1)
Set diaFolder = Nothing
End Sub</code>

 

[Teeroy]

Adapted from code by Richard Schollar (on this forum):

Dim sFolderPath
Dim fldr As FileDialog

Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .AllowMultiSelect = False
    .Show
    sFolderPath = .SelectedItems(1)
End With

MsgBox sFolderPath 'as test only

ps: I'm getting bad lag today and didn't get your last post until this one posted. Google is always your friend for code samples .

 

[Badassack]

Thanks man, The more options for code the better.

 

[bhenard]

I am including an example excel macro that I used to run an automated backup of my documents using a DOS batch file. I hope this example will give some help with your application.

Here is the worksheet subroutine that calls the module subroutine:

Private Sub cb_backup_Click()
Set ws = Worksheets("backup")
Set source_dir = ws.Range("source")
Set destination_dir = ws.Range("destination")

Call backup.backup(destination_dir.Value, source_dir.Value)

End Sub​

Here is the module "backup" subroutine:

Sub backup(destination_dir As String, source_dir As String)

'create a file
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.CreateTextFile(".\backup.bat", True) 'Create a file (overwrite if necessary)
'write xcopy backup command
f.Writeline "xcopy " & Chr(34) & source_dir & "*.*" & Chr(34) & " " & _
Chr(34) & destination_dir & Chr(34) & " " & "/Y /D /E /I"
f.Close

'execute DOS batch file
s = ".\backup.bat"
s = Shell(s, vbMaximizedFocus)

End Sub
​