Open Excel via MsgBox

[Igawa29]

OK I am having a little trouble with this issue and from my Internet research I have complied the following code:

Private Sub Submit_Click()

If Me.combobox1.Value = "Table1" Then
DoCmd.OutputTo acOutputTable, "Table1", acFormatXLS, "C:\Documents and Settings\Igawa\Desktop\temp.xls"
Set objShell = CreateObject("Wscript.Shell")

intMessage = MsgBox("Open File?", _
    vbYesNo, "Access Denied")

If intMessage = vbYes Then
    stAppName = "excel.exe C:\Documents and Settings\Igawa\Desktop\temp.xls"
    Call Shell(stAppName, 1)
    
    Else
    Wscript.Quit
End If
End Sub

So basically the code works to create the file, the problem is trying to open the file. I get a "File not Found" and I don't understand why, because when I go to the file location the xls is there.

 

[MarkWalsh]

You most likely have to put quotes (not sure if it needs to be single or double quotes) around the filepath when you use it in a shell command.

 

[Igawa29]

Hey Mark,
I tried the "" and I get a syntax error, and I tried the '' and I still get the File Not Found Error

 

[MarkWalsh]

Try this:

stAppName = "excel.exe ""C:\Documents and Settings\Igawa\Desktop\temp.xls"""

 

[Igawa29]

I am still getting that File Not Found. When I debug it is highlighting the :

Call Shell(stAppName, 1)

 

[MarkWalsh]

The code works properly for me - it only produces an error if the file is already opened. Otherwise, it opens the excel document.

Are you absolutely sure the filepath is correct?

 

[Igawa29]

Yep filepath is correct because the code is creating the file first.. and I check the path and I see the file is there. I even moved a file into the folder and tried that and it still didn't open. Not sure why it is working on your computer and not mine. It's a headscratcher.

 

[Igawa29]

I GOT IT!

I just had to put C:\Program Files\Microsoft Office XP Standard\OFFICE11\

in front of the excel.exe

 

[PWise]

try

stAppName = "excel.exe ""C:\Documents and Settings\Igawa\Desktop\temp.xls"""

 

[Igawa29]

hmmm it just doesn't want to work unless I have the full excel path

 

[combo]

What about using only vba and excel:

Dim xlObj As Object
Set xlObj = CreateObject("Excel.Application")
xlObj.Workbooks.Open "C:\Documents and Settings\Igawa\Desktop\temp.xls"
xlObj.Visible = True

combo

 

[SkipVought]

BTW, what APPLICATION are you coding in? After all, this forum is VBA Visual Basic for Applications (Microsoft)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Igawa29]

Combo, still not working for me.. for whatever reason.

Skip I am using Access.. probally should have said that lol.

I don't think on my computer there is a way around the full path.

 

[combo]

I have it working. Can you try with additional checkbox (open the file), its value can be immediately used as 'AutoStart' parameter in 'DoCmd.OutputTo'?

combo

 

[PHV]

What about this ?

Private Sub Submit_Click()
stWB = "C:\Documents and Settings\Igawa\Desktop\temp.xls"
If Me.combobox1.Value = "Table1" Then
  DoCmd.OutputTo acOutputTable, "Table1", acFormatXLS, stWB
  intMessage = MsgBox("Open File?", _
    vbYesNo, "Access Denied")
  If intMessage = vbYes Then
    DoEvents
    FollowHyperlink stWB
  End If
End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886