Data From Access to Excel

[orna]

Hi

I have an Excel file that has a link to an Access query.
The parameters for that query are fields in access form.
I'd like to transfer those fields to Excel as well as the query results.

In what way ?

TIA

 

[dhookom]

You can pull field values from Access into Excel in the same way that you create a "link to an Access query". You might need to further define your parameters.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[orna]

I tried that with no success.
Can you guide me how to do it?

Thanks!

 

[dhookom]

Please tell us how you originally linked the query that has parameters from a form. What is the SQL of the query?

When you state "no success", it doesn't help us much.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[orna]

Link the query Access from Excel:
Date -> Import External Data -> Import Data -> New Source ->
ODBC DSN -> MsAccess Database -> Selec my Access DB ->
Select My Query

The query:
TRANSFORM Sum(Format([SumAmnt],'0')) AS Tot " & _
"SELECT PeriodDebit.BranchDscr AS A, " & _
"Sum(Format([SumAmnt],'0')) AS B " & _
"FROM PeriodDebit WHERE PeriodDebit.Month in (""" & MonthRange & """)" & _
"GROUP BY PeriodDebit.BranchDscr " & _
"PIVOTPeriodDebit.CompanyName;"

"MonthRange" is the parameter that is built from fields in Access form.
Want to link/export those 2 fields to Excel.

Hope it's clear now.

 

[dhookom]

I don't see how this could work without writing code to set the SQL property of "the query". How does MonthRange get defined and when?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[easyit]

Orna,

I'm sure that there are several soluitions to your problem, but what I've done to solve the similar problem I had was the folowing:

In Access I create the query in VBA using CreateQueryDef. The VBA code allows me to insert formfields in the sql statement.
Now I can generate the excelsheet using the created query.

You could use the same technique, if the querynames are either passed to Excel or are known in advance.

I use access to generate everything; queries, VBA code to be inserted into a excelfile, starting of VBA code in excel...
Excel is just for reporting purposes.


EasyIT

 

[orna]

I surely explained myself very poorly.
The query (Crosstab) is created in VBA using CreateQueryDef,
and then the Excel file is called from Access, and the query is displayed fine.
The parameters that i need to transfer to Excel are just for display.

 

[PHV]

the Excel file is called from Access
How do you do that ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[orna]

With the shell command

 

[PHV]

Do it with OLE Automation instead and then simply write MonthRange's value in any Cell of your choice.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[orna]

Thank you all!!!

I'll take a look tomorrow at the OLE Automation.