Excel Data Exported to Microsoft Access --> Object Required

[Sean Sodha]

Hi everyone. Within Excel, I am trying to create a table in Microsoft Access, and then export a range of data in Excel into that Access table. I am successful in creating a table in Microsoft Access. However, in my last line of code for exporting the range of cells, I seem to be encountering the error "Object Required". Does anyone have any thoughts on this? My code is below: 

Sub Export()

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
Dim xlbook As Workbook
Dim xlsheet As Worksheet
Set xlbook = ActiveWorkbook
Set xlsheet = Sheets("Simulation")
Dim connectdb As String, pathdb As String, connObj As ADODB.Connection
Dim database_table_name As Variant

blnHasFieldNames = True
database_table_name = InputBox("Please Give a Name To The Test You Are Going to Export (No Spaces): ")

'CREATING TABLE IN ACCESS
'PATH OF EXCEL FILE --> CHANGE THIS
'strPath = "C:\Users\jt\Desktop\Inventory Policy Analysis.xlsm"

'PATH OF DATABASE --> CHANGE THIS
pathdb = "C:\Users\jt\Desktop\Microsoft_Access\HSS_Test.accdb"

'Connection reference and path
connectdb = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pathdb & ";"
'Connecting to the database and creating new table
Set connObj = New ADODB.Connection

'Creating the table with the given column names 
With connObj
.Open connectdb
.Execute "CREATE TABLE " & database_table_name & " ([Dates] datetime NULL , " & _
"[Demand] DOUBLE , " & _
"[Inventory Position] DOUBLE, " & _
"[On Order] DOUBLE, " & _
"[On Hand] DOUBLE)"
End With

strTable = database_table_name
acImport = 0

'EXPORTING DATA TO ACCESS

'THIS IS WHERE THE PROBLEM IS OCCURING
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
database_table_name, strPath, True, "I1:M100"


End Sub

[#2]

This works for me from the database itself.  It seems much simpler....

Option Compare Database

Sub Export()

Dim strPathFile As String
Dim strPath As String
Dim database_table_name As Variant

database_table_name = InputBox("Please Give a Name To The Test You Are Going to Export (No Spaces): ")

strPath = "C:\Users\mmickle1\Documents\Test1.xlsm"

CurrentDb.Execute "CREATE TABLE " & database_table_name & " ([Dates] datetime NULL , " & _
"[Demand] DOUBLE , " & _
"[Inventory Position] DOUBLE, " & _
"[On Order] DOUBLE, " & _
"[On Hand] DOUBLE)"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
database_table_name, strPath, True, "I1:M100"

End Sub

 However if you are getting an object required error maybe try changing this:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
database_table_name, strPath, True, "I1:M100"

to this:

connObj.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
database_table_name, strPath, True, "I1:M100"

[#3]

Hmm it gives me an "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another". Any advice? 

[#4]

Try this VBA code block instead.... You will need to add the reference to the Access Object Library as mentioned in the code comments.  In addition if the file you are running the code out of is not "C:\Users\jt\Desktop\Inventory Policy Analysis.xlsm"  then you will need to update the FileName Parameter :

Option Explicit

Sub AccImport()
    Dim acc As New Access.Application
    Dim database_table_name As Variant
    
    'You need to set a reference to the below Object Library
    'In the Visual Basic Editor do this:
    'Tools > References > Microsoft Access Data XX.X Object Library
    'The XX.X will be your version of Access......something like this 12.0, 14.0, 16.0 etc....

    database_table_name = InputBox("Please Give a Name To The Test You Are Going to Export (No Spaces): ")
    With acc
        .OpenCurrentDatabase "C:\Users\jt\Desktop\Microsoft_Access\HSS_Test.accdb"
        .DoCmd.RunSQL "CREATE TABLE " & database_table_name & " ([Dates] datetime NULL , " & _
        "[Demand] DOUBLE , " & _
        "[Inventory Position] DOUBLE, " & _
        "[On Order] DOUBLE, " & _
        "[On Hand] DOUBLE)"
        
        'In the below DoCmd.TransferSpreadsheet statement if your filename is not the file you are working in then
        'you will need to update the Filename parameter to your filepath....
        'i.e. Filename:= "C:\Users\jt\Desktop\Inventory Policy Analysis.xlsm"
        
        .DoCmd.TransferSpreadsheet _
                TransferType:=acImport, _
                SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
                TableName:=database_table_name, _
                Filename:=Application.ActiveWorkbook.FullName, _
                HasFieldNames:=True, _
                Range:="Simulation$I1:M100"
        .CloseCurrentDatabase
        .Quit
    End With
    Set acc = Nothing
End Sub

[#5]

Thank you so much! I was able to get it to work!!

[#6]

You're welcome.  Glad you were able to get it working!  I haven't done Excel to Access coding in a while so I was a bit rusty.  It was good to re familiarize myself with the object library!