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.
Please Login or Register to view this content.
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.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
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?
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.
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.
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.
sure the workbook and all of the files are on a shared drive.
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
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?
I attached the whole workbook here, you will need to create a text file to input some data but everything else should be there.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks