Analyze in Excel

L

LB79

Registered User.

Local time

Today, 17:22

Joined

Oct 26, 2007

Messages

505

• Apr 3, 2009

• #1

Hello,

I wonder if anyone can assist with this tiny piece of code.
I have a table that is displayed within a subform.
I want to include a button that exports that table to Excel via the Analyze in Excel option.
I am using the code

Code:

[/FONT][/COLOR][FONT=Arial][SIZE=3][COLOR=#000080]DoCmd.RunCommand acCmdOutputToExcel[/COLOR][/SIZE][/FONT][COLOR=black][FONT=Verdana]]

Which works fine, but gives me a blank Excel sheet because the table isn't linked to the parent form.

Does anyone know have I can tie the code either to the subform or to the table itself?
I've tried

Code:

[/FONT][/COLOR][FONT=Arial][SIZE=3][COLOR=#000080]DoCmd.RunCommand acCmdOutputToExcel, "PMA_tblPMA_Export", “Table1”[/COLOR][/SIZE][/FONT][COLOR=black][FONT=Verdana] ]

But it debugs.

Thanks

 

ajetrumpet

Banned

Local time

Today, 11:22

Joined

Jun 22, 2007

Messages

5,638

• Apr 3, 2009

• #2

have you tried the transfer spreadsheet method? are you sure you can transfer source tables when they're open? if the underlying table is the subform itself, which it obviously is, it will be open. and where does it debug? what does it say? have you googled the debug statement?

 

L

LB79

Registered User.

Local time

Today, 17:22

Joined

Oct 26, 2007

Messages

505

• Apr 3, 2009

• #3

Does the transfer spreadsheet function not require it to be saved?
I decided to use the acCmdOutputToExcel function so that the user can choose to save or not.
In Excel when using acCmdOutputToExcel (or analyze in Excel manually from the toolbar, it doesn’t require the table to be closed first).
I have tried looking this up in Google yes.
The debug message is a Compile error.

Thanks

 

L

LB79

Registered User.

Local time

Today, 17:22

Joined

Oct 26, 2007

Messages

505

• Apr 3, 2009

• #4

Does the transfer spreadsheet function not require it to be saved?
I decided to use the acCmdOutputToExcel function so that the user can choose to save or not.
In Excel when using acCmdOutputToExcel (or analyze in Excel manually from the toolbar, it doesn’t require the table to be closed first).
I have tried looking this up in Google yes.
The debug message is a Compile error.

Thanks

 

ajetrumpet

Banned

Local time

Today, 11:22

Joined

Jun 22, 2007

Messages

5,638

• Apr 3, 2009

• #5

what line does it highlight? i'm not sure of either of this. i would have to put together an example and compare it. this is still a little big vague as to exactly what you are wanting to do! just a simple export of a table to excel and being able to save it as a seperate file? is THAT what you want?

 

L

LB79

Registered User.

Local time

Today, 17:22

Joined

Oct 26, 2007

Messages

505

• Apr 3, 2009

• #6

It debugs with compile error - expected array, highlights acCmdOutputToExcel

This is the code I am using
DoCmd.RunCommand acCmdOutputToExcel("PMA_tblPMA_Export")

All I want to do is to open the table in Excel without saving it to any location (other then the temp folder like it does when using the Analyze feature).

 

ajetrumpet

Banned

Local time

Today, 11:22

Joined

Jun 22, 2007

Messages

5,638

• Apr 3, 2009

• #7

i do not really know the answer to this LB. but here is a workaround to at least open the file and analyze it:

Code:

Dim str As String

On Error Resume Next

str = "WHERE DO YOU WANT TO STORE THE FILE?"

  DoCmd.TransferSpreadsheet acExport, , "TABLE NAME", str, False
    
Application.FollowHyperlink str, , True

you can also then have a button on the Excel sheet that closes the excel file, returns you to access, and performs an event that will delete the file out of the storage location that you stored it in. that's a bit messy i know, but it IS something. i'm sorry i couldn't help you more with this. I tested the command too, and I get the same array error...