Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Open Excel via MsgBox

Status
Not open for further replies.

Igawa29

MIS
Jan 28, 2010
99
US
OK I am having a little trouble with this issue and from my Internet research I have complied the following code:

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.
 
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.
 
Hey Mark,
I tried the "" and I get a syntax error, and I tried the '' and I still get the File Not Found Error
 
Try this:

stAppName = "excel.exe ""C:\Documents and Settings\Igawa\Desktop\temp.xls"""
 
I am still getting that File Not Found. When I debug it is highlighting the :

Code:
Call Shell(stAppName, 1)
 
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?
 
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.
 
I GOT IT!

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

in front of the excel.exe
 
try
Code:
stAppName = "excel.exe ""C:\Documents and Settings\Igawa\Desktop\temp.xls"""
 
hmmm it just doesn't want to work unless I have the full excel path
 
What about using only vba and excel:
Code:
Dim xlObj As Object
Set xlObj = CreateObject("Excel.Application")
xlObj.Workbooks.Open "C:\Documents and Settings\Igawa\Desktop\temp.xls"
xlObj.Visible = True


combo
 

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

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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.
 
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
 
What about this ?
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top