Can't make code work to open excel from Access 2003

S

Skip Bisconer

I have tried all the code I understand from this newsgroup and nothing seems
to work for me. I am wanting to transfer a select query to Excel format then
open the file in Excel from access. I have a command button that does the
spreadsheet transfer but I don't know enough about the code I see on this
site to open the file in Excel. Could some give me the code to follow this
statement?

Private Sub Command2_Click()

On Error GoTo Err_Command2_Click

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Skip-Customer
Monitor By Quarters", _
"C:\Documents and Settings\Milo E Bisconer\My Documents\Ben's
Business\TestExport24MonthHistory.xls"

Exit_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub
 
D

Daniel Pineault

Once you have exported the data then you can simply open the file (any file)
using the Application.FollowHyperlink Method

Application.FollowHyperlink "C:\Documents and Settings\Milo E Bisconer\My
Documents\Ben's Business\TestExport24MonthHistory.xls"
 
S

Skip Bisconer

Thanks for responding.
I included this with my transferspreadsheet code. The transfer happened but
Excel didn't open. Do I need to put this in a separate module? Of give it
more direction of some sort?
 
F

fredg

Thanks for responding.
I included this with my transferspreadsheet code. The transfer happened but
Excel didn't open. Do I need to put this in a separate module? Of give it
more direction of some sort?


Next time copy and paste the exact code used in a reply message.

Make sure the path to the application is the same as in the
TransferSpreadsheet code
I would suspect you miss-spelled something in the FollowHyperlink
path. Also, unless you are using the continuation character, all of
the FollowHyperlink code should be on one line.
 
S

Skip Bisconer

Fred,

Thanks for the help that was the issue. I copy pasted and it worked fine.
 
S

Skip Bisconer

I don't know if this should be a new post but I have a question in regarding
this same thread. When I run this code it worked great until I added an
macro to run on worksheet_open. With out the macro the file opens in
approximately 7 seconds. But with the macro in the Workbook_Open it takes 2
1/2 minutes to get to a warning screen that this file may contain viruses
etc. Is there a work around for this to speed up the process and maybe
eliminate the need for user input?
 
F

fredg

I don't know if this should be a new post but I have a question in regarding
this same thread. When I run this code it worked great until I added an
macro to run on worksheet_open. With out the macro the file opens in
approximately 7 seconds. But with the macro in the Workbook_Open it takes 2
1/2 minutes to get to a warning screen that this file may contain viruses
etc. Is there a work around for this to speed up the process and maybe
eliminate the need for user input?

I would suggest you post this in an Excel newsgroup.
Include the exact code (copied and pasted) you are using, then include
the bit about it taking a long time to run. Give your Windows and
Office version numbers, as well as the service packs you have
installed.
Include the macro code so someone has something positive to work on.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top