Results 1 to 6 of 6

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

  1. #1

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    [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:
    1. 'Behind an Access VBA Form
    2. Private Sub Command1_Click()
    3.     Application.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", "C:\Book1.xls", False, "Sheet2$"
    4. End Sub
    Access 2003 VBA Import Code Example:
    Import an Excel Sheet to an Access Table:

    VB Code:
    1. 'Behind an Access VBA Form
    2. Private Sub Command1_Click()
    3.     Application.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Table1", "C:\Book1.xls", False, "A1:D8"
    4. End Sub
    Last edited by RobDog888; Aug 23rd, 2006 at 04:03 PM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  2. #2

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

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

    VB.NET 2003 Import Code Example:
    VB Code:
    1. 'Import all of Sheet2 from Book1.xls
    2. Option Explicit On
    3. Option Strict On
    4.  
    5. Imports Microsoft.Office.Interop
    6.  
    7. Public Class Form1
    8.     Inherits System.Windows.Forms.Form
    9.  
    10.     Private moApp As Access.Application
    11.     Private mbKillMe As Boolean
    12.  
    13. #Region " Windows Form Designer generated code "
    14.  
    15.     Public Sub New()
    16.         MyBase.New()
    17.  
    18.         'This call is required by the Windows Form Designer.
    19.         InitializeComponent()
    20.  
    21.         'Add any initialization after the InitializeComponent() call
    22.  
    23.     End Sub
    24.  
    25.     'Form overrides dispose to clean up the component list.
    26.     Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
    27.         If disposing Then
    28.             If Not (components Is Nothing) Then
    29.                 components.Dispose()
    30.             End If
    31.         End If
    32.         MyBase.Dispose(disposing)
    33.     End Sub
    34.  
    35.     'Required by the Windows Form Designer
    36.     Private components As System.ComponentModel.IContainer
    37.  
    38.     'NOTE: The following procedure is required by the Windows Form Designer
    39.     'It can be modified using the Windows Form Designer.  
    40.     'Do not modify it using the code editor.
    41.     Friend WithEvents Button1 As System.Windows.Forms.Button
    42.     <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
    43.         Me.Button1 = New System.Windows.Forms.Button
    44.         Me.SuspendLayout()
    45.         '
    46.         'Button1
    47.         '
    48.         Me.Button1.Location = New System.Drawing.Point(64, 24)
    49.         Me.Button1.Name = "Button1"
    50.         Me.Button1.TabIndex = 0
    51.         Me.Button1.Text = "Button1"
    52.         '
    53.         'Form1
    54.         '
    55.         Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
    56.         Me.ClientSize = New System.Drawing.Size(180, 66)
    57.         Me.Controls.Add(Me.Button1)
    58.         Me.Name = "Form1"
    59.         Me.Text = "Form1"
    60.         Me.ResumeLayout(False)
    61.  
    62.     End Sub
    63.  
    64. #End Region
    65.  
    66.     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    67.         Try
    68.             moApp = DirectCast(GetObject(, "Access.Application"), Access.Application)
    69.             mbKillMe = False
    70.         Catch ex As Exception
    71.             If TypeName(moApp) = "Nothing" Then
    72.                 moApp = DirectCast(CreateObject("Access.Application"), Access.Application)
    73.                 mbKillMe = True
    74.             Else
    75.                 MessageBox.Show(ex.Message, "VB/Office Guru™ Access Demo", _
    76.                 MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    77.             End If
    78.         End Try
    79.         If mbKillMe = False Then
    80.             If moApp.CurrentDb.Name <> "D:\RobDog888.mdb" Then
    81.                 moApp.OpenCurrentDatabase("D:\RobDog888.mdb")
    82.             End If
    83.         Else
    84.             moApp.Visible = True
    85.             moApp.OpenCurrentDatabase("D:\RobDog888.mdb")
    86.         End If
    87.         moApp.RunCommand(Access.AcCommand.acCmdAppMaximize)
    88.         moApp.DoCmd.Maximize()
    89.         moApp.DoCmd.SetWarnings(False)
    90.         Try
    91.             moApp.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acImport, _
    92.             Access.AcSpreadSheetType.acSpreadsheetTypeExcel9, "Excel_Sheet2", "D:\Book1.xls", False, "Sheet2$")
    93.             moApp.DoCmd.OpenTable("Excel_Sheet2", Access.AcView.acViewNormal, Access.AcOpenDataMode.acEdit)
    94.             Me.Activate()
    95.             MessageBox.Show("Import Done!", "VB/Office Guru™ Access Demo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    96.         Catch ex As Exception
    97.             MessageBox.Show(ex.Message, "VB/Office Guru™ Access Demo", _
    98.             MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    99.         End Try
    100.         moApp.DoCmd.SetWarnings(True)
    101.     End Sub
    102.  
    103.     Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
    104.         If mbKillMe = True Then
    105.             Try
    106.                 moApp.CurrentDb.Close()
    107.                 moApp.Quit(Access.AcQuitOption.acQuitPrompt)
    108.             Catch
    109.                 'No longer opened
    110.             End Try
    111.             moApp = Nothing
    112.         End If
    113.     End Sub
    114. End Class
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  3. #3

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

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

    VB.NET 2005/2008 Import Code Example:
    VB.NET Code:
    1. 'Import all of Sheet2 from Book1.xls
    2. 'NOTE: Add your Button1 control to your form.
    3. Option Explicit On
    4. Option Strict On
    5.  
    6. Imports Microsoft.Office.Interop
    7.  
    8. Public Class Form1
    9.     Inherits System.Windows.Forms.Form
    10.  
    11.     Private moApp As Access.Application
    12.     Private mbKillMe As Boolean
    13.  
    14.     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    15.         Try
    16.             moApp = DirectCast(GetObject(, "Access.Application"), Access.Application)
    17.             mbKillMe = False
    18.         Catch ex As Exception
    19.             If TypeName(moApp) = "Nothing" Then
    20.                 moApp = DirectCast(CreateObject("Access.Application"), Access.Application)
    21.                 mbKillMe = True
    22.             Else
    23.                 MessageBox.Show(ex.Message, "VB/Office Guru™ Access Demo", _
    24.                 MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    25.             End If
    26.         End Try
    27.         If mbKillMe = False Then
    28.             If moApp.CurrentDb.Name <> "C:\RobDog888.mdb" Then
    29.                 moApp.OpenCurrentDatabase("C:\RobDog888.mdb")
    30.             End If
    31.         Else
    32.             moApp.Visible = True
    33.             moApp.OpenCurrentDatabase("C:\RobDog888.mdb")
    34.         End If
    35.         moApp.RunCommand(Access.AcCommand.acCmdAppMaximize)
    36.         moApp.DoCmd.Maximize()
    37.         moApp.DoCmd.SetWarnings(False)
    38.         Try
    39.             moApp.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acImport, _
    40.             Access.AcSpreadSheetType.acSpreadsheetTypeExcel9, "Excel_Sheet2", "C:\Book1.xls", False, "Sheet2$")
    41.             moApp.DoCmd.OpenTable("Excel_Sheet2", Access.AcView.acViewNormal, Access.AcOpenDataMode.acEdit)
    42.             Me.Activate()
    43.             MessageBox.Show("Import Done!", "VB/Office Guru™ Access Demo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    44.         Catch ex As Exception
    45.             MessageBox.Show(ex.Message, "VB/Office Guru™ Access Demo", _
    46.             MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    47.         End Try
    48.         moApp.DoCmd.SetWarnings(True)
    49.     End Sub
    50.  
    51.     Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
    52.         If mbKillMe = True Then
    53.             Try
    54.                 moApp.CurrentDb.Close()
    55.                 moApp.Quit(Access.AcQuitOption.acQuitPrompt)
    56.             Catch
    57.                 'No longer opened
    58.             End Try
    59.             moApp = Nothing
    60.         End If
    61.     End Sub
    62.  
    63. End Class
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  4. #4

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

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

    VB 6 Import Code Example
    VB Code:
    1. Option Explicit
    2. 'Add a reference to Microsoft Access xx.0 Object Library
    3. Private moApp As Access.Application
    4.  
    5. Private Sub Command1_Click()
    6.     On Error GoTo My_Error
    7.  
    8.     Set moApp = CreateObject("Access.Application")
    9.     moApp.DoCmd.SetWarnings False
    10.     moApp.OpenCurrentDatabase "C:\RobDog888.mdb"
    11.     moApp.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Excel_Sheet2", "C:\Book1.xls", False, "Sheet2$"
    12.     moApp.Visible = True
    13.     moApp.DoCmd.OpenTable "Excel_Sheet2", acViewNormal, acEdit
    14.     moApp.DoCmd.SetWarnings True
    15.     Exit Sub
    16. My_Error:
    17.     MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbExclamation
    18. End Sub
    19.  
    20. Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    21.     On Error GoTo My_Error
    22.    
    23.     moApp.CurrentDb.Close
    24.     moApp.Quit acQuitPrompt
    25. MyError:
    26.     Set moApp = Nothing
    27. End Sub
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  5. #5
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    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

  6. #6

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    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

    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width