[Last Call] Learn how to a build a cloud-first strategyRegister Now


Reading from Excel worksheet cause run-time error 1004 cannot access 'spec.xls'

Posted on 2006-05-18
Medium Priority
Last Modified: 2012-05-05
I had an Excel spreadsheet that I'm reading the cells from into my VB apps  when the application first load.  There are a couple of forms that calls different data from the Excel spreadsheet and populate it.  When I do a clear all form and calls the function to populate those forms again, it however gives me the error that the excel file cannot be access.  I'm not sure if the reason for it to not work is because I have two different way of opening the excel file on different forms or what.  If I change the Excel file name for both method than it seems to work fine.  

Is there a way that I can have my main form reload as if I am opening the application all over again?  I think that is the easiest way to go since I'm doing a "New" to clear all text fields on all forms.

I tried doing a Load frmMain, but does not seem to be working when I click on the "New" button from a menu form that I created.

If not please see if changing anything below will help.

First method of opening:

Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

Set wb = xl.Workbooks.Open(App.Path & "\spec.xls")
Set ws = wb.Sheets("sheet1")

.......populate from Excel to VB Apps

Set xl = nothing

Another method is as followed:

Dim rsData As ADODB.Recordset
Dim conn As ADODB.Connection
Dim fileOpen As String

fileOpen = (App.Path & "\Spec.xls")

Set Conn = New ADODB.Connection
Set rsDatra = New ADODB.Recordset

conn.CursorLocation = adUseClient
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source" & fileOpen & ";Extended Properties=""Excel 8.0; HDR=NO" & """"

rsData.Open "Select * From Modes", conn, adOpenDynamic, adLockOptimistic

Do Until rsData.EOF
     If Not IsNull(rsData.Fields(0)) Then
         ......................loop around here
     End If

Question by:holemania
  • 2
LVL 13

Expert Comment

ID: 16715438
>Is there a way that I can have my main form reload as if I am opening the application all over again?

i think a better way is,

put the loading code in a function/sub,
and call that on Form_Load

the next time you should need the code, you can just call the same function/sub again

Author Comment

ID: 16733926
The closing the application and opening it is still giving me issues about not being able to access the Excel file.  It seems that I'm not closing the excel file or it is being use by one of the example above and not closing it after I'm done using it.  

The error seems that after I do the 2nd example from above and than go back to the the first example of opening the excel spreadsheet is when it errors out.  So I'm thinking that I"m not exiting or closing the excel application properly from the 2nd example.  If I have 2 separate excel files, than it is running fine.  If it is using the same file name than it gives me the error that it can't be access.  Can someone look at the code above and see if it is closing properly?  Maybe I need to use conn.close for the 2nd example?  

Author Comment

ID: 16734087
I guess I answered my own question from the above post.  I forgot to close the Excel spreadsheet preventing me from opening it within another form.

Accepted Solution

GranMod earned 0 total points
ID: 16949542
PAQed with points refunded (250)

Community Support Moderator

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

831 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