Using SHELL function

[SteveR77]

:-(

I have created two functions in ACCESS 2000 that do a TransferSpreadSheet command that export one of two queries and then executes the Shell function in order to bring up the file that was created up in Excel.

Both functions create the Excel speadsheet without a problem but one of the functions gives me the error of the file not being found. I tested using a single char as the file name and it still does not work.

PLEASE, tell me what I am missing.

Thanks,

Steve

 

[THoey]

Can you show us the code and the error? Someone will be able to figure it out with that... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[KenG]

It's just a guess...but maybe your code is finishing before the shell function is actually done running Excel.

When using Excel I've had better luck with an Excel object
For example:
Set objXLApp = CreateObject("Excel.Application&quot

Then I run a macro in Excel using something like:
objXLApp.Run("'C:\Program Files\Microsoft _ Office\Office\XLSTART\PERSONAL.XLS'!Macro1&quot

Hope this helps,

 

[SteveR77]

The code that fails:

Private Sub cmdAR_Report_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "A/R Report Query", "C:\Program Files\Microsoft Office\Office\AcctRec", True
Dim RetVal
RetVal = Shell("C:\Program Files\Microsoft Office\Office\excel.exe AcctRec.xls", vbNormalFocus)
End Sub

The ERROR message:

'AcctsRec.xls' Could not be found. Check the spelling of the file name, and verify that the file location is correct.

 

[SteveR77]

Ken,

I was thinking it was a timing issue as well. It just ticks me off when I can write the same blocks of code and the "fine" MS products don't work with any consistancy...

Thanks again,

Steve

 

[THoey]

I haven't exported to Excel, but I did notice that you don't give the filename an &quot;XLS&quot; extension on the TransferSpreadsheet. Do you have to? <<reaching for straws>> Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[SteveR77]

Terry,

I have tried it both ways and the TransferSpreadsheet assumes an XLS extention.

Thanks for the response... When I get a moment I'll have to see if I can get the suggestion of working with objects to work.

 

[SteveR77]

[COLOR==blue] THE ANSWER! [/color]

NOTE: You must add the reference for the MS Excel Object Library in order to have this work..

Private Sub cmdAR_Report_Click()
Dim objExcel As Excel.Application
Set objExcel = New Excel.Application [COLOR==blue] - This creates an instance of MS Excel[/color]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, &quot;A/R Report Query&quot;, &quot;C:\Program Files\Microsoft Office\Office\AcctRec&quot;, True

objExcel.Visible = True [COLOR==blue] - This brings the Excel window up [/color]
objExcel.Workbooks.Open &quot;C:\Program Files\Microsoft Office\Office\AcctRec.xls&quot;

End Sub

The last line of the subroutine brings up the file that I created from the DoCmd line.
This logic will also work with other applications as long as you can reference the Application object.

Thanks again for ALL the help and I hope others can use this information.

B-) Steve

 

[THoey]

Good job Steve!! And KenG... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.