Solved VBS script open file, run macro but does not save the file

Microsoft Excel 2010 - complete product...
February 24, 2015 at 13:13:38
Specs: Windows 10
Hi,

I have the following vbs code that works great, but it doesn't save the file after running the macro? Does anyone have any ideas how to fix this?

Option Explicit

On Error Resume Next

ExcelMacroExample

Sub ExcelMacroExample() 

  Dim xlApp 
  Dim xlBook 

  Set xlApp = CreateObject("Excel.Application") 
  Set xlBook = xlApp.Workbooks.Open("G:\Operations Excellence\Forms\ASA Forms\Start-Stop\Start-Stop by Plant-rev1.xlsm", 0, True) 
  xlApp.Run "RDB_Worksheet_Or_Worksheets_To_PDF_And_Create_Mail"
  xlApp.Save
  xlApp.DisplayAlerts = False
  xlApp.ActiveWorkbook.Close
  xlApp.Quit 

  Set xlBook = Nothing 
  Set xlApp = Nothing 

End Sub 

Thank you,
Sandi


See More: VBS script open file, run macro but does not save the file

Report •


✔ Best Answer
February 25, 2015 at 10:36:28
I'll bet that ...

xlBook.SaveAs  _
"G:\Operations Excellence\Forms\ASA Forms\Start-Stop\Start-Stop by Plant.xlsm"

xlBook.Close 

...made all the difference in the world!

Ya gotta reference the correct object or things can get really weird.

Thanks for posting the final version for the archives. It may just help someone else in the future.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
February 24, 2015 at 16:32:25
If I remember correctly you might add after

.Close SaveChanges=True

::mike


Report •

#2
February 25, 2015 at 05:01:00
Hi Mike,

I have added that statement and it prompts me to do a save as? Any ideas why?

Thanks
Sandi


Report •

#3
February 25, 2015 at 06:16:38
Since we don't have a copy of your workbook(s) it's hard for us to replicate your situation for testing.

What does this macro do?

 xlApp.Run "RDB_Worksheet_Or_Worksheets_To_PDF_And_Create_Mail"

My guess is that it is creating a new file within the Excel instance that the following instruction started, therefore your Save command needs to know the name of the file that you are trying to save.

Set xlApp = CreateObject("Excel.Application") 

I'll bet that if you commented out the following line, you would get the "Save changes?" dialog box since the xlApp that the file is open in doesn't know anything about the new file.

xlApp.DisplayAlerts = False

If I am correct, then the solution would be to give the file a name as part of the Save instruction:

See here options related to passing the path and file name to Excel via VBA:

http://stackoverflow.com/questions/...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 25, 2015 at 06:47:06
The first macro I was testing changes a filter in a pivot table and saves a pdf copy (does this (3) times). My end result that I am almost at is to open the spreadsheet during non-working hours (using task scheduler), refresh a query, refresh the pivot table, filter and save pivot table as pdf's, and then e-mail the pdf's to the plant managers. My new macro is called Auto_Run and I have updated the code below to include your suggestion and the script gives me an Error: Expected Statement / Code: 800A0400 / Source: Microsoft VBScript compliant error?

Option Explicit

On Error Resume Next

ExcelMacroExample

Sub ExcelMacroExample() 

  Dim xlApp 
  Dim xlBook 

  Set xlApp = CreateObject("Excel.Application") 
  Set xlBook = xlApp.Workbooks.Open _
   ("G:\Operations Excellence\Forms\ASA Forms\Start-Stop\Start-Stop by Plant.xlsm", _ 
   0, True) 
  xlApp.Run "Auto_Run"
  xlApp.SaveAs Filename:= _
   "G:\Operations Excellence\Forms\ASA Forms\Start-Stop\Start-Stop by Plant.xlsm", _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
  xlApp.DisplayAlerts = False
  xlApp.ActiveWorkbook.Close SaveChanges=True
  xlApp.Quit 

  Set xlBook = Nothing 
  Set xlApp = Nothing 

End Sub 


Report •

#5
February 25, 2015 at 07:09:04
Or is there a way to have it open as not "Read Only"

Report •

#6
February 25, 2015 at 07:34:13
Let's clear something up first. I didn't mention this earlier, but each time you post your code, you include these lines:

