Export Directly From Excel into Access

RunTime91

Active Member
Joined
Aug 30, 2012
Messages
262
Greetings...

I am using the code below to export from Excel directly into an Access db.

The code works perfectly but I would like to be able to accomplish this without actually opening the Access db

Code:
Sub UploadMorningData3()
On Error Resume Next
    Dim AppAccess As Access.Application
    Set AppAccess = Access.Application
    Call AppAccess.OpenCurrentDatabase("Z:\SanFran\Common10\Sales_DB BACKUP\PIW\SalesRpt\Apps\Sales.accdb")
    
    AppAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblSalesRpt", Application.ActiveWorkbook.FullName, True, "SalesData!A1:BI2"
    
End Sub

Also, I'm using an 'On Error Resume Next' to trap the instance if the db is already open which is fine, but I may not want to resume in the case of 'other' errors - The error # is 7867... What I would want the code to do is in the event the db is already open, then 'Resume Next', in the case of any other error then do something different...

Thank You for your help...
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

RunTime91

Active Member
Joined
Aug 30, 2012
Messages
262
*Update*

The code below achieves the aesthetic effect I'm looking for but the Access db is still opening.
I've been told I need to establish a connection to the db and then open the connection which will allow the transfer of data without having to open the actual db -
I've tried creating an ADODB connection but can't get it to work -

If anyone can guide me on this I would greatly appreciate it, or if I am posting this in the wrong forum - perhaps one of the Admins can chime in on that as well.

Code:
Sub UploadMorningData3()

Application.ScreenUpdating = False
 
    Dim AppAccess As Access.Application
    Set AppAccess = New Access.Application
    Call AppAccess.OpenCurrentDatabase("Z:\SanFran\Common10\Sales_DB BACKUP\PIW\SalesRpt\Apps\Sales.accdb")    
    
    With AppAccess
      .Visible = False
        AppAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblSalesRpt", Application.ActiveWorkbook.FullName, True, "SalesData!A1:BI2
      .CloseCurrentDatabase
    End With

Application.ScreenUpdating = True

End Sub

Thanks as always for any assistance...
 
Last edited:

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,066
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Instead of transferring the data, have you considered linking the Excel Data directly to the Access File. In this manner, every time the Excel data is updated it is reflected in the Access DB as a linked table.
 

RunTime91

Active Member
Joined
Aug 30, 2012
Messages
262
Alan Thanks for chiming in on this...

I have considered this, but due to some volatility issues with the Excel platform I would like to just upload - Apparently, this is much more difficult than I thought

What I have above does work, so I'll work with it for now.

Thank You again Alan..
 

Watch MrExcel Video

Forum statistics

Threads
1,122,736
Messages
5,597,819
Members
414,179
Latest member
anthelsene

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
Top