在上载到Access数据库后将用户输入添加到Excel表中 [英] Add user input to Excel table upon upload to Access database

问题描述

我正在尝试将用户输入与现有Excel文件数据结合起来，以便将它们全部上载到Access数据库时都将包含在单个表中.

I'm trying to combine user inputs with existing Excel file data so they will all be included in a single table when they are uploaded to the Access database.

这是我进行布局的方式，但是我愿意更改它，但需要进行更改.

Here is the way I have it laid out, but I'm open to changing it however need be.

我完全不知道下一步该怎么做.上传日期会自动填写，但其余参数会因产品而异，必须手动填写.我还希望每个字段都必须填写，因此有一条错误消息，提示输入所有参数"，否则输入类似的内容，这将不允许完成上传.

I'm just completely stuck with what to do next. Upload Date is automatically filled in, but the rest of the parameters will vary based on the product and will have to be filled in manually. I also want it to be mandatory that every field is filled in, so have an error message saying "Enter all parameters" or something like that if they aren't, which wouldn't allow the upload to be completed.

之所以必须在Access中执行而不是仅仅在Excel中执行此操作，是因为Excel文件是由AutoCad Electrical生成的，并且可以包含哪些数据受到限制.我曾尝试在Excel中添加列并将其导入，但此方法确实有效，但老板说我们需要用户输入框来使事情变得更容易.

The reason why this is necessary to do in Access and simply not Excel is because the Excel file is generated by AutoCad Electrical and is limited in what data it can include. I tried adding the columns in Excel and importing them and it worked, but my boss said we NEED the user input box to make things easier.

这是我必须导入Excel文件并将其添加到正确表中的代码(_MCL UPLOAD).现在，我只希望能够将用户输入作为额外的列添加到此表中:

This is the code I have to import the Excel file and add it to the correct table (_MCL UPLOAD). Now I just want to be able to have the user inputs add to this table as extra columns:

Private Sub ImportMCL_Click()

On Error GoTo ErrorHandler
'disable ms access warnings
DoCmd.SetWarnings False

'load spreadsheet
DoCmd.TransferSpreadsheet acImport, 8, "_MCL UPLOAD", selectFile(), True
MsgBox "MCL Imported Successfully!"
're-enable ms access warnings
DoCmd.SetWarnings True

Exit Sub

ErrorHandler:
MsgBox "There was an Error: " & Err & ": " & Error(Err)


End Sub

Function selectFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
    If .Show Then
        selectFile = .SelectedItems(1)
    Else
        'stop execution if nothing selected
        End
    End If
End With

Set fd = Nothing
End Function

推荐答案

我的最终代码如下:

'Import MCL Files Code
Private Sub ImportMCL_Click()

On Error GoTo ErrorHandler
'disable ms access warnings
DoCmd.SetWarnings False

'load spreadsheet in .xls format
DoCmd.TransferSpreadsheet acImport, 8, "_MCL_UPLOAD", selectFile(), True
DoCmd.OpenQuery "UpdateMCL"
Call InsertInto_MASTER_UPLOAD
Call Delete_MCL_UPLOAD
MsgBox "MCL Imported Successfully!"
're-enable ms access warnings
DoCmd.SetWarnings True

Exit Sub

ErrorHandler:
MsgBox "There was an Error: " & Err & ": " & Error(Err)

End Sub

'Function called in Import MCL Code above
Function selectFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
    If .Show Then
        selectFile = .SelectedItems(1)
    Else
        'stop execution if nothing selected
        End
    End If
End With

Set fd = Nothing
End Function

'Function Used to Delete MCL Uploaded file after it's moved to Master Table
Sub Delete_MCL_UPLOAD()

Dim dbs As Database, rst As Recordset

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("default_cat.mdb")

' Delete employee records where title is Trainee.
dbs.Execute "DELETE * FROM " _
    & "_MCL_UPLOAD "

dbs.Close

End Sub

'Function Appends _MCL UPLOAD into the _MASTER_UPLOAD table
Sub InsertInto_MASTER_UPLOAD()

Dim dbs As Database

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("default_cat.mdb")

' Select all records in the New Customers table
' and add them to the Customers table.
dbs.Execute " INSERT INTO _MASTER_UPLOAD " _
    & "SELECT * " _
    & "FROM [_MCL_UPLOAD];"

dbs.Close

End Sub

我基本上创建了另一个表来将所有组合信息转储到其中.感谢您的所有帮助！

I basically created another table to dump all of the combined information into. Thanks for all of your help!

这篇关于在上载到Access数据库后将用户输入添加到Excel表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！