Do not use on any
shared computer
May 17, 2008 08:08am pdt
10.19.2004 at 01:20AM PDT, ID: 21173490
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

CreateObject("EXCEL.APPLICATION"), Open Workbook, SaveAs new name doesn't work any more ...
Tags: excel
Hi all,
The following script works like a charm on my workstation

<dummy_script.vbs>
    Dim objExcel
    Dim objWorkBook
    Set objExcel = CreateObject("EXCEL.APPLICATION")
    Set objWorkBook = objExcel.Workbooks.Open("C:\test\test.xls")
    objWorkBook.SaveAs ("C:\test\test2.xls")
    objWorkBook.Close True
    Set objWorkBook = Nothing
    Set objExcel = Nothing
</dummy_script.vbs>

It used to work on a server that generates XL files on the fly and sends them by email upon request in an intranet application.
The code above is just a sample, the actual app is VB6 and involves DB access, running macros, ...
Trying to narrow the scope I found out that the simple script above does not work any more on the server.
The test.xls file is the most simple XL file ever, and the path is OK
The NT account I use definitely has write permissions on the target directory, and test2.xls does not exist when I run the script.

Excel is installed on the server and works normally when used as a client app.
But it seems to fail miserabily when used as an activeX object. The script above says it can't find the file, but both the directory and the fiel definitely exist on the server.

I also checked that the script does not fail due to a ghost/remanant excel.exe process in memory,
using windows toolkit (tlist, kill, ...) : no ghost excel.exe ...

Any ideas what could break / alter the way excel works when used as an activeX object from another App/Script ?

Any comments welcome

Hilaire

Start your free trial to view this solution
Question Stats
Zone: Software
Question Asked By: Hilaire
Solution Provided By: MalicUK
Participating Experts: 2
Solution Grade: A
Views: 1637
Translate:
Loading Advertisement...
10.19.2004 at 01:26AM PDT, ID: 12345375

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
10.19.2004 at 01:30AM PDT, ID: 12345391

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
10.19.2004 at 01:43AM PDT, ID: 12345437

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
10.19.2004 at 01:46AM PDT, ID: 12345446

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
10.19.2004 at 01:49AM PDT, ID: 12345455

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
10.19.2004 at 01:56AM PDT, ID: 12345481

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
10.19.2004 at 02:03AM PDT, ID: 12345504

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
10.19.2004 at 02:15AM PDT, ID: 12345544

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
10.19.2004 at 02:31AM PDT, ID: 12345606

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
10.19.2004 at 02:33AM PDT, ID: 12345617

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
10.19.2004 at 02:32PM PDT, ID: 12352245

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
10.19.2004 at 08:53PM PDT, ID: 12354764

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
Loading Advertisement...
Microsoft
  • Internet Protocols
  • Applications
  • Development
  • OS
  • Hardware
  • Windows Security
Apple
  • Operating Systems
  • Hardware
  • Programming
  • Networking
  • Software
Internet
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Spy / Ad Blockers
  • Web Browsers
  • New Net Users
  • Web Development
  • Chat / IM
  • Anti Spam
  • Web Servers
  • Anti-Virus
  • Email Clients
Gamers
  • Tips
  • Online / MMORPG
  • Puzzle
  • Emulators
  • Action / Adventure
  • Role Playing
  • Consoles
  • Game Programming
  • Strategy
  • Sports
  • Misc
  • Computer Games
Digital Living
  • Hardware
  • New Net Users
  • New Users
  • Software
  • Digital Music
  • Gaming World
  • Home Security
  • Apple
  • Networking Hardware
Virus & Spyware
  • Vulnerabilities
  • IDS
  • Encryption
  • Anti-Virus
  • Operating Systems Security
  • Software Firewalls
  • WebApplications
  • Cell Phones
  • Operating Systems
  • Internet
  • Hardware Firewalls
Hardware
  • Handhelds / PDAs
  • Displays / Monitors
  • Components
  • Networking Hardware
  • Peripherals
  • Laptops/Notebooks
  • Storage
  • Servers
  • Desktops
  • New Users
  • Misc
  • Apple
Software
  • System Utilities
  • Industry Specific
  • Network Management
  • Photos / Graphics
  • Page Layout
  • VMWare
  • Misc
  • Web Development
  • OS
  • CYGWIN
  • Voice Recognition
  • Message Queue
  • Quality Assurance
  • Security
  • Firewalls
  • MultiMedia Applications
  • Development
  • Database
  • Office / Productivity
  • Business Management
  • OS/2 Apps
  • Server Software
  • Internet / Email
