Updating excel worksheet from access query
There's data in your company's Access database that you need to analyze, but the PC you work with does not have Access. You can use Excel's MS Query Wizard to come up with the answers you need.For example, suppose you need a list of customers with outstanding balances, and the data is in the Customer table of your Sales database.Put your cursor inside the contents of the database query in your spreadsheet; that should cause the External Data toolbar to pop up. Select an Excel cell on the same sheet outside the database query rangesuch as H10.(If you fail to do this, and your cursor happens to be inside the data range, the macro wont work properly.) Then turn on the macro recorder by clicking on Tools, Macro and Record New Macro, engaging the Record Macro dialog box.If the toolbar doesnt pop up, do one of the following: Select either Tools, Macro, Stop Recording or View, Toolbars and Stop Recording. Now that weve recorded a macro, we need to attach it to a button in the spreadsheet.Though any type of button will work, well add one from Excels Forms toolbar. Left-click on the button (its circled) and then release it.
Paste the following code into the class module (double-click it in the PE to see the code pane): [sourcecode language=”vb”]Option Explicit Public With Events My Query As Query Table Private Sub My Query_After Refresh(By Val Success As Boolean) If Success Then Msg Box “Query has been refreshed.” End Sub Private Sub My Query_Before Refresh(Cancel As Boolean) If Msg Box(“Refresh query?
For this example we will rename it (select the class module, press F4) to “cls Query“.
The class name is referred to in code later on in this post.
I personally prefer to prepare such Table in Excel and then use Linked Table because it is more flexible to maintain the table in Excel.
Wyman is human resources professional specialized in implementation of HR information system.
Search for updating excel worksheet from access query:
Tip: Take a look at the Transfer Spreadsheet method of the Do Cmd object and use ac Spreadsheet Type Excel12Xml.