Pasting Code into a workook being created. 2

[zevw]

It might be a stupid question!

I am creating a new workbook in excel by exporting data from access. Is there a way that I after I open that workbook, I would like to paste code as a macro, so that when the next user opens this workbook it will run the code in the macro.

Thanks in advance for your help

 

[firefytr]

Hi there,

Make use of the workbook_open event. If in the VBEIDE of the desired file/project, double click ThisWorkbook module. You'll see two drop downs in the right (code) pane, select the left one (should say "(General)") and select Workbook. This will give you code that looks like this ...

Private Sub Workbook_Open()

End Sub

Now, you can either place your code in this procedure, or if it's in a standard module, you can call it from here by putting the procedure name in this routine. Post back if you need more help.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[zevw]

Thanks so much
Firefytr

Sorry!, I did not explain myself correctly. I know that manually I can write code into the Workbook_Open or Autpen module. I would like to paste the code into this workbook through code.

Let me explain myself.

After I export the data from Access. I run a macro from a different workbook that massages the data and gives it a presentable look for the user. What I would like to do while I am massaging this data, to add a module to this newly created sheet, so that once the user opens it they should be prompted by the module / macro.

Thanks again

 

[CautionMP]

zevw,
Here is the begining of a routine that will do what you want. It's built on info from Pearson Software Consulting, LLC Programming To The Visual Basic Editor with a couple of other Excel items build in.

**This was developed and tested in Access 2k SR-1 and assumes that the workbook [tt]\AddModuleToExcel.xls[/tt] resides in the same directory as the database.

[b][green]'REQUIRES: Microsoft Visual Basic For Applications Extensibility 5.3 (VBE6EXT.OLB)[/green][/b]

[navy]Sub [/navy] AddModule()
[b][green]'Change this to your workbook name and path[/green][/b]
Const cFileName = "[i]\AddModuleToExcel.xls[/i]"
[navy]Dim[/navy] objExcel [navy]As Object[/navy]
[navy]Dim[/navy] objWorkBook [navy]As Object[/navy]
[navy]Dim[/navy] vbComponent [navy]As[/navy] vbComponent
[navy]Dim[/navy] vbCodeModule [navy]As[/navy] CodeModule
[navy]Dim[/navy] blnSpawnedExcel [navy]As Boolean[/navy]
[green]'Use In-line Error handling For Object creation[/green]
On [navy]Error Resume Next[/navy]

[green]'Grab an existing Instance of Excel or create a new one[/green]
[navy]Set[/navy] objExcel = GetObject(, "Excel.Application")
[navy]If[/navy] Err.Number <> 0 [navy]Then[/navy]
  [navy]Set[/navy] objExcel = CreateObject("Excel.Application")
  blnSpawnedExcel = [navy]True[/navy]
  Err.Clear
[navy]End If[/navy]

[green]'Switch To block Error handling[/green]
On [navy]Error Goto[/navy] Err_Handler
[green]'Open the target workbook **SEE NOTE ABOVE on [b]CurrentProject.Path[/b][/green]
[navy]Set[/navy] objWorkBook = objExcel.Workbooks.Open([i]CurrentProject.Path & cFileName[/i])

[green]'Get the target module and add some text[/green]
[navy]Set[/navy] vbCodeModule = objWorkBook.VBProject.VBComponents("Sheet1").CodeModule
vbCodeModule.AddFrom[navy]String[/navy] "'Test"

[green]'Create a new standard module and add some text[/green]
[navy]Set[/navy] vbComponent = objWorkBook.VBProject.VBComponents.Add(vbext_ct_StdModule)
[navy]Set[/navy] vbCodeModule = vbComponent.CodeModule
vbComponent.Name = "OLEAddedModule"
vbCodeModule.AddFrom[navy]String[/navy] "'Test"

[green]'Release all the Objects[/green]
Clean_up:
[navy]Set[/navy] vbCodeModule = [navy]Nothing[/navy]
[navy]Set[/navy] vbComponent = [navy]Nothing[/navy]
With objWorkBook
  .Save
  .Close
[navy]End[/navy] With
[navy]Set[/navy] objWorkBook = [navy]Nothing[/navy]
[navy]If[/navy] blnSpawnedExcel [navy]Then[/navy] objExcel.Quit
[navy]Set[/navy] objExcel = [navy]Nothing[/navy]
[navy]Exit Sub [/navy]

Err_Handler:
Debug.Print Err.Number, Err.Description
Stop
[navy]Resume[/navy] Clean_up
[navy]End Sub [/navy]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[zevw]

Thanks!

I did not have the patience to wait. So I created a workbook with my code and once I launch excel I paste my raw data from the exported file into the workbook with the code. I then massage the data in the workbook with the code.

Thanks to firefytr I check if a certain file is open so the code in the workbook is not activated.

Thanks anyways I am sure it will come in handy next time.