?
Solved

Update an excel spreadsheet from vbscript

Posted on 2014-08-27
3
Medium Priority
?
492 Views
Last Modified: 2014-10-01
I am trying to write a vbscript that will open a spreadsheet, update values and then call a procedure.  This is what I have so far, but I am getting an error when attempting to write values to a specific sheet.  What am I missing?


Option Explicit
Const vbNormal = 1         ' window style

DIM wshShell, EnvVar, CurrentDefPrinter, ReportPrinter
'Create a new Windows Script Host Shell Object
Set wshShell = CreateObject("Wscript.Shell")
'Set it to read the env variables
Set EnvVar = wshShell.Environment("Process")
ReportPrinter = EnvVar.Item("AF11x17Printer")

DIM objXL, objWb, objWs, objR, objNet     ' Excel object variables
DIM file, st, gfuncs, installpath
file = "C:\REP_Reports\Report Design\WIKSWT_MON10.xls"

'--Start Excel and run it visibly
Set objXL = WScript.CreateObject("Excel.Application")
objXL.Visible = True
objXL.DisplayAlerts = true

'-- Open the file
Set objWb = objXl.WorkBooks.Open(file)

set objWs = objWb.ActiveWorkbook.Worksheets(4)
objWs.cells(24,3).Value = 0
objWs.cells(25,3).Value = "AUTO"
objXL.Run "AutoRunCTCalcs"

'--Close the workbook and Excel
objXL.ActiveWorkbook.Close False
Set objwb = Nothing
objXL.Quit
Set objXL = Nothing
0
Comment
Question by:AaronGreene1906
  • 2
3 Comments
 
LVL 65

Expert Comment

by:RobSampson
ID: 40289344
Try changing this
set objWs = objWb.ActiveWorkbook.Worksheets(4)
to this
set objWs = objWb.Worksheets(4)

You don't need ActiveWorkbook since you are already reference it with objWb

Rob.
0
 

Author Comment

by:AaronGreene1906
ID: 40292555
That worked perfectly.  I can update values as needed, but the last line is still a problem.  I can't get it to call that procedure.  Should it be

objXL.Run "C:\REP_Reports\Report Design\WIKSWT_MON10.xls!AutoRunCTCalcs"
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 2000 total points
ID: 40292917
You should be able to use
objXL.Run Mid(file, InStrRev(file, "\") + 1) & "!AutoRunCTCalcs"
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question