Failing VBA - Access Import

[hellfire45]

In the below code, I am trying to append the data in the file destinationpath & CRT_File_Backlog to the bottom of the access database table, strAccPath & tbl_backlog

destinationpath & CRT_File_Backlog(excel 2013 .xlsx) >>>>>>>> strAccPath & tbl_backlog(Access 2003 dbo)

The VBA editor is giving me the error Run-Time Error 3274 "External Table is not in the expected format."
And honestly, I'm not sure I've written this up correctly. Can anybody help me correct the error of my way?

Thanks so much!

Option Explicit

Sub File_Uploads_1()


Dim CRT_File_Backlog, CRT_File_Shipments, CRT_File_Serials, CRT_File_Tracking, destinationpath As String
Dim strAccPath, tbl_backlog, tbl_serials, tbl_shipments, tbl_tracking As String


Application.DisplayAlerts = False
Application.CalculateBeforeSave = False
Application.Calculation = xlManual
Application.ScreenUpdating = False
Application.EnableEvents = False


'location and name of files to the appended to the bottom of a subsequent access database tables
If Dir("I:\ASM\All\CRT\Reporting\In Consolidation Process\Master Automation\Saved CRT Uploads\" & Format(Date, "yyyy-mm-dd"), vbDirectory) = "" Then
            MsgBox "No Files Found.  Please run Batches and then try again."
            End
    Else
            destinationpath = "I:\ASM\All\CRT\Reporting\In Consolidation Process\Master Automation\Saved CRT Uploads\" & Format(Date, "yyyy-mm-dd") & "\"
            CRT_File_Backlog = "CRT UPLOAD Backlog - " & Format(Date, "mm-dd-yyyy") & ".xlsx"
            CRT_File_Shipments = "CRT UPLOAD ETAs from Shipments - " & Format(Date, "mm-dd-yyyy") & ".xlsx"
            CRT_File_Serials = "CRT UPLOAD Serials - " & Format(Date, "mm-dd-yyyy") & ".xlsx"
            CRT_File_Tracking = "CRT UPLOAD Tracking- " & Format(Date, "mm-dd-yyyy") & ".xlsx"
End If


'Location of access database and name of tables
strAccPath = "I:\ASM\All\CRT\Reporting\In Consolidation Process\Master Automation\CRT Database.mdb"
tbl_backlog = "Backlog"
tbl_serials = "Serials"
tbl_shipments = "Shipments"
tbl_tracking = "Tracking"


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tbl_backlog, destinationpath & CRT_File_Backlog, -1

 

[Joe4]

I think your issue is that "acSpreadsheetTypeExcel9" is an Excel 2000 format (see: https://msdn.microsoft.com/en-us/vba/access-vba/articles/acspreadsheettype-enumeration-access), which does not jive with a "xlsx" extension. "xlsx" was not introduced until Excel 2007 (https://support.office.com/en-us/ar...in-Excel-0943ff2c-6014-4e8d-aaea-b83d51d46247).

You want to make sure you get all this things aligned. If you are importing into a 2003 Access database, I don't think you can use "xlsx" files, so you may need to first convert the Excel file down to a lower "xls" version.

 

[hellfire45]

I think your issue is that "acSpreadsheetTypeExcel9" is an Excel 2000 format (see: https://msdn.microsoft.com/en-us/vba/access-vba/articles/acspreadsheettype-enumeration-access), which does not jive with a "xlsx" extension. "xlsx" was not introduced until Excel 2007 (https://support.office.com/en-us/ar...in-Excel-0943ff2c-6014-4e8d-aaea-b83d51d46247).

You want to make sure you get all this things aligned. If you are importing into a 2003 Access database, I don't think you can use "xlsx" files, so you may need to first convert the Excel file down to a lower "xls" version.

Thank you for the suggestion.

I changed the file format to 97-03 excel (.xls) and I changed the do command line to

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, tbl_backlog, destinationpath & CRT_File_Backlog, -1

So it's throwing off a different error now, "Run time error 2075 - This operation Requires an open database

I tried to fix it by adding OpenCurrentDatabase (database name) to the code but it didn't resolve the issue.


Let me re-enter the modified code below:

Option Explicit

Sub File_Uploads_1()


Dim CRT_File_Backlog, CRT_File_Shipments, CRT_File_Serials, CRT_File_Tracking, destinationpath As String
Dim strAccPath, tbl_backlog, tbl_serials, tbl_shipments, tbl_tracking As String
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim acApp As Object


Application.DisplayAlerts = False
Application.CalculateBeforeSave = False
Application.Calculation = xlManual
Application.ScreenUpdating = False
Application.EnableEvents = False




'location and name of files to the appended to the bottom of a subsequent access database tables
If Dir("I:\ASM\All\CRT\Reporting\In Consolidation Process\Master Automation\Saved CRT Uploads\" & Format(Date, "yyyy-mm-dd"), vbDirectory) = "" Then
            MsgBox "No Files Found.  Please run Batches and then try again."
            End
    Else
            destinationpath = "I:\ASM\All\CRT\Reporting\In Consolidation Process\Master Automation\Saved CRT Uploads\" & Format(Date, "yyyy-mm-dd") & "\"
            CRT_File_Backlog = "CRT UPLOAD Backlog - " & Format(Date, "mm-dd-yyyy") & ".xls"
            CRT_File_Shipments = "CRT UPLOAD ETAs from Shipments - " & Format(Date, "mm-dd-yyyy") & ".xls"
            CRT_File_Serials = "CRT UPLOAD Serials - " & Format(Date, "mm-dd-yyyy") & ".xls"
            CRT_File_Tracking = "CRT UPLOAD Tracking- " & Format(Date, "mm-dd-yyyy") & ".xls"
End If


'Location of access database and name of tables
strAccPath = "I:\ASM\All\CRT\Reporting\In Consolidation Process\Master Automation\CRT Database.mdb"
tbl_backlog = "Backlog"
tbl_serials = "Serials"
tbl_shipments = "Shipments"
tbl_tracking = "Tracking"




Set acApp = CreateObject("Access.Application")
Call acApp.OpenCurrentDatabase(strAccPath)
Set dbs = acApp.CurrentDb


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, tbl_backlog, destinationpath & CRT_File_Backlog, -1

 

[Joe4]

Are you running the code from Excel or Access?
If from Excel, I think it is telling you that the Access database must be open in order to do that command.
I think it might be better to run this from Access instead of Excel.

 

[hellfire45]

I'm running it from excel. This team at my company isn't very technical and It would definitely be easier for them to run it from excel. Why might it be that the line

[COLOR=#333333]Call acApp.OpenCurrentDatabase(strAccPath)[/COLOR]

does not open up the database?

 

[Joe4]

I write Excel VBA code, and Access VBA code, but I never write Excel VBA code to run anything in Access. So I am not entirely with the familiar with all the different methods of having them interact.
But that line of code you posted looks rather odd to me. You usually use the "Call" function to call other procedure you have created.
Where did you get this line of code?
Can you confirm that it works (is it opening the Access database)?

 

[hellfire45]

I write Excel VBA code, and Access VBA code, but I never write Excel VBA code to run anything in Access. So I am not entirely with the familiar with all the different methods of having them interact.
But that line of code you posted looks rather odd to me. You usually use the "Call" function to call other procedure you have created.
Where did you get this line of code?
Can you confirm that it works (is it opening the Access database)?

Yea I don't think its opening up the database.

 

[Joe4]

See if these help:
http://software-solutions-online.com/vba-open-access-database-from-excel/
https://www.mrexcel.com/forum/microsoft-access/56975-open-access-db-excel-vba.html