Link to home
Start Free TrialLog in
Avatar of DiCanio13
DiCanio13

asked on

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
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

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

Avatar of DiCanio13
DiCanio13

ASKER

Sorry rorya,

this hasnt solved the issue. Im still getting the same error message when opening the excel sheet
It sounds like your first sheet is corrupt - or otherwise broken - and you are copying the problems to the second sheet.
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

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
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
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?
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
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
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?
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
Comments pointed me in the correct direction where i eventually solved the issue