Export to Excel - but not with TransferSpreadsheet function

[hannes]

I would like to display my result on a dynamic SQL query not in Access rather
directly in Microsoft Excel. However I do not like to safe the file or ask
the user where to store it. When I run the query and display it the simple
solution now is to click the "Export to Microsoft Excel" office link. This
would do what I need, however I would like to do this automatically. Does
anyone has an idea how this works?

 

[hannes]

this is very helpful already. thank you very much. two follow up questions:
1) Would you also know how I can add for example an auto filter, freeze the
panes, autoformat the columns?

2) I tried also to auto-format the columns in a query output. However this
seems not to work. My columns change as they are based on different
selections?

 

[Dale Fye]

In addition to posting to the Excel groups, you might want to try the Office
Automation newsgroups. There are lots of examples there of automating Excel
from Access.

VBA gives you the ability to export the results of a query to an Excel
spreadsheet, but you can also then open the spreadsheet through automation,
and VBA to set properties, and formatting of the Excel spreadsheet.

Generally, the way I start this is to output the file to Excel, then I open
the Excel file, and record a macro so I can see how Excel does it, when I
format a block of data. Then, I go back to access and copy/modify the EXCEL
macro into Access VBA. Although not require, it helps if you initially set a
reference to the Excel Object model, since this provides intellisense. Once
I have the automation working the way I want, I generally remove the
reference to Excel and replace the dimension statemets to specific Excel
object types (Workbook, Worksheet, Cell) with assignments as Object. This
late binding ensures that if a user doesn't have the same version of Access
that you are using, or has a different version of Excel, that the code will
be more likely to run on their version.


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[hannes]

thanks both of you, I will post in in the different threat and sorry that I
misused this one.

 

[Klatuu]

You could do all that, but it would involve a lot of VBA code using
Automation to do the formatting of the sheet. You could also load the query
data into the sheet at the same time. And, you would not have to save the
spreadsheet unless the user actually wanted to.

If you are proficient in VBA, it wouldn't be that difficult, just time
consuming.

 

[Klatuu]

Sorry, Chris, but that is not correct. Most of the people in the Excel group
would not know how to use Automation to format a spreadsheet from Access
using VBA.

It can be done if you understand using automation and how to manipulate the
Excel object model from within Access. Anything that can be done either
manually or programmatically in Excel can be done from within Access. That
includes formatting, creating graphs, subtotals, add formulas, etc.
--
Dave Hargis, Microsoft Access MVP

Access forms are capable of these specific features, but it sounds as if you
want to bypass Access's capabilities and send the data to a spreadsheet you
can make pretty. If that's the case, then you should post your questions in
the Excel forums about automating the autoformatting, freezing columns and
filtering the spreadsheet data, where the Excel experts can better help you.

Autoformatting in Access has a different meaning than in Excel.
Autoformatting in Access formats the appearance of the form so that all forms
in the application are standardized. Colors, text boxes, labels, fonts, and
so forth.

If the columns in your query change every time you run the query, then you
are bound to run into trouble by trying a one size fits all approach.


Chris
Microsoft MVP

this is very helpful already. thank you very much. two follow up questions:
1) Would you also know how I can add for example an auto filter, freeze the
panes, autoformat the columns?

2) I tried also to auto-format the columns in a query output. However this
seems not to work. My columns change as they are based on different
selections?

If you use the "Analyze it with Microsoft Excel" function, it will
automatically create a spreadsheet file in your default database folder and

[quoted text clipped - 21 lines]

would do what I need, however I would like to do this automatically. Does
anyone has an idea how this works?

 

[Klatuu]

Good approach, Dale. The only thing different I would suggest is rather than
exporting the data to Excel would be to create the workbook using Automation
then use the CopyFromRecordset method. The advantage is you can place the
data where ever you want it rather than having in start in A1.

Another technique I use which improves performance and keeps the user from
messing up the spreadsheet while it is being built is to set these properties:

xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False

That will cause the Excel sheet to be hidden and saves the time of Excel
repainting the screen repeatedly. Then, when everything is done, set them
back to true and the spreadsheet appears.

Also, as the OP wanted, it has yet to be saved to a file.

 

[Dale Fye]

Dave,

I've never used the CopyFromRecordset method. I'll have to take a look.

Dale

 

[Dale Fye]

