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?
 
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
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.