'Import Wizard' in Excel

[MHUK]

Hi

Does anyone know how to create a macro in VB in MS Excel in order to import a column of data from a query in MS Access into a specified region in an Excel spreadsheet? I do not know VB at all so any help with the basic code for the macro would be really appreciated. I know how to import external data using MS Query however it is a long-winded process...I really need a simple way, ideally using a command button in Excel, which has a macro behind it (this is where I need the help), which when clicked imports the data from the query in Access into a specified region.

Is there such a thing as an Import Wizard that could do the importing of data in a quick and simple way, via use of a command button?

Thank you for any help.

MH

 

[TelcoBT]

Simple export into Excel wouldn't be a problem, where a new excel file is created and the data 'streamed in by Access', but exporting to a specified region, would, I think,need VBA , as I think you would need to point to each cell in the region into which you want to add the information.

 

[Molby]

Probably not the best way to do this, but an easy way is to set up the Query you want to run in a macro within Access (Using OpenQuery), this can then be exported to an excel spreadsheet using the same macro(OutputTo). You can then have VBA import the data from the new workbook into your workbook. You can record yourself doing this in the Macro recorder to get the rest of the code.

Use the code below to run the access side from Excel.

Set acApp = CreateObject("Access.Application&quot
acApp.Visible = False
acApp.OpenCurrentDatabase ("C:\Database.mdb&quot
acApp.DoCmd.RunMacro "Macroname"

Once you put it all together in Excel, you can link the code into your button.

Regards,

Ian