Photo Gallery Member List Search Calendars FAQ Ticket List Log Out


Can VBScript update links in an Excel Workbook

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

 

  
 
  Printable Version
All Forums >> [Scripting] >> WSH & Client Side VBScript >> Can VBScript update links in an Excel Workbook
  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 >>
Can VBScript update links in an Excel Workbook - 10/20/2008 12:51:51 AM   
  TechAnalyst

 

Posts: 12
Score: 0
Joined: 10/20/2008
Status: offline 		Hello Everybody

I'm new here and to VBScript and looking for some help

I'm trying to write a Script to open an Excel 2007 .xlsm Workbook and update it's links.

Is it even possible?

I have the code working internally in a macro but wold like to make it external in a script for compatability with other types of workbooks

The code I have in the script at the moment is as follows

Sub Updatemaster(mastername)
  Dim objExcel, objWorkbook, fn, newname, i,
  Set objExcel = CreateObject("Excel.Application")
  objExcel.Visible = True
  Set objWorkbook = objExcel.Workbooks.Open(mastername)
' Turn off communication with user
  objExcel.DisplayAlerts = False
' Update all links to the Master file
  objWorkbook.UpdateLink
etc...

There is more but the script stops working at "objWorkbook.UpdateLink"

Can somebody please help me?

Thanks





 
 
Post #: 1
 
RE: Can VBScript update links in an Excel Workbook - 10/20/2008 1:40:10 AM   
  ebgreen


Posts: 5251
Score: 31
Joined: 7/12/2005
Status: offline 		Have you tried it in a vbscript yet? In general most things that work in a macro work pretty much the same way in a script.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to TechAnalyst)
 
 
Post #: 2
 
RE: Can VBScript update links in an Excel Workbook - 10/20/2008 1:54:29 AM   
  TechAnalyst

 

Posts: 12
Score: 0
Joined: 10/20/2008
Status: offline 		Thanks

And yes I have tried it as a VB Script

The code I posted was taken from the script and whilst the code works in the macro it doesn't in the script.

It's really puzzling

Any Ideas?

They'll be greatfully received


(in reply to ebgreen)
 
 
Post #: 3
 
RE: Can VBScript update links in an Excel Workbook - 10/20/2008 2:01:06 AM   
  ebgreen


Posts: 5251
Score: 31
Joined: 7/12/2005
Status: offline 		What doesn't work? Do you get an error? If you do, what is the error?

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to TechAnalyst)
 
 
Post #: 4
 
RE: Can VBScript update links in an Excel Workbook - 10/20/2008 2:10:09 AM   
  TechAnalyst

 

Posts: 12
Score: 0
Joined: 10/20/2008
Status: offline 		It doesn't update the links and then doesn't continue to the next part of the script

How can I find out what the error is?

I'm calling the Script from an HTML page if that helps

(in reply to ebgreen)
 
 
Post #: 5
 
RE: Can VBScript update links in an Excel Workbook - 10/20/2008 2:20:17 AM   
  ebgreen


Posts: 5251
Score: 31
Joined: 7/12/2005
Status: offline 		Do you have On Error Resume Next in the script? If so, comment it out. If it is at the op of the script, leave it commented out because that is evil.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to TechAnalyst)
 
 
Post #: 6
 
RE: Can VBScript update links in an Excel Workbook - 10/20/2008 2:23:44 AM   
  TechAnalyst

 

Posts: 12
Score: 0
Joined: 10/20/2008
Status: offline 		No I have no error handling built in to it at all at this stage

The script simply stops when it find and error but doesn't tell me what the error is.

Is there a way I can find out what it is?

(in reply to ebgreen)
 
 
Post #: 7
 
RE: Can VBScript update links in an Excel Workbook - 10/20/2008 2:34:24 AM   
  ebgreen


Posts: 5251
Score: 31
Joined: 7/12/2005
Status: offline 		If it just dies, then how do you know that it is even getting to this chunk. Could you show us the rest of the code?

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to TechAnalyst)
 
 
Post #: 8
 
RE: Can VBScript update links in an Excel Workbook - 10/20/2008 2:45:21 AM   
  TechAnalyst

 

Posts: 12
Score: 0
Joined: 10/20/2008
Status: offline 		Sure

Sub Updatemaster(mastername)
Dim objExcel, objWorkbook, fn, newname, i,
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(mastername)
' Turn off communication with user
objExcel.DisplayAlerts = False
' Update all links to the Master file
objWorkbook.UpdateLink
 MsgBox "links updated"
