Freelance Project Requests info@CodeDocu.de Software Development in C# WPF Asp.Net Core Vba Excel Word SQL-Server EF Linq, UWP Net
#

Download:

Datei 1: Access_Import_Excel_vba.accdb
Datei 2: Excel_Data_to_Import.xlsx

Access: Read Excel file automatically

 

The following vba  code example shows how to automatically import complete Excel files under Microsoft Access.

 

Import Form

For this I have prepared an Access input form with a button in the Access database.

The button then imports the Excel sheet with vba  code.

 

Excel file to import

with excel the data should start in cell A1 and the first row should have the same column labels as in the import table under Access

 

 

Access Import Table

The vba  Import Code copies the data directly to the correct internal table in Access.

A defined import table defines the structures and fields. This means that the data type that is in a field here should correspond to the contents of the Excel cells.

Otherwise, the non-matching data is suppressed as an import error and thus omitted.

 

Vba code for importing Excel data

Automatic importers.

The first step is to empty the import table.

For this purpose, the DELETE Transact SQL is used

CurrentDb.Execute "DELETE * FROM _Import"

 

Afterwards, under Access, the Excel import with  TransferSpreadSheet applies

DoCmd.TransferSpreadsheet acImport, , TableName:="_Import", FileName:=sPath &  "A"  & sFilename, HasFieldNames:=True

 

 

 

Option  Compare Database

Option Explicit On

 

Private    Sub BtnImport_Click()

    '-----------< BtnImport_Click() >-----------

    '< get Filename >

    Dim   sFilename    As String

    sFilename = tbxFilename.Value

 

    Dim   sPath    As String

    sPath = CurrentProject.Path

    '</ get Filename >

 

 

    '--< Import_code_vba >--

    '*Delete old Import_Data

    CurrentDb.Execute "DELETE * FROM _Import"

 

    DoCmd.TransferSpreadsheet acImport, , TableName:="_Import", FileName:=sPath &  "A"  & sFilename, HasFieldNames:=True

 

    '--</ Import_code_vba >--

 

    MsgBox   "Excel Data imported to _Import Table"

    '-----------</ BtnImport_Click() >-----------

End Sub

 

 

 

The file is available for download as an example

 

 

 

Data example for importing Excel data.

Care must be taken to ensure that the correct column formats are applied.

That is, the numbers as numbers, date, special formats, text formats are always read correctly

Col1_Text

Col_Number

col_Date

col_Empty

col_Number_Text

Abc

1

01.01.1999

1

En

2

02.02.1998

2

E

3

31.12.2019

3

Q

3

G

4

H

4

Xx

11

Xx

9

Xx

10

Xx

11

Xxxx

12

13

14

15

16

to the End

17

31.12.2020

17

 

 

 

 

Mobile

.

Contact for Jobs, Project Requests: raimund.popp@microsoft-programmierer.de