Automating Data Import Into Access from an External Data Source

First step whenever I have to create a file import or export process is to do it manually, and save the import steps at the end of that process.

Are you sure you want to import from an xlsb file format?  I've never actually tried to import a .xlsb file format.

I do use import specs when I do data import manually to ensure that the data gets posted to the target table in the desired format.  But in this particular case this is not what I want to do.

I want to import through VBA from the source file in Excel format.  I use the xlsb format as it compacts the data hence process the transfer at a much faster rate to Access table but for my question I can work with xls or xlsx extensions as well.  

For what I need DoCmd should suffice but for some reason it creates test table without any fields or data in it.

You left out a critical parameter.  You have to tell Access that you are using the .xlsb file type.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "test", FilePath, True

To simplify for testing purposes I did the following,

I made a copy of the file with xlsb extension and saved it as xlsx on my desktop

used the below code:

Private Sub Command0_Click()
Dim Filepath As String
Filepath = "C:\Users\ErdenR\Desktop\OCEANResultsTEST.xlsx"

DoCmd.TransferSpreadsheet acImport, , "test", Filepath, True

End Sub

Excel file has one tab in it, there was another one but deleted for testing.

In this case did not put the file type parameter (acSpreadsheetTypeExcel12) so put ", ," instead since it is xlsx.

When I ran the code from the command button it still creates "Test" table but there is only one field in it named F1.

It is not importing data from the source file into Test table.

As Pat mentioned above, you are leaving out the parameter that defines the data source.  You need that file type parameter to tell Access what format the data is in.

Tried with the below including the file type parameter but still the same result.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Test", Filepath, True

"I do use import specs when I do data import manually to ensure that the data gets posted to the target table in the desired format.  But in this particular case this is not what I want to do. "

OK, understand you don't want to do this in this case, but have you tried it with this dataset, to see whether the import wizard will import the file correctly?

What version of Office are you using?

Yes I can use import specs method in fact I do use it where I see fit as part of my total process. Just for the heck of it I have tried that anyways right now and yes it does work using the same data source.  

But again this is not how I want to accomplish this task.  I have routines that dump data sets generated from Access into Excel using functions/subs. They are feeding various pivot tables with dynamically updating ranges in Excel via VBA and multiple dashboards respectively and by the same token I should be able to import data into Access from Excel and DoCmd method should do the trick for this goal.

Besides the idea is that once I set this import function I can use it controlled by a macro which currently runs multiple queries so that way my entire update process would be automated which is done daily.

I am using Office 2016 (Office365 ProPlus)

I have put the data into a brand new Excel file to import from and the problem solved.  
Thank you Pat.

You're welcome.