# Overwrite Access Table from Excel Worksheet VBA



## JazzSP8 (Feb 14, 2019)

Hey All

Not sure if this should be in the Excel or Access forum, apologies if it's in the wrong place.

I'm trying to automate a process for another department, they do a certain amount of work in Excel and then transfer it over to Access to run some queries to produce a report and then export it back out to Excel to finish it off.

I've done the first Excel part, normally what they would do at this point is save the Worksheet out as a single Workbook and then use Access to Import the Workbook to a table and overwrite the previous one.

_Access > Right Click on Table > Import > Excel > Select File etc._

I'm wondering if there is a way I can automate this transfer part as well to make my Macro one continuous process? 

(I already know how to do the rest, it's just this one data transfer which is stopping me.)

I'm using Office 2013, the table I want to update in Access is called 'tblBatteries' and the Worksheet in my Workbook is named 'Batteries' if that means anything.

Thanks in advance for any help that can be provided.


----------



## Joe4 (Feb 14, 2019)

Why not just link an Excel file to an Access table?  Then you would not need to worry about importing the file.
Simply replace the Excel file with the new one.  As long as it has the same location, file name, and structure as the old one, it should work.


----------



## JazzSP8 (Feb 14, 2019)

Never mind, I carried on Googling and eventually came across some code I could work with, this seems to do the trick 


```
Sub SendToAccess()

Set acc = CreateObject("Access.Application")

    acc.OpenCurrentDatabase "G:\Play.mdb"
    acc.DoCmd.TransferSpreadsheet _
            TransferType:=acImport, _
            SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
            TableName:="tblBatteries", _
            Filename:=Application.ActiveWorkbook.FullName, _
            HasFieldNames:=True, _
            Range:="Batteries$A1:W83"
    acc.CloseCurrentDatabase
    acc.Quit
    Set acc = Nothing

End Sub
```

Thanks for looking all the same


----------



## JazzSP8 (Feb 14, 2019)

Joe4 said:


> Why not just link an Excel file to an Access table?  Then you would not need to worry about importing the file.
> Simply replace the Excel file with the new one.  As long as it has the same location, file name, and structure as the old one, it should work.



Or, that. 

I'd got stuck on one track thinking there!

Thanks for that Joe4


----------



## Joe4 (Feb 14, 2019)

You are welcome!


----------

