01-12-2005, 02:11 PM
I am working on a ASP.NET app written in VB.net. I am attempting to import an Excel Macro module into an excel workbook.
I am able to run the macro if it is already in the workbook but this workbook will be created on the fly and this macro needs to be integrated in.
My current code is:
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oBooks As Excel.Workbooks
Dim oModule As VBIDE.VBComponent
exportPath = "c:\Inetpub\wwwRoot\TT\Reports\" & TTUser.GetUserNameFromID(TTSecurity.GetUserID) & "\"
webPath = "/TT/Reports/" & TTUser.GetUserNameFromID(TTSecurity.GetUserID)
myFileName = webPath & "/Excel.xls"
'Start Excel and open the workbook.
oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
oExcel.DisplayAlerts = False
oBooks = oExcel.Workbooks
oBook = oBooks.Open(exportPath & "Excel.xls")
oBook.Application.VBE.ActiveVBProject.VBComponents.Import(exportPath & "Module1.bas")
oExcel.Run("ParseData") 'Run the macros.
oBook.SaveAs(exportPath & "Excel.xls") 'Save in a temporary file
Response.ContentType = myContentType
Response.Redirect(myFileName) 'Send the user to the file
My code bombs on: oBook.Application.VBE.ActiveVBProject.VBComponents.Import(exportPath & "Module1.bas")
It gives me this message: System.Runtime.InteropServices.COMException: Programmatic access to Visual Basic Project is not trusted
Now I have done some research and it has told me to check the box for Trust access to Visual Basic Project.
I was sure this would solve the problem but I still get the same error.
I am running Windows XP, Excel 2003 and VS.NET 2003.
Any suggestions would be greatly appreciated.
01-12-2005, 03:23 PM
I know almost nothing about web programming... but here's a wild guess:
The security settings are specific to each user account. Could it be that the user identity used by the web server for running the ASP.Net code is not the same as what you're currently logged in as?
01-12-2005, 07:40 PM
I know almost nothing about web programming... but here's a wild guess...I'll second that (big-time!), but I think I know what's wrong.
This is not a .Net issue at all. The problem is that you are attempting to alter the Workbook.VBProject while "Programmatic access to Visual Basic Project is not trusted". This is a Macro Security setting (macro virus protection) that cannot be changed programmatically.
If this is a Server-Side action, then the Server this is running on can have this setting changed manually via Alt|Tools|Options... then the 'Security' Tab, then the <Macro Security> button, then the 'Trusted Publishers' Tab. At the bottom of that Tab you will see a checkbox named 'Trust access to Visual Basic Project'. You must make sure that checkbox is checked.
However, if this is a routine that is to run on the client side, it won't run unless this "Trust access to Visual Basic Project" is also turned on manually. Asking your cliients to lower their Macro Proction is not very nice and most probably won't... but I'm guessing that this routine is Server-Side, so I think this is within your control. However, in general, you might want to have a read of some of the caveats discussed here:Considerations for Server-Side Automation of Office (http://support.microsoft.com/kb/257757).
Hope this helps!
01-12-2005, 07:45 PM
...but here's a wild guess:
The security settings are specific to each user account. Could it be that the user identity used by the web server for running the ASP.Net code is not the same as what you're currently logged in as? Ah, now I see what you were getting at... Boy I feel dumb, you were one step ahead of me... :(
Villians, I gave the backdrop to the problem, which it looks like you were aware of: Now I have done some research and it has told me to check the box for Trust access to Visual Basic Project.
I was sure this would solve the problem but I still get the same error. Sorry, my bad, I'll go away now... Helen hit on it: be aware of who is logged in when this is run. I'll go sling away now, sorry guys...
01-13-2005, 07:36 AM
I am running this ASP.NET app on my local host. This means that excel is locally installed and I have opened up permissions to my ASP.NET user account for excel. I am also an admin on this machine.
Any other suggestions?
01-13-2005, 07:47 AM
When you changed the security settings in Excel (Trust access to ...), which account was that for? Your own, or the ASP user account, or both?
01-13-2005, 08:24 AM
I added access to ASP.NET account, my personal account and administrator account. Just in case I allowed full access and still nothing.
The automation works great. In fact, I can execute an imbeded macro. I just can't import to Excel's VB a new module. I get the same untrusted error.
The only thing I can think of is reinstall office.
01-13-2005, 10:18 AM
Reinstalling Office will be no use, trust me. This is a security settings issue.
Despite your explanations, I am not sure that you have been changing the correct setting. You say you "allowed full access" - this doesn't make any sense to me in this context. Giving access to the Visual Basic Project involves checking one box, there are no choices between partial or full access etc. This is not the same thing as the "macro security level" setting (low / medium / high). Tools > Macros > Security, then under the Trusted Sources tab, check the box named "Trust access to Visual Basic Project". Is that what you have done, for all of the relevant accounts?
01-13-2005, 12:34 PM
By this I mean changing the initial file Excel.xls security permissions allowing full control so that I can easily overwrite or change. Right now I am just trying to add to this file the Module by importing it via VB.net code.
So I have done it all. Set Excel setting withing excel, set file permissions to allow full control to everybody and tested my code so that it does everything but access the excel vba.
I can't think of anything else. I even turned off XP service pack 2.
01-14-2005, 09:18 AM
"Set Excel setting withing excel" is still not clear. Please, if you cannot explain what you are doing, you cannot expect me to understand what is going wrong.
So, again, did you or did you not do the following?
In Excel go to Tools > Macros > Security.
Then under the Trusted Sources tab, check the box named "Trust access to Visual Basic Project".
Do this for all user accounts involved.
01-14-2005, 12:23 PM
Really? You have security settings by going to Tools:Macro:Security set to Medium Sec Level and Trust Access to VB.
Then you have file security by right clicking on the Excel.exe file in Program files. Here I am allowing me full access.
Then you can set access to privileges to MS Excel in Administrative Tools:Component Services:....to COM+ Applications:MS Excel.
That is all I got. This should be **** specific.
Help would be nice.
01-19-2005, 03:08 PM
I'm sorry, if that doesn't fix it then I don't know what's wrong.
Please do not start new threads for the same question. You will not get any new or better answers that way. (By the way, in case you haven't noticed, Mike_R and myself are unfortunately the only two persons here who happen to know something about both Office and .Net...)