Photo Gallery Member List Search Calendars FAQ Ticket List Log Out


Looked all over for this...

  		Logged in as: Guest
arrSession:exec spGetSession 2,2,61379
Active Users: There are 0 members and 0 guests.
Users viewing this topic: none
 

 

  
 
  Printable Version
All Forums >> [Scripting] >> WSH & Client Side VBScript >> Looked all over for this...
  Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks! 		Page: [1]
Login
Message << Older Topic   Newer Topic >>
Looked all over for this... - 6/15/2008 6:09:35 PM   
  patgenn123

 

Posts: 37
Score: 0
Joined: 1/14/2008
Status: offline 		I am VERY new to scripting and I am already frustrated I can't figure this out.

Here's what I have so far:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = FALSE
Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings\Pat\Desktop\ACT! Excel ole db.xls")
Set objWorksheet = objWorkbook.Worksheets(1)

objWorkbook.Save()
objExcel.Quit


I know line 4  doesn't belong there because the file ALREADY is running and I don't need another one to be opened.  I just need to work with the existing one.

I am trying to SAVE and CLOSE an ALREADY running excel file THAT ALREADY HAS A FILE NAME that is hidden from view.

WHAT do I need to do to change this to make it work?

Thanks!

Pat

< Message edited by patgenn123 -- 6/15/2008 6:12:02 PM >
 
 
Post #: 1
 
RE: Looked all over for this... - 6/15/2008 6:22:45 PM   
  ehvbs

 

Posts: 2065
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		Hi ,

try to use GetObject instead of CreateObject:

Sorry, my fault: Ignore the following block of bad code (I edited the wrong file and tested
the unchanged version).


      

This works; given a suitable recalc.xls:


      

Good luck!

ehvbs

< Message edited by ehvbs -- 6/15/2008 8:42:32 PM >

(in reply to patgenn123)
 
 
Post #: 2
 
RE: Looked all over for this... - 6/15/2008 6:46:16 PM   
  patgenn123

 

Posts: 37
Score: 0
Joined: 1/14/2008
Status: offline 		ehvbs,

Thanks!  I looked at your script and it's way way way over my head.  I don't get it and right now I am getting pretty mad(not at you), but at myself.

All I did is replace CreateObject with GetObject in the first line and I get this error:

---------------------------
Windows Script Host
---------------------------
Script: C:\Documents and Settings\Pat\Desktop\rthdfgh.vbs
Line: 1
Char: 1
Error: Invalid syntax
Code: 800401E4
Source:  (null)
---------------------------
OK  
---------------------------


Can you help me work with what I have?  I am really at my whits end and don't know what to do.  I know this K.I.S.S. method is used around here.
Can you keep it real simple if you would like to help me?

Sincerely,

Pat

(in reply to ehvbs)
 
 
Post #: 3
 
RE: Looked all over for this... - 6/15/2008 8:55:26 PM   
  ehvbs

 

Posts: 2065
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		Hi patgenn123,

sorry about my blunder. This is a KISS version (assuming your .xls is open, no error handling):

Dim oFS          : Set oFS      = CreateObject( "Scripting.FileSystemObject" )
Dim objExcel     : Set objExcel = GetObject( , "Excel.Application" )
objExcel.Visible       = True
objExcel.DisplayAlerts = False
Dim objWorkbook  : Set objWorkbook  = objExcel.Workbooks( 1 )
Dim objWorksheet : Set objWorksheet = objWorkbook.Worksheets( 1 )

objWorkbook.Save

as you can see, I didn't neither hide or close the open .xls (you'll get Excel
zombies or a surprised user otherwise).

In your shoes, I'd carefully consider whether changing the file while a user
has it opened is really a good idea.

Regards

ehvbs

(in reply to patgenn123)
 
 
Post #: 4
 
RE: Looked all over for this... - 6/16/2008 3:33:31 AM   
  patgenn123

 

Posts: 37
Score: 0
Joined: 1/14/2008
Status: offline 		ehvbs,

Thank you again.  Yet, I still don't understand the script.

I tried to be too general not thinking that there was any difference(because I didn't know)

Here is what I am trying to do:

I have a database(ACT!) which I am pretty sure is irrelevant.
When I open ACT!, I open a "hidden" Excel ole db which is a refresh query on internal data from the database.
 
I am sorry that I didn't say this before because I didn't think it would be an issue, but now it looks like it might be.

Ultimately,  here is what I am looking to do:
When ACT! closes,  close and save the open/hidden Excel file.
((all of this is to be done with Excel being completely hidden))
 
This is really what I want to do.
 
Thanks!

Pat

(in reply to ehvbs)
 
 
Post #: 5
 
RE: Looked all over for this... - 6/16/2008 4:16:03 AM   
  ehvbs

 

Posts: 2065
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		Sorry, patgenn123,

I don't know anything about the ACT! database/application. Perhaps you should consult
the ACT! Docs / the software company's helpdesk or forum.

Does the file C:\Documents and Settings\Pat\Desktop\ACT! Excel ole db.xls
- albeit hidden - exists permanentely on your HD? Can you copy it?

Regards

ehvbs

(in reply to patgenn123)
 
 
Post #: 6
 
RE: Looked all over for this... - 6/16/2008 4:56:39 AM   
  patgenn123

 

Posts: 37
Score: 0
Joined: 1/14/2008
Status: offline 		ehvbs,

I am 100% sure the database is irrelevant to this request.  One could open the excel file and Excel"refreshes itself" without ACT! being open(even though nothing is being fed from the database when ACT! is closed).

I didn't want to complicate things by mentioning the database. 

The reality is that ACT! will be closed when I save/close Excel.

All I am looking to do is save/close that Excel file all while it's hidden.  The excel file NEEDS to be hidden, saved and closed right after ACT! is closed.

That's all!!

Thnak you so kindly,

Pat

(in reply to ehvbs)
 
 
Post #: 7
 
RE: Looked all over for this... - 6/16/2008 5:20:34 AM   
  ehvbs

 

Posts: 2065
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		Sorry, patgenn123,

I still don't get it. Obviously the .xls interacts with the database. Does it execute
a query? Are you interested in a copy of these data? Can't you look at the connection
parameters and the query in the .xls and ask the database directly?

Does " The reality is that ACT! will be closed when I save/close Excel." mean: "I want
to work with the .xls when/right after I closed ACT!"? Then the opened .xls will be
empty, because "nothing is being fed from the database when ACT! is closed)"?

Why the hiding?

I think you should execute the steps you want to do 'by hand' - without any VBScript.
After you get a stable process, you can think about doing it automatically with a script.

I feel that the database (and how it interacts with the .xls) is very pertinent to a solution
and I kind of loose all hope that I can help you.

Sorry

ehvbs

(in reply to patgenn123)
 
 
Post #: 8
 
 
 
 

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.
All Forums >> [Scripting] >> WSH & Client Side VBScript >> Looked all over for this... Page: [1]
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts




Forum Software © ASPPlayground.NET Advanced Edition 2.5.5 ANSI