Problem saving VBScript Script Files

pupsia

Board Regular
Joined
Dec 2, 2015
Messages
67
Hello all,

I'm used to Macro program writing but I'm new to VBScript Script Files and I cant seem to find what can be wrong no matter where I look.
I have a problem with running this vbs file, getting this error:

Line: 7
Char: 1
Error: "Object required: 'application'
Code: 800A01A8


Code:
Dim xlApp
Dim xWb


Set xlApp = CreateObject("Excel.Application") 
Set xWb = xlApp.Workbooks


For each xWb in application.Workbooks
	If not xWb.readonly and windows(xWb.name).visible then
		xWb.save
	End if
Next

Any ideas why this could be?

Thank you!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,054
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Use xlApp instead of application.
 

pupsia

Board Regular
Joined
Dec 2, 2015
Messages
67
Right now there are no errors like before, but when i run the program, the [Saved] does not appear like usual when you save an excel file, so it doesn't save for some reason :(
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,054
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What do you mean by that? What [Saved]?
 

pupsia

Board Regular
Joined
Dec 2, 2015
Messages
67

ADVERTISEMENT

The program should go through all open Excel files and if there are any that are not Read Only, save those files.

What I meant by [Saved] is when we have a name at the top green filed in Excel, how that name changed after a save was done.

Original name (in green field) example:
abc.xlsx - Excel

After you click save:
abc.xlsx - Saved

Now there are no errors as before, but the Excel I have opened for testing is not being saved.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,054
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You're creating a new Excel application instance and looping through that, so there won't be any workbooks to save.
 

pupsia

Board Regular
Joined
Dec 2, 2015
Messages
67

ADVERTISEMENT

Oh, so that does not capture old already opened workbooks. I guess it makes sense that it didn't work as I wanted.

Any advise how I could go through all already opened Excel files and save them?
As I don't know what files will be open and from what path, I cant use direct names for that.
Sometimes there could be 10 or 40 Excel opened at a time..
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,054
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you only have one Excel application instance open, you can just use GetObject rather than CreateObject.
 

pupsia

Board Regular
Joined
Dec 2, 2015
Messages
67
Thank you for the tip! :)

I didn't think about using get instead of create, totally new to this type or writing...

After testing a few things out, this seems to work and does save the files if i run the program:

Code:
set objExcel = getobject(,"Excel.Application")

For Each aWorkbook In objExcel.Workbooks
    If aWorkbook.Saved = False Then
       aWorkbook.Save
    End If
Next

Though I couldn't find a working way to add the "Read Only" and "Visible" parts I had before
 

Watch MrExcel Video

Forum statistics

Threads
1,122,158
Messages
5,594,588
Members
413,915
Latest member
namreh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top