I don't think anyone said you had used the wrong group, or that you had
"misused" this one.

I made my recommendation about the Office Automation newsgroups because the
guys and gals that frequent those groups generally use automation to work in
multiple office applications, from within a single VBA environment. And they
really know their stuff.

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Klatuu]

Here is an example:

'Get Plan Data
strSQL = "SELECT Sum(Plan_res_export.SumOfJAN) AS JAN, " _
& "Sum(Plan_res_export.SumOfFEB) AS FEB, " _
& "Sum(Plan_res_export.SumOfMAR) AS MAR,
Sum(Plan_res_export.SumOfAPR) AS APR, " _
& "Sum(Plan_res_export.SumOfMAY) AS MAY,
Sum(Plan_res_export.SumOfJUN) AS JUN, " _
& "Sum(Plan_res_export.SumOfJul) AS Jul,
Sum(Plan_res_export.SumOfAUG) AS AUG, " _
& "Sum(Plan_res_export.SumOfSEP) AS SEP,
Sum(Plan_res_export.SumOfOCT) AS OCT, " _
& "Sum(Plan_res_export.SumOfNOV) AS NOV,
Sum(Plan_res_export.SumOfDec) AS Dec " _
& "FROM Plan_res_export " _
& "GROUP BY Plan_res_export.BillCat, Plan_res_export.ITM,
Plan_res_export.[SubTask] " _
& "HAVING (((Plan_res_export.BillCat)= '" & strResource & "') AND " _
& "((Plan_res_export.ITM)= '" & strItm & "') AND " _
& "((Plan_res_export.[SubTask])= '" & strRecurring & "'));"
If strItmPM = "PM" Then
strSQL = Replace(strSQL, "Plan_res_export.ITM",
"Plan_res_export.[Program Manager]")
End If
Set rstPlan = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)


If rstPlan.EOF Then
xlSheet.Range("B29", "M29").Value = 0
Else
xlSheet.Cells(29, 2).CopyFromRecordset rstPlan
End If

 

[Dale Fye]

Thanks, Dave.

I like that. When it does the copy from recordset, does it overwrite the
cells formatting, or does it leave it intact?

Here is an example:

'Get Plan Data
strSQL = "SELECT Sum(Plan_res_export.SumOfJAN) AS JAN, " _
& "Sum(Plan_res_export.SumOfFEB) AS FEB, " _
& "Sum(Plan_res_export.SumOfMAR) AS MAR,
Sum(Plan_res_export.SumOfAPR) AS APR, " _
& "Sum(Plan_res_export.SumOfMAY) AS MAY,
Sum(Plan_res_export.SumOfJUN) AS JUN, " _
& "Sum(Plan_res_export.SumOfJul) AS Jul,
Sum(Plan_res_export.SumOfAUG) AS AUG, " _
& "Sum(Plan_res_export.SumOfSEP) AS SEP,
Sum(Plan_res_export.SumOfOCT) AS OCT, " _
& "Sum(Plan_res_export.SumOfNOV) AS NOV,
Sum(Plan_res_export.SumOfDec) AS Dec " _
& "FROM Plan_res_export " _
& "GROUP BY Plan_res_export.BillCat, Plan_res_export.ITM,
Plan_res_export.[SubTask] " _
& "HAVING (((Plan_res_export.BillCat)= '" & strResource & "') AND "
_
& "((Plan_res_export.ITM)= '" & strItm & "') AND " _
& "((Plan_res_export.[SubTask])= '" & strRecurring & "'));"
If strItmPM = "PM" Then
strSQL = Replace(strSQL, "Plan_res_export.ITM",
"Plan_res_export.[Program Manager]")
End If
Set rstPlan = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot,
dbReadOnly)


If rstPlan.EOF Then
xlSheet.Range("B29", "M29").Value = 0
Else
xlSheet.Cells(29, 2).CopyFromRecordset rstPlan
End If

--
Dave Hargis, Microsoft Access MVP

Dave,

I've never used the CopyFromRecordset method. I'll have to take a look.

Dale

 

[Klatuu]

The cell value and formatting are different properties, so modifying the
value of the cell has no effect on the formatting.
--
Dave Hargis, Microsoft Access MVP

Thanks, Dave.

I like that. When it does the copy from recordset, does it overwrite the
cells formatting, or does it leave it intact?