Option Explicit

On Error Resume Next

ExcelMacroExample

When I paste your code into the VBA editor, both of the lines below Option Explicit cause a Compile Error: "Invalid Outside Procedure". Do they not cause that error for you? They should, since they are not commented out and VBA (at least my VBA) has no clue what to do with them.

Next, you didn't mention which line is causing the "Expected Statement" error. I don't know your level of VBA expertise (although it seems high) so please don't take this the wrong way. Are you familiar with the debugging feature of Single Stepping? This tutorial will explain some debugging techniques that you might find useful - assuming you don't already know all about them:

http://www.computing.net/howtos/sho...

Perhaps using F8 to single step through your code would help you determine which line is causing the error and point you in the right direction.

As I mentioned earlier, since we don't have all of the parts and pieces of your environment, it's impossible for us to replicate your errors. Therefore, the best we can do toss out suggestions and see if they help. In other words, if you look at this thread, you'll see that we can easily set up a test workbook and try some code before offering a solution:

http://www.computing.net/answers/of...

In your more complicated case, we would need all of your code, your workbooks, etc. in order to really troubleshoot your issue.

OK, that said, I am not all that familiar with using the CreateObject("Excel.Application") feature, so I may be way off base here...

In your earlier code, you reference the ActiveWorkbook:

 xlApp.ActiveWorkbook.Close

In your latest version you don't:

xlApp.SaveAs Filename:= _

Again, since I am not very familiar with the use of CreateObject, I wonder if your attempt to use SaveAs against the xlApp object and not the xlApp.ActiveWorkbook is part of your issue. Have you tried that?

One last item, which is merely a posting tip:

You will notice that I edited the code in Response #4 to "shorten" some code lines so that they fit within the text window of this forum. That allows us to read the text above the code without having to scroll the window back and forth. The scrolling issue is not your fault, it's just an inconvenient "feature" of this forum. If you could preview your posts and make sure your code lines are short enough so that they don't force us to scroll the window back and forth, that would be really helpful. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
February 25, 2015 at 08:16:53
Hi,

This is actually done with VBS script not VBA using Notepad so I can use the Task
Scheduler to schedule this to run after working hours.

I think I may have figured it out my problem, I have taken out the ", 0, True" portion in
the following line and it is opening as normal.

Set xlBook = xlApp.Workbooks.Open _
("G:\Operations Excellence\Forms\ASA Forms\Start-Stop\Start-Stop by Plant.xlsm")</

Sorry for the confusion and thanks on the posting tip, I found that most irritating as well!

Also, thank you for pointing out the xlApp.ActiveWorkbook.Close was missing I was
wondering why the workbook was not closing at the end ;)

Thank you,
Sandi


Report •

#8
February 25, 2015 at 10:31:32
Just thought I would post my final code for this...

Option Explicit

On Error Resume Next

ExcelMacroExample

Sub ExcelMacroExample() 

  Dim xlApp 
  Dim xlBook 

  Set xlApp = CreateObject("Excel.Application")
  Set xlBook = xlApp.Workbooks.Open _
("G:\Operations Excellence\Forms\ASA Forms\Start-Stop\Start-Stop by Plant.xlsm") 
  xlApp.Application.Visible = False
  xlApp.Run "Auto_Run"
  xlApp.DisplayAlerts = False  
  xlBook.SaveAs  _
"G:\Operations Excellence\Forms\ASA Forms\Start-Stop\Start-Stop by Plant.xlsm"
  xlBook.Close 
  xlApp.Quit

  Set xlBook = Nothing 
  Set xlApp = Nothing 

End Sub 


Report •

#9
February 25, 2015 at 10:36:28
✔ Best Answer
I'll bet that ...

xlBook.SaveAs  _
"G:\Operations Excellence\Forms\ASA Forms\Start-Stop\Start-Stop by Plant.xlsm"

xlBook.Close 

...made all the difference in the world!

Ya gotta reference the correct object or things can get really weird.

Thanks for posting the final version for the archives. It may just help someone else in the future.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
February 25, 2015 at 10:39:38
Most definitely!!!!

Thank you both for steering me in the right direction :)

Sandi


Report •


Ask Question