Opening excel spreadsheet from within a VBscript

[Disco]

I need to open an excel spreadsheet from within a
VBscript and then run a macro within that spreadsheet
before closing it back down again.
Can anyone provide some code that would do this.

Thanks

 

[chandlm]

DISCO.

Try

Set xl = CreateObject("Excel.application")

xl.Application.Workbooks.Open "yourworkbookpath\yourworkbook.xls"
xl.Application.Visible = True
xl.Application.run "'yourworkbookname.xls'!macronametorun"

Set xl = Nothing


This should do exactly what you need.

HTH

 

[chandlm]

Just an extra note to the last post.

Make sure that you add the code to close the workbook and application
in the excel sheet macro so that the focus is then returned to the rest
of your vbscript.

I normally use
ActiveWorkbook.Save
Application.Quit

HTH

 

[disco]

Hi,
This code helps but I now have the problem that the
workbook I am closing is asking if I would like to save
the changes that have been made by the macro.
The answer is no but I'm not sure how to put this into
the code.
Do you know how i can do this ??

Thanks

 

[KJTFS]

you can use the
application.displayalerts = false
that will make excel use defualts instead of ask you what you want to
do. If you do not want it to save the sheet then do
xl.close false


Hope that helps.

Keith
www.kjtfs.com

 

[chandlm]

Disco,

You can get round this by using

ActiveWorkbook.Close False

' closes the active workbook without saving any changes

instead of

activeworkbook.save


HT

 

[Andy]

I assume you really mean VBScript and not VBA, so here goes;

Here is a sample VBScript. Save it in a standard text file with a .VBS
extention.
This one creates an Excel object, opens a workbook, sets a value, runs a VBA
routine, then tides up and closes down.

Set xlObj = CreateObject("Excel.application")
xlObj.Workbooks.Open "C:\Byg\textfile\TextFileReformat.xls"
xlObj.Range("NoOfCharsPerLine").Value = 50
xlObj.Run "ReadFromTextFile"
xlObj.ActiveWorkbook.Saved = True
xlObj.ActiveWindow.Close
xlObj.Quit


--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[KJTFS]

I assume you really mean VBScript and not VBA, so here goes;

Here is a sample VBScript. Save it in a standard text file with a
.VBS
extention.
This one creates an Excel object, opens a workbook, sets a value,
runs a VBA
routine, then tides up and closes down.

Set xlObj = CreateObject("Excel.application")
xlObj.Workbooks.Open "C:\Byg\textfile\TextFileReformat.xls"
xlObj.Range("NoOfCharsPerLine").Value = 50
xlObj.Run "ReadFromTextFile"
xlObj.ActiveWorkbook.Saved = True
xlObj.ActiveWindow.Close
xlObj.Quit


> > >

Don't forget to
Set xlObj = Nothing

at the end......

Keith
www.kjtfs.com

 

[mojof1]

hi,

this code generally works, and gets the job done
however, when the process is complete, i get the error in the picture as attached

anyone pls help?

Andy wrote:
> I assume you really mean VBScript and not VBA, so here goes;
>
> Here is a sample VBScript. Save it in a standard text file with a
> .VBS
> extention.
> This one creates an Excel object, opens a workbook, sets a value,
> runs a VBA
> routine, then tides up and closes down.
>
> Set xlObj = CreateObject("Excel.application")
> xlObj.Workbooks.Open "C:\Byg\textfile\TextFileReformat.xls"
> xlObj.Range("NoOfCharsPerLine").Value = 50
> xlObj.Run "ReadFromTextFile"
> xlObj.ActiveWorkbook.Saved = True
> xlObj.ActiveWindow.Close
> xlObj.Quit
>
>
> > > >
> >
> > Don't forget to
> > Set xlObj = Nothing
> >
> > at the end......
> >
> > Keith
> > www.kjtfs.com


---
Message posted from http://www.ExcelForum.com/