Run-time error '1004' : Microsoft Excel cannot access the file ...

[RickRo]

I have a form that outputs data to an excel templete. I built and tested it on my machine, and it works just fine. When I attempt to change the syntex to a network drive I get the run time error.

Public Sub CreateSpreadsheet()

Dim RecSet As Recordset
Dim MyExcel As Excel.Application
Dim MyBook As Workbook
Dim MySheet As Worksheet

Set MyExcel = CreateObject("Excel.Application")
MyExcel.DisplayAlerts = False
[COLOR="Red"]Set MyBook = MyExcel.Workbooks.Add("\\agnbot\is doc$\Desktop\Inventory\Asset_Team\asset_update_collection-templete")[/COLOR]
Set MySheet = MyBook.Worksheets(4)
Set RecSet = CurrentDb.OpenRecordset("tblTemp")

    MySheet.Range("a8").CopyFromRecordset RecSet

    MyBook.SaveAs FileName:="\\agnbot\is doc$\Desktop\Inventory\Asset_Team\asset_update_collection-templete " & _
    Format(Now(), "mm_dd_yyyy hh mm AMPM"), FileFormat:=xlNormal, Password:="", _
    WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

MyBook.Close

MyExcel.Quit

Set RecSet = Nothing
Set MyExcel = Nothing
Set MyBook = Nothing
Set MySheet = Nothing

End Sub

 

[MarkK]

Just checked the object browser for the Excel.Workbooks.Add() method and it looks like the parameter you should pass to the function is the path to a template file. It appears that you are passing in the path to a folder.
hth

 

[boblarson]

Need to use the file extension too.