Overwrite Access Table from Excel Worksheet VBA

[JazzSP8]

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]

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]

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]

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]

You are welcome!