Add vb code to a new workbook using a macro

[Alagard]

Please Login or Register  to view this content.

This is the code I am using now. I am creating a new workbook with the data from this text file and inputting the formulas that I need. The issue that I am having is that I want to be able to add the code below to the new sheet on it's creation and I have not been able to get it to work. Thanks for your help.

Please Login or Register  to view this content.

[Leith Ross]

Hello Alagard,

This macro will copy the contents of a text file to the active sheet code module of the workbook. You will need to copy your macro and save it as a text file. Change the file path in the macro to this location. Call the macro before you close the workbook and save it.

Please Login or Register  to view this content.

[Alagard]

I run into an issue here. I am creating the new worksheet in my macro and filling it with data and formulas at that step. Looking at this, can I incorporate this into the code at the beginning to do this all in one step?

[Leith Ross]

Hello Alagard,

I have added the macro call to your code. You will still need to copy and paste your sound macro into Notepad and save it. Once you have done that, you will need to change the path in the macro I wrote to your file's location.

Please Login or Register  to view this content.

[Alagard]

I get an error when I try to run that.

Programmatic access to Visual Basic Project is not trusted

the debugger then highlights this line
Set VBproj = Wkb.VBProject

Thanks for the help.

[Leith Ross]

Hello Alagard,

If you are running this code on you r own machine, you should not be getting this error. However, if you are attempting to change another workbook on someone else's machine you will.

[Alagard]

sure the workbook and all of the files are on a shared drive.

[Alagard]

So I checked the box for trusted access and that step goes okay however now the next step gives an error

Run-time error '9':
Subscript out of range
Set VBmod = VBproj.VBComponents(ActiveSheet.Name).CodeModule

[Leith Ross]

Hello Alagard,

The error indicates the name of the module was not found. Which doesn't make sense because a workbook must have at least one worksheet in it and it is looking for the Active Sheet. Can you post the workbook for review?

[Alagard]

I attached the whole workbook here, you will need to create a text file to input some data but everything else should be there.

[Leith Ross]

Hello Alagard,

Based on your previous post and code, you were opening a new workbook and I included a call to the macro at this time. The target workbook would be the active workbook (new workbook). The code will be written into the Sheet module of the active sheet in the active workbook.

I assume that both workbooks are open in the same instance of Excel. If the target workbook is open in another instance of Excel the macro will error. But, the error would be different than the one you are getting. I can not reproduce the error on my machine.