使用VB导入而不是手动导入 [英] Importing using VB not manual import

问题描述

我目前知道如何手动导入这些文件，但我希望通过导入按钮执行vb代码以自动执行。有一个链接到导入按钮的字段，要求您输入日期并单击导入。单击导入后，我希望它根据文件名上的日期获取文件。

I currently know how to import these files manually, but I am looking to execute a vb code through my import button to do it automatically. There is a field linked to the import button which requires you to enter a date and click import. Once import is clicked, I would like it to grab a file based on the date that is on the files name.

这些文件是文本文件，文件名写入有两种格式：

These files are text files and the file names are written in two types of format:

第一格式 - P.RR1234.ABCDEF.D160112.T123456

1st Format - P.RR1234.ABCDEF.D160112.T123456

第二格式 - G1234.ABCDEF.D160112.T123456

2nd Format - G1234.ABCDEF.D160112.T123456

这是我当前表单的vb代码（我目前已将其映射到我的桌面，但有100个文件位于格式为的网络/共享路径）：

Here is my vb code for my current form (I currently have it mapped to my desktop but there are 100's of files located on a network/shared path that are in the format):

          Option Compare Database

Private Sub cmdImport_Click()

On Error GoTo Click_Err

    If Nz(txtReportDate, "") = "" Then
        MsgBox "NOTICE! Please enter the Report Month you wish to Import."

        Else

    Dim rs As Recordset
    Dim sql As String

    'Loop through recordset of all Contracts and import files
    sql = "SELECT DISTINCT FROM AAAAB_CE"
    Set rs = CurrentDb.OpenRecordset(sql)
    rs.MoveLast
    rs.MoveFirst
    If rs.RecordCount > 0 Then
        Do While rs.EOF = False
            ImportFile rs!DISTINCT
            rs.MoveNext
        Loop
    End If

    DoCmd.Hourglass True
    DoCmd.SetWarnings False

    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    MsgBox "Finished Importing!"
    DoCmd.OpenQuery "query_Files_Loaded_CE", acViewNormal, acReadOnly

click_Exit:
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    Exit Sub

Click_Err:
    DoCmd.Hourglass False
    MsgBox "Error Detected: " & Err.Number & " - " & Err.Description, vbCritical, "Error"
    Resume click_Exit

       Exit Sub
    End If

End Sub

模块：

Option Explicit
Option Compare Database

Public Function ImportFile(Contract As String)

DoCmd.TransferText acImportFixed, "123_Files", "AAAAB_CE", "C:\Users\123456\Desktop\TestingFolder\test.txt", False

End Function

我正在尝试将这些文本文件导入到一个表中。有很多文件，所以手动不够。

I am trying to import these text files into one table. There are a lot of files, so manually would be insufficient.

目前，如果我在表单文本日期输入一个随机日期并单击导入。它导入我选择的文件，但格式完全错误。这很奇怪，因为我保存的规格是以正确的格式完成的。

Currently, if I put in a random date on the form text date and click import. It imports the file I select, but in a completely wrong format. Which is weird because the specification I saved was done in the correct format.

总而言之，我想完成三件事。

All in all I am trying to accomplish three things.

1）通过在表单上的字段中输入日期自动导入（日期将是文件名的一部分）并单击导入按钮
2）以正确的结构导入文本文件，固定列
3）如果输入的日期不正确，请不要导入任何内容。

1) Import automatically by entering a date in a field on form (the date will be part of the file name) and clicking the import buton 2) Import the text file in the correct structure, fixed columns 3) If an incorrect date is entered, do not import anything.

推荐答案

我无法排除故障这段代码不知道txt文件中使用的日期格式和文件本身，但这就是我会攻击它的方式：

I couldn't troubleshoot this code without knowing the date format used in the txt files and the files themselves, but this is how I would attack it:

• 使用Dir循环遍历指定文件夹位置的文件，搜索包含日期的.txt文件，

• use Dir to loop through the files at specified folder location searching for .txt files that contains the date,

然后调用ImportFile函数（实际上是sub no？）通过传递文件名。

then calling your ImportFile function (which is really a sub no?) by passing the filename.

希望你能够遵循逻辑......

Hopefully enough there for you to follow the logic..

Public Sub sampleSub()
Dim dirStr As String
Dim filePath As String
Dim fileDate As Date

filePath = "C:\Users\123456\Desktop\TestingFolder\"
On Error GoTo doNothing:
fileDate = DateValue(InputBox("NOTICE! Please enter the Report Month you wish to Import."))

dirStr = Dir(filePath & "\" & "*" * Format(fileDate, "FileDateFormat") & "*" & ".txt")
Do Until dirStr = ""
    Call ImportFile(dirStr)
Loop

Exit Sub
doNothing:
Call MsgBox("Error Detected: " & Err.Number & " - " & Err.Description, vbCritical, "Error")
End Sub

Public Sub ImportFile(fileName As String)
Call DoCmd.TransferText(acImportFixed, "123_Files", "AAAAB_CE", fileName, False)
End Sub

希望这会有所帮助，
TheSilkCode

Hope this helps, TheSilkCode

这篇关于使用VB导入而不是手动导入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！