Export Workbook Code 1

[mveera]

Hi,

I export all the sheets, forms and modules from one worksheet to another programatically. but i am not able to expor the code which is present in workbook object say "ThisWorkbook". How can i export the code automatically?

As of now i do it manually after exporting all other objects.

Thank You
Veera

 

[RobBroekhuis]

Try this:

Dim s As String
With ThisWorkbook.VBProject.VBComponents("ThisWorkbook&quot.CodeModule
s = .Lines(1, .CountOfLines)
End With
Workbooks("OtherWorkbook&quot.VBProject.VBComponents("ThisWorkbook&quot.CodeModule.AddFromString s

Rob

 

[RobBroekhuis]

One word of caution on the approach above: if you have the VBE set to default to "option explicit", the module will now have two of them (one from the old code, and one new one generated automatically by VBE). VBE doesn't like that, and yells at you. So in that case, replace the last statement above with:

With Workbooks("OtherWorkbook&quot.VBProject.VBComponents("ThisWorkbook&quot.CodeModule
.AddFromString s
.DeleteLines 1
End With

Rob

 

[mveera]

Thanks Rob. It worked fine.

In my excel my VBE is set to Option Explict. It added Option Explcit only once. I will cross check once again.

Is it possible to change the name of the workbook object from "ThisWorkbook" to anyother. It says it is a readonly property and cannot be set. but i can change the name using the properties editor.

Thanks
Veera

 

[RobBroekhuis]

Yes. There is a difference between the workbook.name property (cannot be changed) and the vbproject component ThisWorkbook's .name property (which can be changed). I'm not sure why you'd want to, though.
Rob

 

[mveera]

Hi,

There are two reasons for changing workbook name.

1) The current development workbook keeps growing in size everyday without any specific reason. The only way to reduce the size was to export & import to a new workbook. This reduces the file size drastically. in my case from 8.9mb to 1.5mb. In the original workbook, the name is "PlanningWorkbook". But when i import it the name becomes "ThisWorkbook". I use this name to copy the code as suggested by you. So if i start using new workbook i cannot export it unless i change the name to "PlanningWorkbook". As of now i named the original workbook object also to "ThisWorkbook".
2) The users are allowed to export the entire or section of the workbook. so even in this case the workbook object name has to be changed.

That is the reason i asked for name change.
As suggested by you i used vbproject component ThisWorkbook's .name and it worked.

Thank You very much for the help.

Veera

 

[mveera]

Hi,

I changed the name of the workbook object, it worked fine. but when i try opening the new file it gives an Internal Memory error and crashes Excel..

Veera

 

[RobBroekhuis]

I'm still unclear why it's important to change the workbook object's name. That is a property which is completely invisible to the users. Whereas I frequently rename VBA modules, and sometimes the VBAProject itself, I've never seen a need to change the name of the ThisWorkbook VBcomponent. I guess partly because I fear that Excel will get upset with me (as it did with you). Not a real answer, I know.
Other strangeness: when I programmatically rename the ThisWorkbook VB component, the name change is reflected in the project browser, but not in the object properties window. Which tells me Excel isn't making the change in all the right places...
Rob

 

[mveera]

Thanks for the reply Rob.

It is not a requirement to change the name of the workbook object name. I created the workbook with a different and i want to maintain the name in the new exported file also. which it seems that it cannot be done. so i have changed the name to the default name provided. I also noticed that when i change the name of the object is reflected in the properties window. So that gives me the reason why excel crashes when i open the new file.

Thank You very much helping me export the code.

Regards
Veera