-
Apr 27th, 2006, 12:48 AM
#1
[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
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Aug 23rd, 2006, 04:03 PM
#2
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
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Mar 29th, 2008, 04:32 PM
#3
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
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Feb 19th, 2009, 01:47 PM
#4
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
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
May 24th, 2009, 12:31 AM
#5
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
#6
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|