Exporting a query to excel using outputTo and adding a title

[kscheu via AccessMonster.com]

I am using OutputTo to export a query to excel, prompt the user to save as
and then open the excel file. See below:
DoCmd.OutputTo acOutputQuery, "q701ExportFields", acFormatXLS, , True 'Opend
Excel Automatically

Now I want to add a row to the opened spreadsheet at the top and add a title.
How do I accomplish this? I am new to excel formatting, but I created a
macro and the code to do this is:
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Title"

I get an error when I run this. Do I have to set an object variable and
select the active sheet if I use the OutputTo method as described above?

Thanks,
Karen

--
Message posted via http://www.accessmonster.com

[Ron2005]

Try something like this

I use the following and it seems to work fine:

reportfilename = ReportDirectory & reportfilename
Set es = CreateObject("Excel.Application")
es.Visible = False
es.Workbooks.Open FileName:=reportfilename
es.Sheets("q701ExportFields").Select ' or whatever the tab ends up
being named
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Title"

' at this point you can do almost anything that you can do in a macro
(including running an excel macro
es.ActiveWorkbook.Save
es.ActiveWorkbook.Close (False)
es.Application.Quit

=============================

I set this one up to not show what is being done but if you take out
the es.visible it will show. And if you take out the close and quit it
will remain open.
Ron

[kscheu via AccessMonster.com]

Thanks for your response. The only problem I have is that I would have to
handle the naming of the file and the directory. If I use the OutputTo
method, the save as dialog comes up and the user can save the file wherever
they want. I may be missing something simple, but I just don't know enough
about the excel export. I was hoping to still use the OutputTo method and
simply add a title to the opened spreadsheet. Do you know if that is
possible?

Thanks.
Ron2005 wrote:

Try something like this

I use the following and it seems to work fine:

reportfilename = ReportDirectory & reportfilename

Set es = CreateObject("Excel.Application")

es.Visible = False

es.Workbooks.Open FileName:=reportfilename

es.Sheets("q701ExportFields").Select ' or whatever the tab ends up
being named
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Title"

' at this point you can do almost anything that you can do in a macro
(including running an excel macro

es.ActiveWorkbook.Save
es.ActiveWorkbook.Close (False)

es.Application.Quit

=============================

I set this one up to not show what is being done but if you take out
the es.visible it will show. And if you take out the close and quit it
will remain open.

Ron

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200604/1

[Ron2005]

The problem there is that if they are in that sense selecting where to
save the file to, you have to somehow find out where that file is in
order for you to open it and do whatever is necessary.

That is an area that I have not gotten into so I am not sure if it is
doable.

A thought,
In the code, it is using a save. Instead of doing that, just before
that change the visible to yes and then use saveas instead of save.
That may give you the what you want with you getting a chance to do the
extra stuff before it is saved. I would still do the copy and opening
the copy before hand so that they don't accidentially overwrite your
master with something that won't work properly.

Ron