ITPro
  • OS
  • Storage
  • Encryption
  • Operating Systems Security
  • Apple Hardware
  • Laptops & Notebooks
  • Servers
  • Networking Hardware
  • Peripherals
  • Devices
  • Displays / Monitors
  • WebTrends / Stats
  • Search Engines
  • Firewalls
  • WebApplications
  • IDS
  • Vulnerabilities
  • Email Clients
  • File Sharing
  • Spy / Ad Blockers
  • Web Browsers
  • Web Servers
  • Networking
  • Anti-Virus
  • Chat / IM
  • Anti Spam
Developer
  • Web Servers
  • Web Browsers
  • Game Programming
  • Dev Tools
  • Industry Specific
  • Office / Productivity
  • Database
  • CYGWIN
  • Web Development
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Programming
  • Content Management
  • Application Servers
  • Protocols
Storage
  • Removable Backup Media
  • Storage Technology
  • Servers
  • Grid
  • Remote Access
  • Backup / Restore
  • Misc
  • Hard Drives
OS
  • Miscellaneous
  • Security
  • Development
  • Linux
  • VMWare
  • MainFrame OS
  • Unix
  • Apple
  • OS / 2
  • AS / 400
  • BeOS
  • Microsoft
  • VMS / OpenVMS
Database
  • Oracle
  • Miscellaneous
  • MySQL
  • Software
  • Sybase
  • Contact Management
  • PostgreSQL
  • Data Manipulation
  • Clarion
  • InterSystems Cache
  • Siebel
  • MUMPS
  • OLAP
  • SQLBase
  • SAS
  • GIS & GPS
  • 4GL
  • Berkeley DB
  • DB2
  • Informix
  • Interbase / Firebird
  • FoxPro
  • Reporting
  • LDAP
  • Filemaker Pro
  • MS SQL Server
  • dBase
  • MS Access
Security
  • Misc
  • Web Browsers
  • Software Firewalls
  • Operating Systems Security
  • File Sharing
  • Spy / Ad Blockers
  • Vulnerabilities
  • WebApplications
  • IDS
  • Anti-Virus
  • Encryption
  • Anti Spam
  • Email Clients
  • VPN
  • Chat / IM
Programming
  • Editors IDEs
  • Installation
  • Handhelds / PDAs
  • Multimedia Programming
  • System / Kernel
  • Algorithms
  • Game
  • Signal Processing
  • Project Management
  • Open Source
  • Database
  • Misc
  • Languages
  • Processor Platforms
  • Theory
Web Development
  • Scripting
  • Blogs
  • Web Servers
  • Software
  • Search Engines
  • Web Graphics
  • Images
  • Internet Marketing
  • Images and Photos
  • Components
  • Document Imaging
  • Web Languages/Standards
  • Illustration
  • WebApplications
  • Fonts
  • WebTrends / Stats
  • Authoring
  • Digital Camera Software
  • Miscellaneous
Networking
  • Protocols
  • Apple Networking
  • Network Management
  • Message Queue
  • Application Servers
  • Content Management
  • File Servers
  • Email Servers
  • Misc
  • Java Editors & IDEs
  • Wireless
  • Networking Hardware
  • Backup / Restore
  • System Utilities
  • ISPs & Hosting
  • Web Servers
  • Storage Technology
  • Removable Backup Media
  • Servers
  • Broadband
  • Grid
  • OS / 2
  • Novell Netware
  • Unix Networking
  • Windows Networking
  • Security
  • Telecommunications
  • Operating Systems
  • Linux Networking
Other
  • Community Advisor
  • Lounge
  • Community Support
  • New Net Users
  • Philosophy / Religion
  • Math / Science
  • Miscellaneous
  • URLs
  • Expert Lounge
  • Politics
  • Puzzles / Riddles
Community Support
  • Suggestions
  • New to EE
  • New Topics
  • Community Advisor
  • CleanUp
  • Announcements
  • General
  • Feedback
  • Input
  • EE Bugs
 
10.19.2004 at 01:26AM PDT, ID: 12345375

Rank: Genius

Did you try to work with the IP addres of the server instead the C:\ prompt?

I can imagine that some stations are looking at their own C-drive

regards,
Jeroen
 
10.19.2004 at 01:30AM PDT, ID: 12345391
I run this script on the server (remote control via terminal services).
So the path is OK

Thks
Hilaire
 
10.19.2004 at 01:43AM PDT, ID: 12345437

Rank: Genius

I understand that the path is a path on the local machine then and not on the server. otherwise I can imagine that this can lead to problems sometimes the OS thinks C:\test refers to a local address instead of remote address
 
10.19.2004 at 01:46AM PDT, ID: 12345446
Same thing if I run the script on the server. Changing the path to another path that exists only on the server still gives the same err.
 
