[dba-Tech] VBS question

[Susan Harkins]

Thanks Gustav -- you're right about 1) -- easy to check and as you suggest,
might not be necessary. 

Susan H. 


Hi Susan

  1.
Shouldn't happen, but easy to check
  2.
Use: objwb.Close False

/gustav

________________________________
Fra: dba-Tech <dba-tech-bounces at databaseadvisors.com> på vegne af Susan
Harkins <ssharkins at gmail.com>
Sendt: 21. juni 2019 23:40
Til: 'Discussion of Hardware and Software issues'
Emne: Re: [dba-Tech] VBS question

It's working now. I had a misnamed variable (ugh). I hate that kind of
problem -- hard to find yourself. I left it and planted flowers in the
garden I maintain as  volunteer. Came back and spotted it right away.

I have two more questions though:

1.) I don't want to close all instances of Excel, just the one that's
running the macro. I don't want to inadvertently shut someone down who might
be using Excel.

2.) I want to inhibit the Excel Save when exiting prompt. Should I do this
in the Excel procedure or in the VBS script?

Thanks everyone for your help today.

Susan H.

Hi Susan

It could be that it doesn't run using your account.
Try moving the script and workbook to the Shared/Public Documents folder.

/gustav

-----Oprindelig meddelelse-----
Fra: dba-Tech <dba-tech-bounces at databaseadvisors.com> På vegne af Susan
Harkins
Sendt: 21. juni 2019 16:01
Til: 'Discussion of Hardware and Software issues'
<dba-tech at databaseadvisors.com>
Emne: Re: [dba-Tech] VBS question

Thanks Gustav -- still not working.

I dropped in a cell(1,"A").Value = Time() statement just test and it doesn't
work either -- the script is running -- I can see the Echo on the screen.
Not sure how to continue troubleshooting.

Is it possible that it's something with my Task Scheduler settings?

The Action is Start a program.
The Program/script is C:\Windows\Systems32\cscript.exe.
The only argment is "C:\Users\Susan Harkins\Documents\PrintPivotTable.vbs"

I have the visible property set to True and I thought I would see Excel
quickly open, but that doesn't happen. Should it?

Susan H.



tech at databaseadvisors.com>
Subject: Re: [dba-Tech] VBS question

Hi Susan

Excel is very picky with its objects. So, try:

'.vbs file to print Excel PivoTable1.xlsm at 8am daily.

'Path to workbook.
strPath = "C:\Documents\PivotTable1.xlsm"

'Macro name.
strMacro = "PrintUpdatedPivotTable"

'Create an Excel instance.
Set objApp = CreateObject("Excel.Application") Set objwb =
objExcel.Workbooks.Open(strPath) objApp.Visible = True  ' Should not be
needed.

objApp.Run strMacro

objwb.Close
Set objwb = Nothing

objApp.Quit

Set objApp = Nothing

'Scheduled task completed.
MsgBox strPath & " " & strMacro & " PivotTable printed successfully",
vbInformation


Also, check that no Excel instance is left open after running the script.
_______________________________________________
dba-Tech mailing list
dba-Tech at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-tech
Website: http://www.databaseadvisors.com