Update data in Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two reports in Access one is and output to and the other is exported.
I am trying to figure out which one to use so my columns (data) will be
updated when the user clicks the automated button. I noticed the export to
excel does not update but the output to does. I have two questions

1. Is there a easy way to update the data based of Access report export?
2. If not I can use the output to but I need to find out how to
automatically align the columns when the new data is entered.

Am I taking the hard way or is there an easier way to export updated reprts
to Excel?

Please help I am a newbe in Excel.
 
TKM

Go into Access from Excel via Data>Get External Data>New database query...

Once you have set this up, you can take the same menu options and just press
the ! mark and the data will refresh with the up to date data

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

(e-mail address removed)
www.nickhodge.co.uk
 
I dont get what you are saying. Go into Access from Excel? I went into Excel
and dont see a Get External Data anywhere in the menu. What I am doing is
exporting a report into Excel like this
et SpdSheet = CreateObject("Excel.Application")
SpdSheet.Workbooks.Open ("M:\documents and
settings\tromil\report#3qry2of2.xls")
SpdSheet.Visible = True

Then the report opens in Excel. But when the user pushes the button again
she gets the same data with no update. I am trying to find a way to automate
this to update when the user clicks the button and not keep showing the same
report.

Any suggestions?
 
You have an automation solution in VBA.

I was saying go into Excel (Options: Data>Import External Data>New Database
Query), once you have setup this ODBC connection to an Access Table/Query,
you will be able to take the same option and simple press the ! to refresh
the data. It may be better to do this in a pivot table in Excel (Data>Pivot
table report or chart), but not knowing what you want the Excel portion to
look like it's difficult to say. In each case a simple refresh will always
reflect the new data. You can even set it to refresh automatically on
opening the Excel file.

If it is an Access report, hen this will be derived from an Access
query/table, so you should be able to replicate this in Excel

You can also set up Excel to retain formatting, fill down formulae, as data
gets added taken away, etc

No code needed this way

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

(e-mail address removed)
www.nickhodge.co.uk
 
OK got it. How would I get it to refresh? Is there a button I can push then
record the macro and put it in my code?b Thanks again for your help!
 
Back
Top