[FAQ's: OD] How do I transfer data between Access and Excel?

Printable View

Apr 27th, 2006, 12:48 AM
RobDog888

[FAQ's: OD] How do I transfer data between Access and Excel?
Using the TransferSpreadsheet method of the DoCmd object is the easiest method for transfering data between Access and Excel.

It only takes a few arguments.

Expression.DoCmd.TransferSpreadsheet TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA

TransferType: (acExport, acImport, acLink )
Specifies the direcion of the transfer.

SpreadsheetType: (acSpreadsheetTypeExcel3, acSpreadsheetTypeExcel4, acSpreadsheetTypeExcel5, acSpreadsheetTypeExcel7, acSpreadsheetTypeExcel8, acSpreadsheetTypeExcel9, acSpreadsheetTypeLotusWK1, acSpreadsheetTypeLotusWK3, acSpreadsheetTypeLotusWK4)
Specifies the filetype and version. This depends on the filters and converters that you may have optionally selected during Access/Office installation.

TableName: (Variant - Access Object)
This specifies the Access Object that will be used as the source/destination depending upon the first argument supplied.
It can be a Table or Query to be Exported from or just a Table to be imported to.

FileName: (Variant - filepath\name)
Depending on the first argument this will be either the file to export the database objects conetents into or the source file for use in an import operation.

HasFieldNames: (Variant - True/False)
Specifies that the first row of the spreadsheet as field names when importing or linking. Use False to identify the first row of the spreadsheet as normal data.

Range: (Variant - Range)
A valid range of cells or the name of a range in the spreadsheet. Applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank.

UseOA: (Variant)
This argument is not supported.

Access 2003 VBA Export Code Example:
Export an Access Table to an Excel Sheet:
VB Code:
```
'Behind an Access VBA Form
Private Sub Command1_Click()
    Application.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", "C:\Book1.xls", False, "Sheet2$"
End Sub
```
Access 2003 VBA Import Code Example:
Import an Excel Sheet to an Access Table:
VB Code:
```
'Behind an Access VBA Form
Private Sub Command1_Click()
    Application.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Table1", "C:\Book1.xls", False, "A1:D8"
End Sub
```

Re: [FAQ's: OD] How do I transfer data between Access and Excel?

VB.NET 2003 Import Code Example:

VB Code:

'Import all of Sheet2 from Book1.xls
Option Explicit On 
Option Strict On
 
Imports Microsoft.Office.Interop
 
Public Class Form1
    Inherits System.Windows.Forms.Form
 
    Private moApp As Access.Application
    Private mbKillMe As Boolean
 
#Region " Windows Form Designer generated code "
 
    Public Sub New()
        MyBase.New()
 
        'This call is required by the Windows Form Designer.
        InitializeComponent()
 
        'Add any initialization after the InitializeComponent() call
 
    End Sub
 
    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub
 
    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer
 
    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Button1 As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.Button1 = New System.Windows.Forms.Button
        Me.SuspendLayout()
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(64, 24)
        Me.Button1.Name = "Button1"
        Me.Button1.TabIndex = 0
        Me.Button1.Text = "Button1"
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(180, 66)
        Me.Controls.Add(Me.Button1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.ResumeLayout(False)
 
    End Sub
 
#End Region
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            moApp = DirectCast(GetObject(, "Access.Application"), Access.Application)
            mbKillMe = False
        Catch ex As Exception
            If TypeName(moApp) = "Nothing" Then
                moApp = DirectCast(CreateObject("Access.Application"), Access.Application)
                mbKillMe = True
            Else
                MessageBox.Show(ex.Message, "VB/Office Guru™ Access Demo", _
                MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            End If
        End Try
        If mbKillMe = False Then
            If moApp.CurrentDb.Name <> "D:\RobDog888.mdb" Then
                moApp.OpenCurrentDatabase("D:\RobDog888.mdb")
            End If
        Else
            moApp.Visible = True
            moApp.OpenCurrentDatabase("D:\RobDog888.mdb")
        End If
        moApp.RunCommand(Access.AcCommand.acCmdAppMaximize)
        moApp.DoCmd.Maximize()
        moApp.DoCmd.SetWarnings(False)
        Try
            moApp.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acImport, _
            Access.AcSpreadSheetType.acSpreadsheetTypeExcel9, "Excel_Sheet2", "D:\Book1.xls", False, "Sheet2$")
            moApp.DoCmd.OpenTable("Excel_Sheet2", Access.AcView.acViewNormal, Access.AcOpenDataMode.acEdit)
            Me.Activate()
            MessageBox.Show("Import Done!", "VB/Office Guru™ Access Demo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "VB/Office Guru™ Access Demo", _
            MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try
        moApp.DoCmd.SetWarnings(True)
    End Sub
 
    Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        If mbKillMe = True Then
            Try
                moApp.CurrentDb.Close()
                moApp.Quit(Access.AcQuitOption.acQuitPrompt)
            Catch
                'No longer opened
            End Try
            moApp = Nothing
        End If
    End Sub
End Class

Re: [FAQ's: OD] How do I transfer data between Access and Excel?

VB.NET 2005/2008 Import Code Example:

VB.NET Code:

'Import all of Sheet2 from Book1.xls
'NOTE: Add your Button1 control to your form.
Option Explicit On
Option Strict On
 
Imports Microsoft.Office.Interop
 
Public Class Form1
    Inherits System.Windows.Forms.Form
 
    Private moApp As Access.Application
    Private mbKillMe As Boolean
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            moApp = DirectCast(GetObject(, "Access.Application"), Access.Application)
            mbKillMe = False
        Catch ex As Exception
            If TypeName(moApp) = "Nothing" Then
                moApp = DirectCast(CreateObject("Access.Application"), Access.Application)
                mbKillMe = True
            Else
                MessageBox.Show(ex.Message, "VB/Office Guru™ Access Demo", _
                MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            End If
        End Try
        If mbKillMe = False Then
            If moApp.CurrentDb.Name <> "C:\RobDog888.mdb" Then
                moApp.OpenCurrentDatabase("C:\RobDog888.mdb")
            End If
        Else
            moApp.Visible = True
            moApp.OpenCurrentDatabase("C:\RobDog888.mdb")
        End If
        moApp.RunCommand(Access.AcCommand.acCmdAppMaximize)
        moApp.DoCmd.Maximize()
        moApp.DoCmd.SetWarnings(False)
        Try
            moApp.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acImport, _
            Access.AcSpreadSheetType.acSpreadsheetTypeExcel9, "Excel_Sheet2", "C:\Book1.xls", False, "Sheet2$")
            moApp.DoCmd.OpenTable("Excel_Sheet2", Access.AcView.acViewNormal, Access.AcOpenDataMode.acEdit)
            Me.Activate()
            MessageBox.Show("Import Done!", "VB/Office Guru™ Access Demo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "VB/Office Guru™ Access Demo", _
            MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try
        moApp.DoCmd.SetWarnings(True)
    End Sub
 
    Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        If mbKillMe = True Then
            Try
                moApp.CurrentDb.Close()
                moApp.Quit(Access.AcQuitOption.acQuitPrompt)
            Catch
                'No longer opened
            End Try
            moApp = Nothing
        End If
    End Sub
 
End Class

Re: [FAQ's: OD] How do I transfer data between Access and Excel?

VB 6 Import Code Example

VB Code:

Option Explicit
'Add a reference to Microsoft Access xx.0 Object Library
Private moApp As Access.Application
 
Private Sub Command1_Click()
    On Error GoTo My_Error
 
    Set moApp = CreateObject("Access.Application")
    moApp.DoCmd.SetWarnings False
    moApp.OpenCurrentDatabase "C:\RobDog888.mdb"
    moApp.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Excel_Sheet2", "C:\Book1.xls", False, "Sheet2$"
    moApp.Visible = True
    moApp.DoCmd.OpenTable "Excel_Sheet2", acViewNormal, acEdit
    moApp.DoCmd.SetWarnings True
    Exit Sub
My_Error:
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbExclamation
End Sub
 
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    On Error GoTo My_Error
    
    moApp.CurrentDb.Close
    moApp.Quit acQuitPrompt
MyError:
    Set moApp = Nothing
End Sub

May 24th, 2009, 12:31 AM
make me rain

Re: [FAQ's: OD] How do I transfer data between Access and Excel?

what normally i do is ,
use copyfromrecordset method of
a cell
for eg:
create a recordset of the required dataset say :exportRS
Create excel object
dim exls as object
dim exlt as object
dim exlb as object

set exls = createobject("excel.application")
set exlb = exls.workbooks.add
set exlt = exlb.worksheets(1)

with exlt
.range("a1").cells.copyfromrecordset exportrs
end with
but the field names must be written separately
May 24th, 2009, 04:57 PM
RobDog888

Re: [FAQ's: OD] How do I transfer data between Access and Excel?

Yes, I cover the direction from Excel to Access method in my other FAQ entry:

Excel to Access - http://www.vbforums.com/showthread.php?t=402060

:)