Excel document not saving

[Guest]

From VBA, I start with an excel workbook with one worksheet. Then, I open a
second excel file (with one worksheet), edit it and bring the tab into the
original workbook. So, there are now two worksheets in the original workbook.
Then save it - this works. Then, do it again... Open a new second excel file,
edit the tab and bring it into existing workbook. Now there are 3 tabs in the
original workbook. I try to save and it gives an error "document not
saved"... using the following code:

Workbooks.Open ("\\...path\Summary.xls")
Windows("Summary.xls").Activate
Sheets("Tab1").Select
Sheets("Tab1").Move before:=Workbooks(Original).Sheets(1)
ActiveWorkbook.Save

 

[Guest]

If you are talking about trying to save the original workbook add this line
just below the "Save" line.
ThisWorkbook.Activate

Or instead of ActiveWorkbook.Save use This Workbook.Save.

 

[Guest]

Thanks for your help. I am getting the same problem when using the
ThisWorkbook example.

Something I neglected to mention before. The VBA is attached to an Access
database. The subroutine is not attached to the excel file which I am trying
to save.

Restating the process with further detail... Using Access VBA, the excel
application is created. Then, the initial excel file is created and saved.
Then a second, pre-exising file is opened (within the same application) that
contains a single worsheet. This worksheet is then moved to the inital file
and the initial file is resaved. Then another pre-existing file is opened and
moved into the initial file. The save doesn't work the second time through.

Thanks,
Kevin

 

[NickHK]

Kevin,
When Automating Excel (or any app) externally, you need to be more careful.
Creating additional refereneces, to those you expect and and deal with,
becomes a problem and may prevent you from closing down the app at the end
of your code.
Thus you should always only use fully qualified refereneces to the objects
used.
Also avoid .Select/.Activate, unless it is required, which is seldom the
case. e.g.

Dim XLApp As Excel.Application
Dim XLWBSource As Excel.WorkBook
Dim XLWBDestination As Excel.WorkBook
Dim XLWS As Excel.WorkSheet

Set XLApp=New Excel.Application
Set XLWBSource=XLApp.Workbooks.Open ("\\...path\Summary.xls")
Set XLWS=XLWBSource.Worksheets(""Tab1")

Set XLWBDestination =XLApp.Workbooks.Open ("\\...path\Original.xls")

XLWS.Move before:=XLWBDestination.Sheets(1)
XLWBSource.Save

NickHK