从Excel中的表导出DBF（请注意并注意数据类型和字段长度） [英] Export DBF from a Table in Excel (with consider and pay attention to Datatype and length of fields)

问题描述

我正在使用宏容器Excel 工作簿在 Sheet1中的表上。

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

并且必须使用<$ c $从表创建DBF文件c> VBA 宏。

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

1-请注意，2003年以后，Excel不支持 DBF 格式，因此无法使用波纹管...

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

因此，请编写一个 VBA 宏，该宏将从 Filtered <中保存 DBF / code>范围（具有多个 Area 的范围）在MS-Excel <$ c中的表 $ c>工作表。

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-我可以为创建的 DBF中的每个字段定义自定义数据类型通过假设的 VBA 宏？

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

我不想打开或编辑现有的 *。dbf 。我将使用自定义的数据类型和长度 DBF $ c>（如果可能）；从工作表中具有 VBA 函数。

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.

我们可以使用：

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

为此？ （作为一个想法）

For that?! (As an idea)

谢谢。

更多说明：

是否有任何快速而安静的方法可以从一系列Excel中导出 dbf 文件？如果存在从该范围创建和保存 dbf 文件的直接方法，则更好。

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.

推荐答案

此代码并不完美。

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

这篇关于从Excel中的表导出DBF（请注意并注意数据类型和字段长度）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！