Access to excel problem

[cornishteeth]

I have a report that needs to be viewed in an excel
spreadsheet. I want to create a macro that opens the
report as an excel spreadsheet for viewing, and then sends
that object via outlook once I allow it to go. I know how
to send object, its just a problem with getting the
spreadsheet to open for viewing. I can get it to save on
my desktop, but not automatically open. Can anyone help.

Frank

 

[Norie]

What code do you already have?

When do you want to view the spreadsheet?

 

[cornishteeth]

I was trying to do it via the Macro functions built into access. I want to view the spreadsheet when I depress the command button prompting the sheet to open. My programming of VB is limited. Does this task require VB code to accomplish?

Frank

 

[Norie]

Frank

Have you looked at the RunApp macro action. That might be what you need.

If that doesn't work then try converting what you the macros you have already into Visual Basic

(Tools>Macro>Convert Macros to Visual Basic when in the Macro Tab).

Post that code and maybe we can help.

 

[cornishteeth]

Function Macro345()
On Error GoTo Macro345_Err

DoCmd.OpenReport "new arrivals", acPreview, "", "[STU DATA]![Report Date]>[Forms]![MENTORING]![Text0]-1"
DoCmd.SendObject acReport, "new arrivals", "MicrosoftExcel(*.xls)", "", "", "", "", "", False, ""


Macro345_Exit:
Exit Function

Macro345_Err:
MsgBox Error$
Resume Macro345_Exit

End Function

Here is what the macro does so far. In between opening the report and sending the report off, I want to be able to convert the open report to excel and view it prior to sending the object. Does this make sense?

Frank

 

[Norie]

Frank

The first thing I noticed is that you don't seem to be actually creating an Excel spreadsheet from the report.

Your just sending it in Excel format.

I don't know exactly how Access does this but it probably creates a temporary xls somewhere.

I think you'll need to look at actually exporting the report to Excel then opening it.

BTW did you look at the RunApp method?

 

[cornishteeth]

From what I gather, the RunApp just opens up a blank excel spreadsheet. I don't know how it would apply. I can currently do what you said. Change the report to and excel spreadsheet and save it on my harddrive. Then I need to go the file, open it up, verify it, then send it out in outlook. I was trying to have access open it up in excel, let me look at it, and when I accept it, send it out in outlook.

Frank

 

[Norie]

Frank

Does the RunApp method not take any arguments, like filename?

 

[cornishteeth]

I tried C:\newarrivals.xls in the command line and it didn't work.

 

[cornishteeth]

Changed the command line to ...

C:\Program Files\Microsoft Office\Office\excel.exe C:\newarrivals.xls

and it worked.

Thanks