Using vb script to copy one excel sheet to another is giving a macro error message

Hi,

I'm using vb script to copy data from one excel sheet to another which seems to work, but when i try to open the newly created excel file i get the following error message -
Errors were detected in 'XXXX.xls,' but Microsoft Excel was able to open the file by making repairs listed below. Save the file to make these repairs permanent.

Lost Visual Basic project.
Lost ActiveX controls.

The vb script code that im using to copy the data between the 2 excel sheets is pretty basic -
Set objXL = CreateObject("EXCEL.APPLICATION")
objXL.Workbooks.Open "C:\Test1.xls"
Set objWS = objXL.ActiveWorkbook.Worksheets("Sheet1")

objXL.Workbooks.Open "C:\Test2.xls"
Set objWS2 = objXL.ActiveWorkbook.Worksheets("Sheet1")

objWS2.rows("1:1").value = objWS.Rows("1:1").value
objXL.ActiveWorkbook.SaveAs "C:\Test2.xls"
objXL.Quit

When i try and copy the data manually it copies fine, and im able to open the new excel sheet without any errors, but when running my script it copies across the data fine but gives the above errors.
I think the issue has something to do with the fact that the first excel sheet has macros associated to it

Ideally i would like to run my script, have the data copied from one excel sheet to another, then open the new excel sheet without having to repair the excel because it contained the above errors.

Thanks
DiCanio13Asked:
Who is Participating?
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.

Rory ArchibaldCommented:
Hi,
See if this works any better:
 

Set objXL = CreateObject("EXCEL.APPLICATION")
set objWB = objXL.Workbooks.Open("C:\Test1.xls")
Set objWS = objWB.Worksheets("Sheet1")
set objWB2 = objXL.Workbooks.Open("C:\Test2.xls")
Set objWS2 = objWB2.Worksheets("Sheet1")
 
objWS2.rows("1:1").value = objWS.Rows("1:1").value
objWB2.SaveAs "C:\Test2.xls"
objwb2.close false
objwb.close false
objXL.Quit

Open in new window

0
DiCanio13Author Commented:
Sorry rorya,

this hasnt solved the issue. Im still getting the same error message when opening the excel sheet
0
BadotzCommented:
It sounds like your first sheet is corrupt - or otherwise broken - and you are copying the problems to the second sheet.
0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Rory ArchibaldCommented:
Try this so that you can actually see what is going on - I suspect you may be getting a dialog box which is preventing Excel from saving and/or closing:

Set objXL = CreateObject("EXCEL.APPLICATION")
objXL.Visible = True
set objWB = objXL.Workbooks.Open("C:\Test1.xls")
Set objWS = objWB.Worksheets("Sheet1")
set objWB2 = objXL.Workbooks.Open("C:\Test2.xls")
Set objWS2 = objWB2.Worksheets("Sheet1")
 
objWS2.rows("1:1").value = objWS.Rows("1:1").value
objWB2.SaveAs "C:\Test2.xls"
objwb2.close false
objwb.close false
objXL.Quit

Open in new window

0
DiCanio13Author Commented:
Hi Rorya,

Ive already tried this and there doesnt seem to be a problem when the copy and paste happens....i can see the data from sheet one being copied across to sheet 2....and this sheet gets saved without a visible problem. No dialog boxes pop up during the script execution or saving of files.
Ive also tried running this scenario manually and no errors occur....i only get the errors when i try to run this process using vb script.

If what 'Badotz' was saying above is true, that im copying across a corupt file....can i check this in vb script? why would the corrupt file work when manually copied?

Thanks
0
Rory ArchibaldCommented:
I'm not convinced it is corruption. Which version of Excel are you using?
This works fine for me:
 

Set objXL = CreateObject("EXCEL.APPLICATION")
set objWB = objXL.Workbooks.Open("C:\Test1.xls")
Set objWS = objWB.Worksheets("Sheet1")
set objWB2 = objXL.Workbooks.Open("C:\Test2.xls")
Set objWS2 = objWB2.Worksheets("Sheet1")
 
objWS2.rows("1:1").value = objWS.Rows("1:1").value
objWB2.Save ' no need to save as since it's the same file name.
objwb2.close false
objwb.close false
objXL.Quit

Open in new window

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
DiCanio13Author Commented:
Hi Rorya,

Ive tried that code and it works fine when i use 2 new excel sheets. The problem arises when i use a sheet with macros associated with it....when the save occurs, some macros / libraries are missing from the saved sheet. Have you any idea why or how this may happen?
0
Rory ArchibaldCommented:
What kind of macros, what do you mean by libraries, and which version of Excel? Any chance you could post a couple of sample workbooks that don't work for you?
0
DiCanio13Author Commented:
Hi Rorya,

Here's the file that im using to take data from - called "Input_Data". This spreadsheet is created from an web application called TM1. Im reading this data to a normal excel sheet, but when i save this the errors occur.
Input-Data.xls
0
DiCanio13Author Commented:
Hi Rorya,

I think the issue may be related to the VBA code in the saved excel sheet not being enabled. Ive attached a word doc with screenshots of the VBA when doing this process manually (first 2 screenshots) and then doing this process through vb script (last 2 screenshots). As you can see, in the last 2 screenshots the VBA code is greyed out and doesnt seem to be enabled. Is there any way in vb script to make sure the VBA is enabled?
VBA-Error.doc
0
Rory ArchibaldCommented:
I can't replicate that - your workbook works fine for me (as I would expect, since you're not actually copying, you're just assigning values). Is that the actual original source workbook as produced by the other application, or have you opened and saved it in between?
0
DiCanio13Author Commented:
Hi Rorya,

This problem is now solved. It was due to the version of excel which my PC had installed (2002). Ive since upgraded this to 2003 and the issue doesnt occur anymore. Thanks for your help anyway
0
DiCanio13Author Commented:
Comments pointed me in the correct direction where i eventually solved the issue
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
VB Script

From novice to tech pro — start learning today.