Can A Form Be Used Instead Of Cut/Paste To Excel ?

[carl]

I use these 2 queries and then paste the results into excel...

Q1
SELECT Query3.OCC_Vol, AS BOX_Vol, (/OCC_Vol) AS BOX, [A]/
OCC_Vol AS AMEX, [P]/OCC_Vol AS ARCA, [Z]/OCC_Vol AS BATS, [C]/OCC_Vol
AS CBOE, [W]/OCC_Vol AS C2, /OCC_Vol AS ISE, [Q]/OCC_Vol AS NOM,
[X]/OCC_Vol AS PHLX
FROM Query3
ORDER BY OCC_Vol DESC;

Q2
SELECT Query3.OCC_Vol, AS BOX_Vol, (/OCC_Vol) AS BOX, [A]/
OCC_Vol AS AMEX, [P]/OCC_Vol AS ARCA, [Z]/OCC_Vol AS BATS, [C]/OCC_Vol
AS CBOE, [W]/OCC_Vol AS C2, /OCC_Vol AS ISE, [Q]/OCC_Vol AS NOM,
[X]/OCC_Vol AS PHLX
FROM Query3
ORDER BY OCC_Vol DESC;

The excel spreadsheet looks something like this:

Trade Date 4/27/2011

Result Q1 OCC_Vol BOX_Vol
17,439,351 513,649



Result Q2 underlying OCC_Vol BOX_Vol
SPY 2,212,187 46,832
SLV 1,285,651 46,377
STX 983,631 809
IWM 419,558 6,851

I type in trade date manually.

Can a form be used so I do not need to copy/paste into excel ?

Thanks you in advance.

 

[John W. Vinson]

I use these 2 queries and then paste the results into excel...

Q1
SELECT Query3.OCC_Vol, AS BOX_Vol, (/OCC_Vol) AS BOX, [A]/
OCC_Vol AS AMEX, [P]/OCC_Vol AS ARCA, [Z]/OCC_Vol AS BATS, [C]/OCC_Vol
AS CBOE, [W]/OCC_Vol AS C2, /OCC_Vol AS ISE, [Q]/OCC_Vol AS NOM,
[X]/OCC_Vol AS PHLX
FROM Query3
ORDER BY OCC_Vol DESC;

Q2
SELECT Query3.OCC_Vol, AS BOX_Vol, (/OCC_Vol) AS BOX, [A]/
OCC_Vol AS AMEX, [P]/OCC_Vol AS ARCA, [Z]/OCC_Vol AS BATS, [C]/OCC_Vol
AS CBOE, [W]/OCC_Vol AS C2, /OCC_Vol AS ISE, [Q]/OCC_Vol AS NOM,
[X]/OCC_Vol AS PHLX
FROM Query3
ORDER BY OCC_Vol DESC;

The excel spreadsheet looks something like this:

Trade Date 4/27/2011

Result Q1 OCC_Vol BOX_Vol
17,439,351 513,649



Result Q2 underlying OCC_Vol BOX_Vol
SPY 2,212,187 46,832
SLV 1,285,651 46,377
STX 983,631 809
IWM 419,558 6,851

I type in trade date manually.

Can a form be used so I do not need to copy/paste into excel ?

Thanks you in advance.

I'd use File... Export from the query, or use the TransferSpreadsheet method
in VBA. A Form will not help much - you can copy and pasted from a form, but
it's no easier than doing so from a query datasheet. The TransferSpreadsheet
can write to a named range, which it looks like you'll want.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[carl]

 I use these 2 queries and then paste the results into excel...

Q1
SELECT Query3.OCC_Vol, AS BOX_Vol, (/OCC_Vol) AS BOX, [A]/
OCC_Vol AS AMEX, [P]/OCC_Vol AS ARCA, [Z]/OCC_Vol AS BATS, [C]/OCC_Vol
AS CBOE, [W]/OCC_Vol AS C2, /OCC_Vol AS ISE, [Q]/OCC_Vol AS NOM,
[X]/OCC_Vol AS PHLX
FROM Query3
ORDER BY OCC_Vol DESC;

Q2
SELECT Query3.OCC_Vol, AS BOX_Vol, (/OCC_Vol) AS BOX, [A]/
OCC_Vol AS AMEX, [P]/OCC_Vol AS ARCA, [Z]/OCC_Vol AS BATS, [C]/OCC_Vol
AS CBOE, [W]/OCC_Vol AS C2, /OCC_Vol AS ISE, [Q]/OCC_Vol AS NOM,
[X]/OCC_Vol AS PHLX
FROM Query3
ORDER BY OCC_Vol DESC;

The excel spreadsheet looks something like this:

Trade Date  4/27/2011

   Result Q1        OCC_Vol         BOX_Vol
            17,439,351      513,649

Result Q2   underlying       OCC_Vol         BOX_Vol
   SPY      2,212,187       46,832
   SLV      1,285,651       46,377
   STX      983,631         809
   IWM      419,558         6,851

I type in trade date manually.

Can a form be used so I do not need to copy/paste into excel ?

Thanks you in advance.

I'd use File... Export from the query, or use the TransferSpreadsheet method
in VBA. A Form will not help much - you can copy and pasted from a form, but
it's no easier than doing so from a query datasheet. The TransferSpreadsheet
can write to a named range, which it looks like you'll want.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

Thanks John.

Would you be able to show me how I would do this ?

For example, the file I want to save the results to is C:\market.xls.

In that spreadsheet, I would like the results from Q1 to be in range
C3:M4 and for Q2 B8:M3000.

Also. If it is possible, I would like the cell format to be 2 decimal
percentage in E4:M3000.

If this question is posted in the wrong area could you tell me what
area a post like this belongs.

Thanks again.

 

[John W. Vinson]

Thanks John.

Would you be able to show me how I would do this ?

For example, the file I want to save the results to is C:\market.xls.

In that spreadsheet, I would like the results from Q1 to be in range
C3:M4 and for Q2 B8:M3000.

Also. If it is possible, I would like the cell format to be 2 decimal
percentage in E4:M3000.

If this question is posted in the wrong area could you tell me what
area a post like this belongs.

I have very little experience interfacing with Excel.

You may be aware that Microsoft stopped supporting these newsgroups last year.
They are no longer hosted on Microsoft's servers, or accessible from the
"help" feature of the program; as a result traffic is way, way down, both in
terms of people asking questions and people answering. See my .sig for some
alternative forums. My good friends at UtterAccess are very expert and may be
able to walk you through the process.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[carl]

I have very little experience interfacing with Excel.

You may be aware that Microsoft stopped supporting these newsgroups last year.
They are no longer hosted on Microsoft's servers, or accessible from the
"help" feature of the program; as a result traffic is way, way down, bothin
terms of people asking questions and people answering. See my .sig for some
alternative forums. My good friends at UtterAccess are very expert and may be
able to walk you through the process.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Thanks.