Reading from Excel worksheet cause run-time error 1004 cannot access 'spec.xls'
Posted on 2006-05-18
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