' Save the Master file
objWorkbook.Save
'  resume communications with user
objExcel.DisplayAlerts = True
objWorkbook.Close False
 Set objWorkbook = Nothing
objExcel.Quit
Set oExcel = Nothing
End Sub

I know it's that line because if I comment it out the script displays the message box (my amature atempt at debugging) if I leave it in it doesn't display the message box

FYI fn, newname, i are not used yet. they will be but there's not much point putting the code in til I fix this problem




(in reply to ebgreen)
 
 
Post #: 9
 
RE: Can VBScript update links in an Excel Workbook - 10/20/2008 2:51:49 AM   
  ebgreen


Posts: 5251
Score: 31
Joined: 7/12/2005
Status: offline 		Do you get anything if you allow it to display alerts?

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to TechAnalyst)
 
 
Post #: 10
 
RE: Can VBScript update links in an Excel Workbook - 10/20/2008 2:56:54 AM   
  TechAnalyst

 

Posts: 12
Score: 0
Joined: 10/20/2008
Status: offline 		No I don't get anything at all.

Except for a hanging script ;)

(in reply to ebgreen)
 
 
Post #: 11
 
RE: Can VBScript update links in an Excel Workbook - 10/20/2008 2:59:32 AM   
  TechAnalyst

 

Posts: 12
Score: 0
Joined: 10/20/2008
Status: offline 		I just found out that I can check the error in the browser it

UpdateLink Method of Workbook class failed

(in reply to TechAnalyst)
 
 
Post #: 12
 
RE: Can VBScript update links in an Excel Workbook - 10/20/2008 3:04:02 AM   
  ebgreen


Posts: 5251
Score: 31
Joined: 7/12/2005
Status: offline 		Wait, so where exactly is this running? Inside Excel? In a webpage/HTA? As a stand alone VBScript?

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to TechAnalyst)
 
 
Post #: 13
 
RE: Can VBScript update links in an Excel Workbook - 10/20/2008 3:10:10 AM   
  TechAnalyst

 

Posts: 12
Score: 0
Joined: 10/20/2008
Status: offline 		Is running in an HTML page which I'm opening in Interenet Explorer

It calls this Sub when a button is clicked

I never had a problem calling the Macro inside the work book this way so I don't think it's that

(in reply to ebgreen)
 
 
Post #: 14
 
RE: Can VBScript update links in an Excel Workbook - 10/20/2008 3:35:09 AM   
  ebgreen


Posts: 5251
Score: 31
Joined: 7/12/2005
Status: offline 		There are several issues with access rights associated with code running in html pages. This is where I would expect the issue to lay. Unfortunately this isn't really my area od expertise, so I don't know for a fact that this is the issue. Perhaps someone with more experience in this specific realm will wonder along.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to TechAnalyst)
 
 
Post #: 15
 
RE: Can VBScript update links in an Excel Workbook - 10/21/2008 12:20:53 AM   
  TechAnalyst

 

Posts: 12
Score: 0
Joined: 10/20/2008
Status: offline 		I fixed it!

I replaced
objWorkbook.UpdateLink

with
objWorkbook.UpdateLink objWorkbook.LinkSources, 1

and it works

Thanks for your help though

(in reply to ebgreen)
 
 
Post #: 16
 
RE: Can VBScript update links in an Excel Workbook - 10/22/2008 1:07:37 AM   
  TNO


Posts: 1402
Score: 16
Joined: 12/18/2004
From: thenewobjective.com
Status: offline 		@ebgreen

The rule is if its in a webpage, all security has to be turned off in the browser options which would include allowing active content and adding the domain to the full truest zone (which still may not work, especially with IE7+). The  Best and easiest solution is to always use an hta

_____________________________

To iterate is human, to recurse divine. -- L. Peter Deutsch

(in reply to TechAnalyst)
 
 
Post #: 17
 
RE: Can VBScript update links in an Excel Workbook - 10/22/2008 1:20:08 AM   
  ebgreen


Posts: 5251
Score: 31
Joined: 7/12/2005
Status: offline 		Thanks for sharing the info, but since it is not something I deal with regularly I will have forgotten it in approximately...three...two...one.....wait, what was I talking about?

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to TNO)
 
 
Post #: 18
 
 
 
 

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 >> Can VBScript update links in an Excel Workbook 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