Here is an example:

'Get Plan Data
strSQL = "SELECT Sum(Plan_res_export.SumOfJAN) AS JAN, " _
& "Sum(Plan_res_export.SumOfFEB) AS FEB, " _
& "Sum(Plan_res_export.SumOfMAR) AS MAR,
Sum(Plan_res_export.SumOfAPR) AS APR, " _
& "Sum(Plan_res_export.SumOfMAY) AS MAY,
Sum(Plan_res_export.SumOfJUN) AS JUN, " _
& "Sum(Plan_res_export.SumOfJul) AS Jul,
Sum(Plan_res_export.SumOfAUG) AS AUG, " _
& "Sum(Plan_res_export.SumOfSEP) AS SEP,
Sum(Plan_res_export.SumOfOCT) AS OCT, " _
& "Sum(Plan_res_export.SumOfNOV) AS NOV,
Sum(Plan_res_export.SumOfDec) AS Dec " _
& "FROM Plan_res_export " _
& "GROUP BY Plan_res_export.BillCat, Plan_res_export.ITM,
Plan_res_export.[SubTask] " _
& "HAVING (((Plan_res_export.BillCat)= '" & strResource & "') AND "
_
& "((Plan_res_export.ITM)= '" & strItm & "') AND " _
& "((Plan_res_export.[SubTask])= '" & strRecurring & "'));"
If strItmPM = "PM" Then
strSQL = Replace(strSQL, "Plan_res_export.ITM",
"Plan_res_export.[Program Manager]")
End If
Set rstPlan = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot,
dbReadOnly)


If rstPlan.EOF Then
xlSheet.Range("B29", "M29").Value = 0
Else
xlSheet.Cells(29, 2).CopyFromRecordset rstPlan
End If

--
Dave Hargis, Microsoft Access MVP

Dave,

I've never used the CopyFromRecordset method. I'll have to take a look.

Dale
--

email address is invalid
Please reply to newsgroup only.



:

Good approach, Dale. The only thing different I would suggest is
rather than
exporting the data to Excel would be to create the workbook using
Automation
then use the CopyFromRecordset method. The advantage is you can place
the
data where ever you want it rather than having in start in A1.

Another technique I use which improves performance and keeps the user
from
messing up the spreadsheet while it is being built is to set these
properties:

xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False

That will cause the Excel sheet to be hidden and saves the time of
Excel
repainting the screen repeatedly. Then, when everything is done, set
them
back to true and the spreadsheet appears.

Also, as the OP wanted, it has yet to be saved to a file.
--
Dave Hargis, Microsoft Access MVP


:

In addition to posting to the Excel groups, you might want to try the
Office
Automation newsgroups. There are lots of examples there of
automating Excel
from Access.

VBA gives you the ability to export the results of a query to an
Excel
spreadsheet, but you can also then open the spreadsheet through
automation,
and VBA to set properties, and formatting of the Excel spreadsheet.

Generally, the way I start this is to output the file to Excel, then
I open
the Excel file, and record a macro so I can see how Excel does it,
when I
format a block of data. Then, I go back to access and copy/modify
the EXCEL
macro into Access VBA. Although not require, it helps if you
initially set a
reference to the Excel Object model, since this provides
intellisense. Once
I have the automation working the way I want, I generally remove the
reference to Excel and replace the dimension statemets to specific
Excel
object types (Workbook, Worksheet, Cell) with assignments as Object.
This
late binding ensures that if a user doesn't have the same version of
Access
that you are using, or has a different version of Excel, that the
code will
be more likely to run on their version.


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

this is very helpful already. thank you very much. two follow up
questions:
1) Would you also know how I can add for example an auto filter,
freeze the
panes, autoformat the columns?

2) I tried also to auto-format the columns in a query output.
However this
seems not to work. My columns change as they are based on different
selections?

:

If you use the "Analyze it with Microsoft Excel" function, it
will
automatically create a spreadsheet file in your default database
folder and
open it for the user, so the user will have to delete that file
afterwards.

Here is an example of how to call a stored query (QueryName) and
automatically export it to Excel without TransferSpreadsheet:

DoCmd.OpenQuery "QueryName", acViewNormal
RunCommand acCmdOutputToExcel

Once the user is finished analyzing the spreadsheet, the user can
press a
button that contains the code to delete the file:

