Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 3rd, 2011, 02:45 PM
Authorized User
 
Join Date: Aug 2011
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default Not sure Excel issue or Batch file issue - Application.Workbooks("Book2.xls").Close

Hi

I've got an issue but not sure if it's related to excel or to the batch code I use.

I'm using a batch file so that I can schedule a process to run the file "Book1.xls".

When I run the batch file, which opens Book1.xls, it gives me this error when I use the code Application.Workbooks("Book2.xls").Close {where I use Book1.xls to close the (already) open excel Book2.xls}

But when I run Book1.xls by double-clicking and enabling macros, it runs perfectly.

Any ideas in terms of batch code? This is the code I'm using:

@echo off

echo off

SET PROD_Folder=S:\General\Team\

"C:\Program Files\OFFICE11\EXCEL.EXE" "E:\Excel_Practise\Book1.xls" /p".\" /e

echo Copy to prefered location!

copy "E:\Excel_Practise\Book2.xls" "%PROD_Folder%Book2.xls"

echo DONE!

exit

* My excel code saves the file Book1.xls and places it as Book2.xls at the "PROD_Folder" location.

Can anyone help me on this?

Justin
 
Old October 3rd, 2011, 03:56 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

i'm thinking you either need to digitally sign the workbook or make the folder it's in a trusted location. the problem i believe is the fact that you have to click enable macros, which you can't do with a batch file.

In Excel, try going to File > Options > Trust Center, then clicking on the "Trust Center Settings" button, then on the "Add New Location" button and take it from there.

Never tried signing a workbook, but that functionality is usually on the Start Menu > Microsoft Office > Microsoft Office 2010 Tools > Digital Certificate For VBA Projects

Good luck, gonna be away from comp, let me know if you figure it out

Mike
 
Old October 4th, 2011, 01:23 PM
Authorized User
 
Join Date: Aug 2011
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Mike... Well that doesn't really help me out much!

My problem is that when i run my batch file, it opens my excel and runs the macro automatically but gives me an error at the above mentioned line.

Well if I open the excel manually and run the macro, it executes line where the error used to occur and gives me my required output.

Need some real help on this one!!

Justin
 
Old October 4th, 2011, 03:09 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

when you open the workbook manually, do you have to click options, then click enable macros?





Similar Threads
Thread Thread Starter Forum Replies Last Post
Application.Workbooks("try.xls").Close problem justinferns Excel VBA 8 October 14th, 2011 01:34 PM
Chapter 2 "myListView" issue Ysosrs BOOK: Professional Android 2 Application Development 0 September 26th, 2011 08:12 PM
"Unknown Zone (Mixed)" Issue EvetsM BOOK: Professional Ajax 2nd Edition ISBN: 978-0-470-10949-6 1 July 11th, 2011 07:53 AM
Add a CheckBox DataColumn to my DataGridView, Null format: "" or "True" but Error: F ismailc C# 2005 0 September 25th, 2009 04:56 AM
Issue using "saxon:line-number()" in command line XSL with Saxon9.jar ROCXY XSLT 3 June 3rd, 2009 04:24 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.