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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Skip Bisconer - 30 Nov 2007 07:29 GMT

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

Reply to this Message

Daniel Pineault - 30 Nov 2007 12:56 GMT

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"

Signature

Hope this helps,

Daniel Pineault

> 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
[quoted text clipped - 17 lines]
>
> End Sub

Reply to this Message

Skip Bisconer - 30 Nov 2007 18:44 GMT

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?

> Once you have exported the data then you can simply open the file (any file)
> using the Application.FollowHyperlink Method
[quoted text clipped - 23 lines]
> >
> > End Sub

Reply to this Message

fredg - 30 Nov 2007 19:34 GMT

> Thanks for responding.
> I included this with my transferspreadsheet code. The transfer happened but
[quoted text clipped - 28 lines]
>>>
>>> End Sub

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.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Skip Bisconer - 30 Nov 2007 20:30 GMT

Fred,

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

> > Thanks for responding.
> > I included this with my transferspreadsheet code. The transfer happened but
[quoted text clipped - 36 lines]
> path. Also, unless you are using the continuation character, all of
> the FollowHyperlink code should be on one line.

Reply to this Message

Skip Bisconer - 30 Nov 2007 21:29 GMT

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?

> Fred,
>
[quoted text clipped - 40 lines]
> > path. Also, unless you are using the continuation character, all of
> > the FollowHyperlink code should be on one line.

Reply to this Message

fredg - 30 Nov 2007 21:52 GMT

> 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
[quoted text clipped - 48 lines]
>>> path. Also, unless you are using the continuation character, all of
>>> the FollowHyperlink code should be on one line.

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.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message