Home > Forum Home > Analyzing Corporate Financial Data > Automate Getting to MS Query Wizard Share

Automate Getting to MS Query Wizard

Excel Help for Automate Getting To Ms Query Wizard in Analyzing Corporate Financial Data

Forum TopicPost Reply Login

Automate Getting To Ms Query Wizard

Rate this:
(3/5 from 3 votes)

I use MS Query Wizard in excel to manipulate data based out of a MS Access File. The process is currently manual, I go to the Excel >> Data Ribbion > Get External Data >> From Other Sources >> From Microsoft Query > Check the "Use the Query Wizard to create/edit queries" >> Select MS Access Database* (in the Databases tab) >> find the file located in a specific path >> hit OK and then the wizard pops up!

Is there anyway to AUTOMATE this whole process? I would like to click a button on the Ribbon which does all this in the background and when it done pops up with the Wizard so i can build my query!

Thanks for your help!
 Posted by on
Replies - Displaying 1 to 2 of 2Order Replies By: Most Recent | Chronological | Highest Rated
Rate this:
(4/5 from 2 votes)
We can export the data from Microsoft Excel to Microsoft Access by using VBA. Below is the VBA code and process which you need to paste in the code module of the file.
  1. Open Excel
  2. Press ALT + F11
  3. VBA Editor will OPEN
  4. Click anywhere in the Project Window
  5. Click on Insert
  6. Click on Module
  7. In the Code Window, Copy and Paste the below mentioned Code:
    Sub ADOFromExcelToAccess()
    ' exports data from the active worksheet to a table in an Access database
    ' this procedure must be edited before use
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=C:\FolderName\DataBaseName.mdb;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records in a table
    r = 3 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
    With rs
    .AddNew ' create a new record
    ' add values to each field in the record
    .Fields("FieldName1") = Range("A" & r).Value
    .Fields("FieldName2") = Range("B" & r).Value
    .Fields("FieldNameN") = Range("C" & r).Value
    ' add more fields if necessary...
    .Update ' stores the new record
    End With
    r = r + 1 ' next row
    Set rs = Nothing
    Set cn = Nothing
    End Sub

Jerry M
 Jerry M
 Posted by on
Rate this:
(3.5/5 from 2 votes)
There are two main options to access the MS Query Wizard more efficiently.
  1. Create a quick access menu item by choosing Office Button > Excel Options > Customize > Data Tab > add the Get External Data From Other Sources item > OK.  You can also click Options when the Data Source dialogue appears to provide a path to the database in order to speed up access when opening this in the future.
  2. Create a VBA macro that executes all of the steps required.  You can record the steps in a blank workbook and then save the workbook as an Excel add-in. The add-in can then be made accessible at all times in Excel.
 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Automate Getting to MS Query Wizard in the