docmd.transferspreadsheet错误地分配了数据类型 [英] docmd.transferspreadsheet incorrectly assigning Data Type

问题描述

我正在使用以下代码将数据从Excel文件导入到Access中.

I'm using the following code to import data from an Excel file into access.

Private Sub cmdOriginal_Click()

Dim fDialog As Office.FileDialog
Dim varFile As Variant

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

With fDialog

  .AllowMultiSelect = False
  .Title = "Please select a file"
  .Filters.Clear
  .Filters.Add "Excel Files", "*.XLSX"
  .Filters.Add "All Files", "*.*"

If .Show = True Then

For Each varFile In .SelectedItems

DoCmd.TransferSpreadsheet acImport, 10, "OriginalData", varFile, True, ""
Beep
MsgBox "Import Complete!", vbExclamation, ""

Next
Else
     MsgBox "You clicked Cancel in the file dialog box."
End If

End With

End Sub

在大多数情况下，它运行良好，但是在某些情况下，我发现它错误地将字段的数据类型设置为数字.然后，这将导致导入失败，因为要导入的数据实际上包含文本和数字.不能提前创建表，因为导入的每个文件的字段名都不同.

It works well for the most part, however in some cases I'm finding that it incorrectly sets the data type of a field to number. This then causes import failures as the data being imported actually contains both text and numbers. Creating the table in advance isn't an option because the field names are different for each file I import.

对于我如何克服这个问题，我将不胜感激.

I'd appreciate any suggestion of how I can get past this.

关于， 亚当

推荐答案

使用Access可以最好地猜测数据类型应该是什么.一个古老的技巧是在Excel文件的标题下方插入一个虚拟行.在令人反感的列中，输入您需要的数据类型的示例，因此对于字符串，请输入"abd123"之类的内容.如果您不想手动执行此操作，则可以在Excel或Access本身中轻松实现自动化(创建Excel应用程序对象，打开文件，进行调整并保存，然后再上传代码).然后，您可以简单地使用包含伪字符串作为条件的删除查询从表中删除这些行.希望这会有所帮助.

Access makes it's best guess as to what the data type should be. An old trick is to insert a dummy row below your headers in the Excel file. In the offending columns put an example of the data type you require, so for a string put something like "abd123". If you don't want to do that manually it could easily be automated within Excel or from Access itself (create an Excel app object, open the file, make the adjustment & save before your code does the upload). Then you can simply delete these rows from the table with a delete query containing the dummy string as a criteria. Hope this helps.

这篇关于docmd.transferspreadsheet错误地分配了数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！