call shell

[rblair11]

I am using transferspreadsheet to output a query to an excel spreadsheet. I then want that spreadsheet to open automatically.

If I code the filename in the call shell command it works fine, as in:

Call Shell("C:\Program Files\Microsoft Office\OFFICE11\excel.exe ""C:\Documents and Settings\Desktop\Filename.xls""", 1)

However, the way the code works is that the transferspreadsheet outputs to a filename chosen by the user from an input box. I've been racking my brain for several hours trying to get the variable strMyFile inserted into the Call Shell command with no luck.

My first thought was to replace path and filename.xls with strMyFile but excel comes back and says can't find strMyFile.xls. strMyFile is supposed to be a variable containing the full path and filename.

How do I pass the information in strMyFile to the call shell command? It was so easy with the transferspreadsheet portion.

Code is below

    strMyfile = InputBox("Enter File Name", "File Name")
    strMyfile = "C:\Documents and Settings\Rael\Desktop\" & strMyfile
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryWorkoutExport", strMyfile
    Call Shell("C:\Program Files\Microsoft Office\OFFICE11\excel.exe ""strMyFile""", 1)

Thank you.

 

[namliam]

    strMyfile = InputBox("Enter File Name", "File Name")
    strMyfile = "C:\Documents and Settings\Rael\Desktop\" & strMyfile
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryWorkoutExport", strMyfile
    Call Shell("C:\Program Files\Microsoft Office\OFFICE11\excel.exe ""strMyFile""", 1)

That is nearly right....
You are doing
strMyfile = "C:\Documents and Settings\Rael\Desktop\" & strMyfile
To read the content of the variable... but not here....
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\excel.exe ""strMyFile""", 1)

I think you can figure it from here?

Season greets from Amsterdam

The Mailman

 

[rblair11]

I got it, finally

I was making it too difficult and I also read about the chr$(34) trick to get quotes included. Here is the code that works:

MyfileShell = Chr$(34) & "C:\Program Files\Microsoft Office\OFFICE11\excel.exe " & Chr$(34) & " " & Chr$(34) & Myfile & Chr$(34)
    Call Shell(MyfileShell, 1)

 

[namliam]

Note the difference:

"C:\Program Files\Microsoft Office\OFFICE11\excel.exe ""strMyFile"""

vs

"C:\Program Files\Microsoft Office\OFFICE11\excel.exe " & Chr$(34) & " " & Chr$(34) & Myfile & Chr$(34)

Greets

 

[Paul200968]

shell string not working

Hi, Im trying to get the string from my combobox (which is a field from a tabel) into the shell but it doesnt seems to work. this is the code:

Private Sub Combo1_AfterUpdate()
'myfile = "\\Tpghos41data.nl.tntpost.com\occ&sqa$\OPS_Processes\Reports\Awkward\test.txt"
myfile = Me.Combo1

MyfileShell = Chr$(34) & "Notepad.exe " & Chr$(34) & " " & Chr$(34) & myfile & Chr$(34)
Call Shell(MyfileShell, 1)


'Call Shell("Notepad.exe \\Tpghos41data.nl.tntpost.com\occ&sqa$\OPS_Processes\Reports\Awkward\test.txt", 1)
End Sub