append data from excel to existing table in access vba (1 Viewer)

[habinalshaikh]

Hello there

I have a database named "Dashboard" and I have an excel file named Report

I want to create a button that add the data from the excel file to the table "Dashboard"

what I did is this:

I created a button to get the excel file path to be in the textbox "txtPath"

but I need someone help me to do the add the data from the excel to the "Dashboard" table

thank you,,

 

[theDBguy]

Depending on what type of data you have, I would recommend importing the Excel data into a staging table first, so you can scrub it first, if necessary, before unintentionally contaminating the dashboard table.

 

[GPGeorge]

The VBA you need is like this:


Public Sub GetExcel()

DoCmd.TransferSpreadsheet TransferType:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel12Xml, TableName:="Dashboard" "StagingTableName" , FileName:="C:\PathandFileNameExcel.xlsx", HasFieldNames:=True

End Sub

 

[habinalshaikh]

Depending on what type of data you have, I would recommend importing the Excel data into a staging table first, so you can scrub it first, if necessary, before unintentionally contaminating the dashboard table.

do you mean to import the excel data to a temporary table then add the data from the temporary table to the "Dashboard" table and then the temporary table will be deleted?

ok what is the code that I have to write?

 

[GPGeorge]

do you mean to import the excel data to a temporary table then add the data from the temporary table to the "Dashboard" table and then the temporary table will be deleted?

ok what is the code that I have to write?

If the table structure of the destination table (i.e. "Dashboard") does not change, I recommend not creating and deleting it. Rather I suggest you delete any existing records in the staging table prior to creating a new import. Either approach will create database bloat, but having the staging table with its structure makes the task of importing less troublesome, IMO.

 

[habinalshaikh]

The VBA you need is like this:


Public Sub GetExcel()

DoCmd.TransferSpreadsheet TransferType:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel12Xml, TableName:="Dashboard" "StagingTableName" , FileName:="C:\PathandFileNameExcel.xlsx", HasFieldNames:=True

End Sub

thank you so much it works