Solved

Run-time error '91': Object variable or With block variable not set

Posted on 2014-09-26
6
3,066 Views
Last Modified: 2014-09-26
I have a run-time error when I try to import an excel file into ms access table.
I want excel and ms access in the same folder no matter where the location is.

Sub ImportExcelToAccess()

      Dim xlsht As Excel.Worksheet
      Dim xlWrkBk As Excel.Workbook
      Dim myRec As DAO.Recordset
      Dim xlApp As Excel.Application
      Dim xlWrksht As Excel.Worksheet
      Dim i As Long
      Dim sPathAndFile As String
      Dim sPath As String
      Dim sFile As String

    Set myRec = CurrentDb.OpenRecordset("tblImport_Main")
    Set xlApp = CreateObject("Excel.Application")

    sFile = "Import_Main.xlsx"
    sPath = ActiveWorkbook.Path & "\" & sFile
    
    sPathAndFile = sPath & sFile
    Set xlWrkBk = Workbooks.Open(sPathAndFile)
    
    Set xlWrksht = xlWrkBk.Sheets(1)
       
     For i = 2 To 100
      
        myRec.AddNew
        On Error Resume Next
        myRec.Fields(0) = xlWrksht.Cells(i, "A")
        myRec.Fields(1) = xlWrksht.Cells(i, "B")
        myRec.Fields(2) = xlWrksht.Cells(i, "C")
        myRec.Fields(3) = xlWrksht.Cells(i, "D")
        myRec.Fields(4) = xlWrksht.Cells(i, "E")
        myRec.Fields(5) = xlWrksht.Cells(i, "F")
        myRec.Fields(6) = xlWrksht.Cells(i, "G")
        myRec.Fields(7) = xlWrksht.Cells(i, "H")
        myRec.Fields(8) = xlWrksht.Cells(i, "I")
        myRec.Fields(9) = xlWrksht.Cells(i, "J")
        myRec.Update

    Next
    Set myRec = Nothing
    Set xlApp = Nothing
       
   Exit Sub
            
End Sub

Open in new window


Thank you for your help.
0
Comment
Question by:Queennie L
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40346380
change this

sPath = ActiveWorkbook.Path & "\" & sFile

to

sPath = currentproject.Path & "\" & sFile
0
 

Author Comment

by:Queennie L
ID: 40346395
@Rey Obrero:

I have a run-time error '1004': Sorry, we couldn't find the file. Is it possible it was moved, renamed or deleted?

It stops right here: >>>Set xlWrkBk = Workbooks.Open(sPathAndFile)

Thank you for your help.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40346433
where is the excel file located?
i assume you are calling the codes from Access..

where is the Access app located?

is the Excel file and access app in the same folder?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:Queennie L
ID: 40346443
Yes. Both Excel and Access are in the same folder.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40346508
change this

Set xlWrkBk = Workbooks.Open(sPathAndFile)

to

Set xlWrkBk = Workbooks.Open(sPath)
0
 

Author Closing Comment

by:Queennie L
ID: 40346600
This solved the error.

You are smart!

Thank you for your help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question