Kill Application.GetOption("Default Database Directory") &
"\QueryName.
xls"


Chris
Microsoft MVP



hannes wrote:
I would like to display my result on a dynamic SQL query not in
Access rather
directly in Microsoft Excel. However I do not like to safe the
file or ask
the user where to store it. When I run the query and display it
the simple
solution now is to click the "Export to Microsoft Excel" office
link. This
would do what I need, however I would like to do this
automatically. Does
anyone has an idea how this works?

 

[aaron_kempf]

Warning!

this Chris guy is not credible.
He does not know the basics of MS Access.

-Aaron

Sorry, Dave, but the Excel experts would offer a non-Automation solution
based on an Excel spreadsheet which would look simpler and easier to maintain,
as it would avoid having to upgrade two parts of the application (both Access
and Excel, instead of just Excel) whenever new versions of MS Office come out.
The data would be stored in a Jet or an ACE database file, and Excel would
query the data and format it as desired, so no need to worry about upgrading
any Access VBA Automation code for Office 14 and beyond.

Chris
Microsoft MVP

Sorry, Chris, but that is not correct.  Most of the people in the Excelgroup
would not know how to use Automation to format a spreadsheet from Access
using VBA.

It can be done if you understand using automation and how to manipulate the
Excel object model from within Access.   Anything that can be done either
manually or programmatically in Excel can be done from within Access.  That
includes formatting, creating graphs, subtotals, add formulas, etc.

Access forms are capable of these specific features, but it sounds as if you
want to bypass Access's capabilities and send the data to a spreadsheetyou

[quoted text clipped - 26 lines]

would do what I need, however I would like to do this automatically.. Does
anyone has an idea how this works?

 

[Klatuu]

Aaron, don't go there.
All MVPs know about you. I will avoid embarresing you if you just stay out
of it.
--
Dave Hargis, Microsoft Access MVP

Warning!

this Chris guy is not credible.
He does not know the basics of MS Access.

-Aaron

Sorry, Dave, but the Excel experts would offer a non-Automation solution
based on an Excel spreadsheet which would look simpler and easier to maintain,
as it would avoid having to upgrade two parts of the application (both Access
and Excel, instead of just Excel) whenever new versions of MS Office come out.
The data would be stored in a Jet or an ACE database file, and Excel would
query the data and format it as desired, so no need to worry about upgrading
any Access VBA Automation code for Office 14 and beyond.

Chris
Microsoft MVP

Sorry, Chris, but that is not correct. Most of the people in the Excel group
would not know how to use Automation to format a spreadsheet from Access
using VBA.

It can be done if you understand using automation and how to manipulate the
Excel object model from within Access. Anything that can be done either
manually or programmatically in Excel can be done from within Access. That
includes formatting, creating graphs, subtotals, add formulas, etc.
Access forms are capable of these specific features, but it sounds as if you
want to bypass Access's capabilities and send the data to a spreadsheet you
[quoted text clipped - 26 lines]
would do what I need, however I would like to do this automatically.. Does
anyone has an idea how this works?

 

[Klatuu]

That is actually a pretty good solution; however, using late binding, there
should be no problems when either Access or Excel is upgraded.
--
Dave Hargis, Microsoft Access MVP

Sorry, Dave, but the Excel experts would offer a non-Automation solution
based on an Excel spreadsheet which would look simpler and easier to maintain,
as it would avoid having to upgrade two parts of the application (both Access
and Excel, instead of just Excel) whenever new versions of MS Office come out.
The data would be stored in a Jet or an ACE database file, and Excel would
query the data and format it as desired, so no need to worry about upgrading
any Access VBA Automation code for Office 14 and beyond.

Chris
Microsoft MVP

Sorry, Chris, but that is not correct. Most of the people in the Excel group
would not know how to use Automation to format a spreadsheet from Access
using VBA.

It can be done if you understand using automation and how to manipulate the
Excel object model from within Access. Anything that can be done either
manually or programmatically in Excel can be done from within Access. That
includes formatting, creating graphs, subtotals, add formulas, etc.

Access forms are capable of these specific features, but it sounds as if you
want to bypass Access's capabilities and send the data to a spreadsheet you

[quoted text clipped - 26 lines]

would do what I need, however I would like to do this automatically. Does
anyone has an idea how this works?