10.19.2004 at 01:49AM PDT, ID: 12345455

Rank: Genius

Hi Hilaire,

Looking at your code are you trying to run this from a web page. Either ASP, HTM or any of the others? Especially ASP, P2P or another server-side web page. Because if so then you are going to fail with that code as it doesn't take into account who is logged in or any of their permissions. So if it is serverside page then you need to firstly use the full UNC path (in the format \\server\share\path\file.ext). Then you need to make sure that your web server which is processing the server side page has access to the file. I have tried to do this in the past with pages and had all sorts of problems. I actually had more success with javascript - I'll dig out some code for you.

Cheers,
MalicUK.
 
10.19.2004 at 01:56AM PDT, ID: 12345481

Rank: Genius

Hilaire, here is the javascript I used.

<script language="javascript">
function openExcel(strFileName) {
      var yourAddress = "//server/share/folder/subfolder/";
        openExcelDocPath(yourAddress + strFileName, false);
   }
function openExcelDocPath(strLocation, boolReadOnly) {
        var objExcel;
        objExcel = new ActiveXObject("Excel.Application");
        objExcel.Visible = true;
        objExcel.Workbooks.Open(strLocation, false, boolReadOnly);
   }
</script>

Then I called it in this case by a hyperlink using:

<P><A HREF="#" onClick="openExcel('154.xls');"">1.5.4 File Description</A></P>

Although it can of course be called anywhere where javascript can be called using:

openExcel('myfile.xls');
 
10.19.2004 at 02:03AM PDT, ID: 12345504
Thks MalicUK,
this code is (tranlated from VB6 to VBS) part of a VB6 server component that runs as a service on the server, "listens" for new jobs from a database-hosted queue and generates XL files accordingly.
The service is run using a special account that has sufficient rights on the target directory.
Eg I can create a file manually in the target directory when connected as this user on the server.

I provided sample code to narrow the scope a show a simple operation that used to work, and does not work anymore since yesterday (as a side not, we changed the security level of macros/VBA in excel from "average" to "low" cause a company did not send us the latest security certificates).

Maybe there's a prompt or a new server settings that prevents Excel to run in silent mode (The app runs as a NT service, so I don't want excel to show)

Hilaire

 
10.19.2004 at 02:15AM PDT, ID: 12345544

Rank: Genius

OK, just majorly confused there.

Right have you tried dim-ing objExcel and objWorkbook as Objects rather than variables? I don't know that this is the problem but I just am trying anything!

    Dim objExcel As Object
    Dim objWorkBook As Object
    Set objExcel = CreateObject("EXCEL.APPLICATION")
    Set objWorkBook = objExcel.Workbooks.Open("C:\test\test.xls")
    objWorkBook.SaveAs ("C:\test\test2.xls")
    objWorkBook.Close True
    Set objWorkBook = Nothing
    Set objExcel = Nothing

Accepted Solution
 
10.19.2004 at 02:31AM PDT, ID: 12345606
>>OK, just majorly confused there<<
Sorry I'm not a native english-speaker so my comments happen to be far from crystal-clear ;-)

in VB6 I tried both fully qualified object types  (excel.application, excel.workbook) and generic object.
sample code is VBS, so no datatype accepted.

In fact I think the question could be shortened as follows :
- yesterday I could use Excel as an ActiveX object in my VB6/VBS code
- today it doesn't work any more
- meanwhile I just changed security level for macros from "average" to "low" (reverting to "average" doesn't solve the prb)
- excel is still OK when used as a client app

Is there a way to fix it whithout re-installing Excel ?
 
10.19.2004 at 02:33AM PDT, ID: 12345617

Rank: Genius

instead of re-installing Excel you might try the startup switches:
http://support.microsoft.com/default.aspx?scid=kb;en-us;211481&Product=xlw2K
   /regserver       Forces Excel to register itself and then quit. Use this
                    switch when you want Excel to rewrite all its
                    registry keys and reassociate itself with Excel files,
                    such as workbooks, charts, and so on.

                    Example: /regserver
Assisted Solution
 
10.19.2004 at 02:32PM PDT, ID: 12352245
Thanks roos01, I didn't remember this
I finally got it to work in VBS using /unregserver and /regserver,

my VB6 still failed but I noticed that the excel version on the server had been updated.
my VB6 code used
dim objwkb as Excel.Workbook
I got it to work again by using object datatype + late binding
dim objwkb as object
set objwkb = createobject("Excel.Workbook")



 
10.19.2004 at 08:53PM PDT, ID: 12354764

Rank: Genius

glad to hear it worked for you.
thanks for the grade!
Jeroen
 
 
20080206-EE-VQP-25