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

Posted on 2006-05-18
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
    LVL 13

    Expert Comment

    >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

    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

    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

    PAQed with points refunded (250)

    Community Support Moderator

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    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…

    732 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

    21 Experts available now in Live!

    Get 1:1 Help Now