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? 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, which has a macro behind it, which when clicked imports the data from the query into a specified region in the Excel spreadsheet where the button is located.

Thank you for any help.

MH

 

[comaboy]

Have a look at this code for an Access Form. Place a command button of the form and paste the code in it's OnClick event.

Note, this code is incomplete but it should get your going...


Private Sub cmdExport_OnClick()
Dim oFILE as String
oFILE = "c:\export.xls" ' you should get this from the user!
If Dir(oFILE) <> &quot;&quot; Then
If MsgBox(oFILE & &quot; already exists. Do you want to replace this file?&quot;, vbInformation + vbYesNo) <> vbYes Then
Exit Sub
Else
Kill oFILE
End If
End If
' change &quot;qryExport&quot; to your Query name
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, &quot;qryExport&quot;, oFILE, True
Dim xlApp As Object
Set xlApp = CreateObject(&quot;Excel.Application&quot
xlApp.Visible = True
xlApp.Workbooks.Open Filename:=oFILE
End Sub