TheSwamp

Code Red => VB(A) => Topic started by: Kheilmann on June 13, 2006, 05:59:48 PM

Title: Modifying XLS files from Acad VBA project
Post by: Kheilmann on June 13, 2006, 05:59:48 PM
I have an old xls file (Template) and a new one which was just updated.  The update was so extreme that I am trying to update the old data to the new sheet automatically.  I've mapped what cells on the old sheet match the cells on the new sheet.  Nothing however seems to work(as far as reading copying old data and pasting into new sheet).

I think all I need to know is how to switch from one open Excel app to another.  The sheets are not opened in the same instance of Excel.

Here is kind of what I have done....(excuse the mistakes..this is just a quick reference)

Set ObjApp = Create excel.application
Set ObjWsht = objapp.workbooks.open("FileName")
If objwsht.cells(200,1) = "YES" then ' Cell A200  is a Flag to determine which sheets are updated already
Set ObjWsht2 = Objwsht
Set Objwsht = Nothing
Set Objwsht = objapp.workbooks.open("NewTemplate")
end if
So now I have 2 EXcel apps open. one with the old sheet and one with the new.
The old has all my data, and I want to copy that data and paste into the new Template...

When I record a macro in Excel it shows like this..

Windows("OldFile.xls").activate
Cutcopypaste = false
Range("MetalWork").copy
Windows("NewTemplate.xls").activate
Range("MetalWork").paste

The Windows("filename.xls").activate generates an error in ACAD VBA..
"Function not Defined"


Title: Re: Modifying XLS files from Acad VBA project
Post by: Atook on June 13, 2006, 06:38:25 PM
The Excel VBA model is different from the Excel reference you need to add to your Acad VBA project...

trying throwing ObjApp infront of your calls..

=============================
ObjApp.Windows("OldFile.xls").Activate
ObjApp.Cutcopypaste = False'<--note this won't work, there is no cutcopypaste, there is CutCopyMode? though.
ObjApp.Range("MetalWork").Copy
ObjApp.Windows("NewTemplate.xls").Activate
ObjApp.Range("MetalWork").Paste
=============================

I haven't run through it, but I'd guess that's your problem