Export Directly From Excel into Access

[RunTime91]

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

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...

 

[RunTime91]

Would this question be better suited for the Access forum?

 

[RunTime91]

^

 

[RunTime91]

*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.

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...

 

[alansidman]

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]

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..