Overwrite Access Table from Excel Worksheet VBA

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,227
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.
 
Upvote 0
Never mind, I carried on Googling and eventually came across some code I could work with, this seems to do the trick :)

Code:
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 :)
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top