Adding code to a generated workbook 1

[RicksAtWork]

I am programtically creating a new workbook and copying data to it.

It then saves itself.

I want to be able to control the print settings of the new worksheets, however my boss doesnt want buttons added to the generated worksheets/workbook.

Is it possible to add code to the workbook when I am creating it from the parent workbook that will always run before the user trys to print the exported workbook?

i.e.


With ActiveSheet

.PageSetup.Orientation = xlLandscape
.PageSetup.FitToPagesWide = 1
.PageSetup.LeftFooter = "targetWorkSheetName"

End With

 

[SkipVought]

Hi,

You need to be more descriptive about the process.

SURE its possible to print the sheet in the original code... ActiveSheet.PrintOut.

But does the user get to interact BEFORE the printing takes place?

NEED TO KNOW MORE!

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[Chance1234]

In answer to your first part, here is a macro for copying code from one module in one workbook to another , not my coding.

Sub CopyModule(SourceWB As Workbook, strModuleName As String, _
    TargetWB As Workbook)
' copies a module from one workbook to another
' example: 
' CopyModule Workbooks("Book1.xls"), "Module1", _
    Workbooks("Book2.xls")
Dim strFolder As String, strTempFile As String
    strFolder = SourceWB.Path
    If Len(strFolder) = 0 Then strFolder = CurDir
    strFolder = strFolder & "\"
    strTempFile = strFolder & "~tmpexport.bas"
    On Error Resume Next
    SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
    TargetWB.VBProject.VBComponents.Import strTempFile
    Kill strTempFile
    On Error GoTo 0
End Sub

Chance,

Filmmaker, gentlemen and read my blog at

http://www.20six.co.uk/Chance1234

 

[Chance1234]

sorry forgot to add you need to reference the VBA Extensibility library

Chance,

Filmmaker, gentlemen and read my blog at

http://www.20six.co.uk/Chance1234

 

[RicksAtWork]

whats the VBA Extensibility library. How do I add the reference?

Is there a WorkBook/Worksheet load event that I can stick my code in?

 

[Chance1234]

Go to Tools, referendces and its something like Microsoft Visual Basic Extensibility Libary , tick the box next to it.

Under thisworkbook , there is

Private Sub Workbook_Open()

End Sub




Chance,

Filmmaker, gentlemen and read my blog at

http://www.20six.co.uk/Chance1234

 

[PHV]

I am programtically creating a new workbook
Why not filling the PageSetup properties of the created sheets in the same run ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HughLerwill]

Ricks,

You may be able to sqirt code into the workbook when/as you create it like this;

a$ = "Private Sub Workbook_Open()" & vbCr & _
"With ActiveSheet" & vbcr & _
".PageSetup.Orientation = xlLandscape" & vbcr & _
".PageSetup.FitToPagesWide = 1" & vbcr & _
".PageSetup.LeftFooter = ""targetWorkSheetName""" & vbcr & _
"End With" & vbcr & _
"End Sub"

ActiveWorkbook.VBProject.VBComponents.Item("ThisWorkbook").CodeModule.AddFromString (a$)

regards Hugh

 

[RicksAtWork]

Cheers all...

PHV

Why not filling the PageSetup properties of the created sheets in the same run ?

I tried this - it didn't seem to work.


HughLerwill

I got the following error: 'Programmatic access to Visual Basic Project is not trusted'

Also, is the string "ThisWorkbook" a standard or should it be the name of the target workbook?

 

[HughLerwill]

Ricks,

"ThisWorkbook" is standard. If you checkout the visual basic project for the Workbook you should be able to see it listed in the VBA IDE.
Sounds as if Excel's security settings are preventing this working, try adjusting them.

Notwithstanding the above PHV's approach seems the most simple and I'm suprised to hear it won't work. Maybe you should post the code you are using.

Hugh

 

[RicksAtWork]

I've solved the security issue, however - this line still doesnt seem to work:

ActiveWorkbook.VBProject.VBComponents.Item(countryWorkSheet.Name).CodeModule.AddFromString (a$)

I get an index of out range error.

Help!!

 

[HughLerwill]

ThisWorkbook" IS standard, use it in place of your countryWorkSheet.Name

Hugh,

 

[RicksAtWork]

Right!

I have managed to inject the following code into the parent workbook:

Private Sub Workbook_Open()
Dim iCount As Integer
iCount = ActiveWorkbook.Sheets.Count
For i = 1 To iCount - 1
ActiveWorkbook.Sheets(i).Select
With ActiveSheet
.PageSetup.Orientation = xlLandscape
.PageSetup.FitToPagesWide = 1
.PageSetup.LeftFooter = "targetWorkSheetName"
End With
Next i
End Sub

I was hoping that when this printed, the spreadsheet would be shrunk to fit the page. How do I do this?

 

[RicksAtWork]

Done it!

Thanks for everybodies help.