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

MS Query Wizard Automated

Excel Help for Ms Query Wizard Automated in Analyzing Corporate Financial Data

Forum TopicLogin

Ms Query Wizard Automated

Rate this:
(4/5 from 2 votes)
Confused 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
View Full Post

Find relevant Excel templates and add-ins for MS Query Wizard Automated in the