none
OpenText Run-time Error 1004 Cannot Access the File Since Upgrade from 2007 to 2013 RRS feed

  • Question

  • My team has a process that places a macro-enabled Excel file in a folder on a secure web server. When a user clicks on that Excel file from the web page and selects Open, the user receives a Windows Security message. The user then enters a username and password required to access the secure web server, and the Excel macro starts kicking off. It imports and processes a bunch of text files (over 100) from the same secure web server and produces a multi-tab report. After we updated from 2007 to 2013, the process still imports and processes several text files, but it stops approximately halfway through and returns an Error 1004: Cannot Access the File.

    If I download all of the files via FTP to a folder then Save and Run the macro-enabled Excel file from that folder, it works fine. It only blows up with the 1004 error when trying to run it from that secure web server as intended. I wonder if Excel somehow forgets the logon information after x number of requests out to the https server. Any ideas how to verify this and stop it if this is what's happening so that the Excel report completes?

    It is running this block of code where the error occurs:

    openname = trim(base_data_dir) & "/" & trim(cfilefull)
        On Error GoTo handler_c5
        Workbooks.OpenText Filename:=openname _
              , Origin:=xlWindows _
              , StartRow:=1 _
              , DataType:=xlDelimited _
              , TextQualifier:=xlDoubleQuote _
              , ConsecutiveDelimiter:=False _
              , Tab:=True _
              , Semicolon:=False, Comma:=False, Space:=False _
              , Other:=True, OtherChar:="|" _
              , FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1) _
                               , Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1) _
                               , Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1) _
                               , Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1) _
                               , Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1))
        On Error GoTo 0

    Thanks!

    Monday, October 10, 2016 3:42 PM

All replies

  • >>>If I download all of the files via FTP to a folder then Save and Run the macro-enabled Excel file from that folder, it works fine. It only blows up with the 1004 error when trying to run it from that secure web server as intended.

    According to your description, developers can use Automation in Microsoft Office to build custom solutions that use the capabilities and the features that are built into the Office product. Although such programmatic development can be implemented on a client system with relative ease, a number of complications can occur if Automation takes place from server-side code such as Microsoft Active Server Pages (ASP), ASP.NET, DCOM, or a Windows NT service.

    Microsoft strongly recommends that developers find alternatives to Automation of Office if they need to develop server-side solutions. Because of the limitations to Office's design, changes to Office configuration are not enough to resolve all issues. Microsoft strongly recommends a number of alternatives that do not require Office to be installed server-side, and that can perform most common tasks more efficiently and more quickly than Automation. Before you involve Office as a server-side component in your project, consider alternatives.

    Most server-side Automation tasks involve document creation or editing. Office 2007 supports new Open XML file formats that let developers create, edit, read, and transform file content on the server side. These file formats use the System.IO.Package.IO namespace in the Microsoft .NET 3.x Framework to edit Office files without using the Office client applications themselves. This is the recommended and supported method for handling changes to Office files from a service.

    For more information, please refer to Considerations for server-side Automation of Office

    Thanks for your understanding.
    • Proposed as answer by David_JunFeng Thursday, October 20, 2016 1:20 AM
    Tuesday, October 11, 2016 4:49 AM