Importing Excel into a premade Access Form

[Def294]

Spent hours searching the internet before resorting to pleading for help. Im trying to import a CSV file from excel into an access form that I have already made. Simply put I don't want to have to type data twice and im trying to eliminate any room for error. Any tips or ideas would be greatly appreciated.

 

[pbaldy]

Well, you don't import data into a form; you import into a table, then display the data with a form. Look at TransferSpreadsheet and TransferText in help.

 

[Def294]

I'm trying to make a database where they could print out the form from each entry. It wouldn't be a one time import from exel, it would be multiple entries a day. Im trying to eliminate any room for error on their part, while being able to consolidate all the data into one database. If its not possible to import directly into the form, are there any recommendations on what would be the easiest way to accomplish close to what im trying to do.

 

[pbaldy]

I won't say it can't be done, but it would be more complicated (you'd use automation to copy values from the spreadsheet to the form). If you're trying to let the user approve the data, perhaps you can import into a local table, base your form on that and have code to copy the data into the regular table.

 

[dbay]

Well, you don't import data into a form; you import into a table, then display the data with a form. Look at TransferSpreadsheet and TransferText in help.

I import data from an Excel sheet into a form so it can be modified if need be before saving it to a table.

Private Sub CMD_Browse_Files_Click()
On Error GoTo err_proc
    Dim OBJ_File As Object
    Dim OBJ_XL As Excel.Application
    Dim OBJ_WKB As Excel.Workbook
    Dim OBJ_SHT As Excel.Worksheet
    Dim STR_Path As String
 
    STR_Path = Browse_Files
 
    If STR_Path <> "" Then
        DoCmd.Hourglass True
        Me.TXT_File_Path = STR_Path
        Set OBJ_XL = New Excel.Application
        With OBJ_XL
            .Visible = False
            Set OBJ_WKB = .Workbooks.Open(STR_Path)
            Set OBJ_SHT = OBJ_WKB.Worksheets("Sheet1")
            With OBJ_SHT
 
                Me.TXT_Example_1 = .Cells(24, "B")
                Me.TXT_Example_2 = .Cells(20, "C")
                Me.TXT_Example_3 = .Cells(21, "C")
                Me.TXT_Example_4 = .Cells(22, "C")
                Me.TXT_Example_5 = .Cells(23, "C")
 
            End With
        End With
        OBJ_WKB.Close False
        OBJ_XL.Quit
    End If
 
exit_proc:
On Error Resume Next
    DoCmd.Hourglass False
    Set OBJ_File = Nothing
    Set OBJ_SHT = Nothing
    Set OBJ_WKB = Nothing
    Set OBJ_XL = Nothing
    Exit Sub
err_proc:
    Call Error_Logging("FRM_Edit_FollowUps", "CMD_Browse_Files_Click", Erl, Err.Number, Err.Description)
    Resume exit_proc
End Sub

 

[pbaldy]

Which is exactly what I described here:

I won't say it can't be done, but it would be more complicated (you'd use automation to copy values from the spreadsheet to the form).