Solved

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

Posted on 2014-09-26
6
3,019 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
  • 3
  • 3
6 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

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

Accepted Solution

by:
Rey Obrero 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

This collection of functions covers all the normal rounding methods of just about any numeric value.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now