Analyze in Excel Option

[Imdabaum]

So here is a fun one. I have two forms that run off the same query. I do this because there is one form where users can view all invoices made. And then there is a subform that shows only the invoices related to the main form!ID. The user wants to use the Analyze in Excell tool for selected invoice items from the subform it imports all data into Excel. The funny thing is if I open the Main Invoice form that shows all, I can select which ones I want and only those get exported. Anyone know how I can get the same functionality in the sub form?

[OBP]

Surely the query that you create for the "Export" will control what gets transferred?

[Imdabaum]

That's what I thought. But they are the same query just with different names (I didn't make this application just maintaining it. I would have used the same query for both items).

But I actually tried that and something in the functionality of it changes when placed in a subform. I will try and include the output for what is happening maybe you can get a better idea of what is going on. I only includeded an image of the invoice analysis, but the image is the same just with the properties window highlighed and the same text is selected... the output is Properties.xls while the output from the analysis on the Invoice form is frmInvoice.xls.

[Imdabaum]

** If anyone knows the true purpose of Analyze in Excell from the Tools->Office Links toolbar please let me know if this is not what it does. Cause I can't get it to work for this subform.--**

From everything I have tried with the Analyze in Excell tool it seems to be the equivalent of copying data from access and pasting it into an Excel spreadseet. So here is what I came up with... I just need help with 1. Create the file if it doesn't exist. 2. Overwrite all information that was previously stored or offer option to save as new fileName.


Dim xApp As Object
Dim ExFile As Object
Dim fn As String

RunCommand (acCmdCopy) 'Copy the fields that are selected in Access Datasheet view.

fn = "C:\Documents and Settings\Invoice.xls"

Set xApp = CreateObject("Excel.Application")
Set ExFile = xApp.Workbooks.Open(fn)
xApp.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
xApp.UserControl = True
RunCommand (acCmdPaste)

Exit_cmdExcellAnalysis_Click:
Exit Sub
Err_cmdExcellAnalysis_Click:
'Check what kind of error then output message or create the file that didn't exist
MsgBox Err.Description
Resume Exit_cmdExcellAnalysis_Click


End Sub

[Imdabaum]

Does anyone use this built in Analyze in Excel function?

[Norie]

Why don't you just use TransferSpreadsheet?

I don't think that option actually analyses anything, it just exports data to Excel.

[Imdabaum]

That's what I thought. So how can I just transfer the data ta=hat is selected/highlighted?

[Norie]

How exactly is the data being highlighted/selected?

If the user is just highlighting with the cursor the records of interest I know of no way to export what they've selected to Excel.

[Imdabaum]

How exactly is the data being highlighted/selected?

If the user is just highlighting with the cursor the records of interest I know of no way to export what they've selected to Excel.


Well holding shift and dragging the mouse accross the fields that you want to export. Then the user was clicking the office tools--> analyze in excel

[Norie]

Well I really don't think you'll be able to export to Excel like that.

As far as I'm aware the analyze option will just work with the current object displayed.

And it will export all records and fields in that object regardless of user selection.

[Imdabaum]

Thanks Norie. I had never seen it done, but it does work when following this same procedure through a lone form linked to the same query. The user says that the subform use to do it... I have no idea how they managed it, I'll just tell her I can't fix it or and to copy and paste them into excel for now.

[Norie]

Well I think the user might be dreaming.:)

I know you can export to Excel based on criteria supplied by the user, but I've never seen it done in this way.

One way I can think of doing it would be to use a continuous form where each record has a checkbox.

The user checks the records they want to export and then you use TransferSpreadsheet to export a query which has criteria based on the field the checkbox is bound to.

[Imdabaum]

Is there a way I can detect what is highlighted and then export that with a button event? I am pretty sure Excel has some functionality, but I could be wrong.Just trying to see if there is some way I can mimic the idea that the user wants for this application.