CODEWITHSUNDEEP
excelvbams-access
KultRussell
KultRussell

Reputation: 11

Importing Excel File to Access Form

I have exhausted every resource I can find and before I determine my Access DB to be corrupt I wanted to post this question:

I am working on a tool that will import the data from an Excel form that is to be distributed to a couple hundred individuals. I have created an Access form in parallel and I am trying to create a macro that will auto-populate the Access form fields with the data from the Excel form.

Here is the code I am working with:

Sub Import_Click()
Dim objDialog As Object
Dim CEtarget As Form
Dim CEfile As String
Set CEtarget = Forms("Data Entry")
Set CEsource = Excel.Application
Set objDialog = Application.FileDialog(3)

With objDialog
    .AllowMultiSelect = False
    .Title = "Select Form"
    .filters.Clear
    .filters.Add "*.xlsm; *.xlsx; *.xls", "*.xlsm; *.xlsx; *.xls"
    .Show
    If .SelectedItems.Count = 0 Then
        Exit Sub
    Else
        CEfile = .SelectedItems(1)
        CEsource.Workbooks.Open (CEfile)
        CEsource.Visible = True

    End If

CEtarget.FirstName.Value = CEsource.Range("B14")
End Sub

The error occurs at the following string:

Set CEtarget = Forms("Data Entry")

I receive the following error:

Run-time Error '-2146500594 (800f000e)': Method 'Item' of object 'Forms' failed

Not sure what could be wrong. I did have the code working at one point, but somehow I started seeing that error message.

EDIT:I tested the code while the form was in Design View and it executed without any problems. The error prompt still persists while in Form and Layout View.

Upvotes: 0

Views: 874

Answers (2)

RubberDuck
RubberDuck

Reputation: 12728

For future reference, decompiling the database will often fix this issue. Access will often get these "minor corruptions" when you're doing a lot of vba development. I think it has something to do with the way it stores the binaries. Be sure to also compact and repair after decompiling. That will remove any left over junk binaries. They cause the file size to bloat after a while.

Upvotes: 1

KultRussell
KultRussell

Reputation: 11

I never did figure out what the problem was, but I made a copy of the form and referenced that one instead. After that the code worked fine.

Upvotes: 1

Related Questions