Export DBF from a Table in Excel (with consider and pay attention to Datatype and length of fields)

Question

I'm working in an macro container Excel Workbook on a Table in Sheet1.

And have to create DBF file from the Table With VBA macro.

1-note that after 2003, Excel not support DBF format, thus I cannot use bellow...

ActiveWorkbook.SaveAs _
    Filename:="C:\insurance.dbf", _
    FileFormat:=xlDBF4, _
    CreateBackup:=False

So, please write a VBA macro which save DBF from a Filtered range (a range with multi Area) of that Table in MS-Excel Worksheet.

2- May i define my customized data type to each field in created DBF by above hypothetical VBA macro?

I Don't want opening or edit an existing *.dbf. I'm just going create and save DBF with customized Datatype and length if possible; from a multi area filtered range table in Worksheet with a VBA function.

Can we use:

Dim appAccess as Object
set appAccess = CreateObject("Acss.App")
...
appAccess.Run ...

For that?! (As an idea)

Thanks.

For more description:

Are there any quick and quiet way to export dbf file from a range of Excel? If existing direct way to create and save dbf file from that range, that is better.

Answer 1

This code is not perfect. Just refer to it.

Sub myQuery()
    Dim path As String, pathExcel As String
    Dim dbName As String
    'Dim App As Access.Application
    Dim App As Object

    'Set App = New Access.Application
    On Error Resume Next
    Set App = CreateObject("Access.Application")

    path = "C:\Users\Administrator\Documents\"
    pathExcel = ThisWorkbook.FullName

    dbName = "C:\Users\Administrator\Documents\Database1.accdb" '<~~ in access, you create accdb(Database1.accdb) before.

    App.OpenCurrentDatabase dbName

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Sheet1", pathExcel '<~~from sheet1 to access

    App.DoCmd.TransferDatabase acExport, "DBase IV", path, acTable, "Sheet1", "test.dbf" '<~~ access table to dbf
    DoCmd.DeleteObject acTable, "Sheet1"

    App.DoCmd.Close
    App.DoCmd.Quit
    App.Quit

End Sub