OutputTo Excel

  • Thread starter Thread starter m stroup
  • Start date Start date
M

m stroup

qryMakeDepartmentTable creates a "DepartmentTable" table with
department/employee info.
mcrOutputDeparmentTable exports the table to ManningDoc.xls Worksheet
"DepartmentTable"
Chart1("DepartmentChart") is created from the worksheet.

My problem is that when I use the macro to update the data in Excel, it
creates a new worksheet "DepartmentTable1". I can't find anywhere to set
this differently. The first time I resent info, it updated the worksheet
(and the chart). It hasn't worked since.

Any suggestions?
 
Hi M,

When I want an Excel sheet to be 'live' and automatically update itself -

I tend to use either MSQuery (a klunker of an older method) or ADO (better
but complex for the non-programmer to tackle.)

Rather than continually fighting with an export that you have to keep
performing manually - even if it works at times, it's more elegant to create
a data sheet that updates itself - even if you need to place the data in
sheet2 and reference to it from sheet1 to be able to present a polished,
formatted page with some additional calcs or whatever.

The exact steps to employ MSQuery and the wizard are dependent upon the
version of Excel you are using but it's fairly intuitive and there is a
wizard to help you out. Start with Data, From Access in Excel 2007, or Excel
2000/2003 Data, Get External, New Database Query. Note: You may have to
install MSQuery (it's part of the Office Tools package and some people don't
install those with the application.)

Hope this helps!

Gordon
 
Thanks for replying Gordon. After struggling, I finally went to Excel and
imported the table. It updates very well, as does the chart - with just one
line of code!
Most of my queries need no other manipulations. I do have one report that
requires more manipulation. I will look into MS Query tomorrow. I saw a
query form come up at one point when I was setting up the import. It said it
required Admin privileges. That could be something totally different. I
will look specifically for it tomorrow. Again, thanks.
 
Back
Top