Another one for the Programmers

[Daxton A.]

I am using the OutputTo Statement.
When i leave the filename to Output to....blank, access prompts me for the file, this is good, that's what i want it to do. But lets say i want to then take and open the file in excel. How would i code that?

I know that i somehow have to set an object variable = to the textbox on the popup where the name of the file is typed. But i dont know how to do that with a default dialog box. I also dont know what code to use to open Excel or the file in excel. Do you have an idea on how to accomplish this task?

Thanxamillion,
Daxton

 

[rich.barry]

Put an invisible Hyperlink on the page...
then


DoCmd.OutputTo intType, strName, acFormatXLS, "c:\report.xls"
Forms!SwitchBoard!MainHyperLink.HyperlinkAddress = "c:\Report.xls"
Forms!SwitchBoard!MainHyperLink.Hyperlink.Follow


This example uses the same filename every time.
If you want to define your file name then try this.


Dim FiletoOpen as String

FiletoOpen=InputBox("Enter File Name")
DoCmd.OutputTo intType, strName, acFormatXLS, FiletoOpen
Forms!SwitchBoard!MainHyperLink.HyperlinkAddress = FiletoOpen
Forms!SwitchBoard!MainHyperLink.Hyperlink.Follow

 

[sambo]

In regards to opening and retrieving excel..
You need to do 1 of 2 things.

1. If Excel.exe is already running, use GetObject Method

OR

2. If Excel.exe is not already running, use CreateObject Method


Check out this site to find the nuts and bolts behind the fIsAppRunning function.

Dim xlApp as Object
If fIsAppRunning("excel", False) = True Then
  'MsgBox "Excel is Running"
  Set xlApp = GetObject(, "Excel.Application") 'Create the xls space
Else
  'MsgBox "Excel is not running"
  Set xlApp = CreateObject("Excel.Application") 'Create the xls space
End If

 

[Daxton A.]

Thanks for the help so far fellas

The filename is going to change everytime. It has to. I need to be able to catch the filename that is typed in the dialog box. Any idea on how to do this. Im doing all of this using the OutputTo Statement. If you leave the filename blank then it will prompt for the user to determine where to put the file and what to name it.

Have any ideas?

 

[rich.barry]

Did you actually try what was suggested ?? It is really easy.

To make a Hyperlink, just put a label on the form. In the example I called it MainHyperLink.

Don't let Access prompt you for the file name by leaving the OutputTo field blank, otherwise you can't capture what the user types. Pre-empt it by making your own dialog box.

Dim FiletoOpen as String

'Get a file name from the user
FiletoOpen=InputBox("Enter File Name")

'but don't process a blank file name
If FiletoOpen ="" then Exit Sub

'Write the Query to a file
DoCmd.OutputTo acOutputQuery, "Your Query", acFormatXLS, FiletoOpen

'Point the Hyperlink to that file
Forms!YourFormName!MainHyperLink.HyperlinkAddress = FiletoOpen

'Follow the Hyperlink to open the file
Forms!YourFormName!MainHyperLink.Hyperlink.Follow

 

[mrxdotcom]

Thanks for the reply... it's perfect.. thanks again