VBS Script

Hi,

I have a VBS script which i am creating to try and automatically run a report

On this report, the pivots often change and excel will give the message

"Do you want to replace the contents of destination cells in Screen?"

Then it comes up with two other message boxes where user presses "ok" and "ok" again

How can i skip through these with the script. The answer to the question is always yes and to the two message boxes, always press ok....

Can anyone incorporate this into my script?

Thanks
Seamus




Dim oXL, oFolder, aFile, FSO
        
Set oXL = CreateObject("Excel.Application")
Set FSO = CreateObject("Scripting.FileSystemObject")
        
oXL.DisplayAlerts = False
oXL.Workbooks.Open("G:\Asset Services Risk Team\Internal ACS\Internal Accounts Report.xls")
oXL.Run "x" 
oXL.Run "CreateToday"
oXL.Run "CreateFile" 
oXL.ActiveWorkBook.Close SaveChanges = True

Open in new window

Seamus2626Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jppintoCommented:
You could add this code to the Open event of the Workbook to not diplay alerts.

jppinto
Private Sub Workbook_Open()
With Application
    .DisplayAlerts = False
    .EnableEvents = False
    .ScreenUpdating = False
End With
End Sub

Open in new window

0
Rory ArchibaldCommented:
Since you are already using DisplayAlerts, my suspicion would be that you can't simply dismiss the dlaiogs. You would need to clear the area around the pivots before refreshing them. Can't comment on the other messages as you haven't said what they are.
0
IanMurphyCommented:
the message only appears when you have text outside the pivot table which will be overwritten.

It would be easier to just maintain the area around the pivottable clear, no?

Ian
0
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

jppintoCommented:
Seamus, didn't noticed that you already had DisplayAlerts=False on your code...sorry.
0
Seamus2626Author Commented:
Thanks guys,

What do you mean by

"You would need to clear the area around the pivots before refreshing them"

"maintain the area around the pivottable clear, no?"

The message boxes are jus silly things like "Report ran" They can be removed if they have to

Thanks
Seamus
0
Rory ArchibaldCommented:
I mean you would need to clear the contents of the cells around the pivot table(s) before refreshing them. Presumably they are not important as you are overwriting them anyway?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IanMurphyCommented:

hmm, so you want to do this *exclusively* from the external vbs script. ... difficult. you may be able to do it with sendkeys but it would be problematic and unreliable.

The above code opens an excel worksheet, which I presume has some code which executes on startup.

Why not just modify the startup vba code and correct it. If its displaying messages just remove the 'msgbox' lines.

By "You would need to clear the area around the pivots before refreshing them" I meant that when you modify a pivot table config and the resulting table overwrites cells with text in them it displays the message you mentioned above "Do you want to replace the contents of destination cells in Screen?". The cure is to not overwrite cells with data in them... make sure the surrounding cells contain nothing.


Ian
0
Seamus2626Author Commented:
perfect, i understand now. I will remove the messageboxes and clear the cells in the macro

Thanks
Seamus
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.