Solved

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

Posted on 2014-09-26
6
3,044 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

776 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