Run Time error 1004 - Cannot access *.xls

[bama]

Any help would be great. I am stuck with this issue for last two days. Thanks

I am trying to update a excel sheet from access using access macro. The code works fine for second object but not for first.

I am getting the error on objxl.Workbooks.Open FileName:=Trim(strfname)

Note: I am linking this tables to create table in access.

Here is my code

Public objxl, objxl1, objxl2 As excel.Application


Private Sub Command2_Click()
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim dbs As Database
Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("CUST", dbOpenDynaset)
Set rst2 = dbs.OpenRecordset("customer", dbOpenDynaset)
DoCmd.Hourglass True
Dim strPath As String
Dim xlApp As Object
Dim xlSheet As Object
Dim strfname As String

Set objxl = CreateObject("Excel.application")
Set objxl2 = CreateObject("Excel.application")
strfname = "C:\Contract_IDS\AHC_CUSTOMER.xls"
'objxl.Workbooks.Open strfname

objxl.Workbooks.Open FileName:=Trim(strfname)
objxl.Application.Run "AHC_CUST"
objxl.Workbooks.Close
objxl.Quit

strfname = "C:\Contract_IDS\AHC_CUSTOMER_HAS.xls"

objxl2.Workbooks.Open FileName:=strfname
objxl2.Application.Run "AHC_Customer_Has"
objxl2.Workbooks.Close
objxl2.Quit

Set objxl = Nothing
Set objxl2 = Nothing
end sub

In excel I have the following code:

Selection.QueryTable.Refresh BackgroundQuery:=False ActiveWorkbook.Save

any help would be great. Thanks

 

[boblarson]

First up - in VBA you can't use the same syntax as in VB. You need to declare objects completely:

Public objxl As excel.Application, objxl1 As excel.Application, objxl2 As excel.Application

Second, this line:

objxl.Workbooks.Open FileName:=Trim(strfname) objxl.Application.Run "AHC_CUST"

is not valid.

Either it is a typo or you really do have it all on one line (which can't be).

 

[bama]

objxl.Workbooks.Open FileName:=Trim(strfname) objxl.Application.Run "AHC_CUST"
That's just typo error each is seperate line
objxl.Workbooks.Open FileName:=Trim(strfname)
objxl.Application.Run "AHC_CUST"

I tried Declaring each object seperate still I am getting same error.

Thanks.

 

[bama]

Any help would be great. I thought it got fixed. But same thing is still happening. I don't understand why its just giving me error for first file, not to the second one. If I unlink the excel from access linked manger it works fine. The second file works fine with link.

Very urget help please.


Regards
-Bama

 

[bama]

Fixed the problem. Thanks for the offer though.

 

[boblarson]

Fixed the problem. Thanks for the offer though.

The person you responded to was just a spammer using a bogus post to post with spam links in their signature.

 

[Rick Stanich]

Fixed the problem. Thanks for the offer though.

Your not going to tell anyone what the solution was?