Forum Discussion

Sean Sodha's avatar
Sean Sodha
Copper Contributor
Apr 25, 2018

Excel Data Exported to Microsoft Access --> Object Required

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

 

 

 

 

 

 

 

Formulas & Functions
Need Help
  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Apr 25, 2018

    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"
    • Sean Sodha's avatar
      Sean Sodha
      Copper Contributor
      Apr 30, 2018

      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? 

      • Matt Mickle's avatar
        Matt Mickle
        Bronze Contributor
        Apr 30, 2018

        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

Resources