Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1736
  • Last Modified:

add a macro programmatically to EXCEL using vbscript in ASP

Hi,

I need to export html table to EXCEL and while exporting i have to create a new macro and associate that to the excel.I have the below code that exports the table to excel but i have no idea how i can add a macro to it in vbscript.

<Script Language="VBScript">
    Sub RunScript
        strCopy = MyTable.InnerHTML
        document.parentwindow.clipboardData.SetData "text", strCopy

        Set objExcel = CreateObject("Excel.Application")
        objExcel.Visible = True
   
        Set objWorkbook = objExcel.Workbooks.Add()
        Set objWorksheet = objWorkbook.Worksheets(1)
        objWorksheet.Paste
    End Sub
</Script>
0
madhujinka
Asked:
madhujinka
  • 5
  • 2
1 Solution
 
purplepomegraniteCommented:
http://support.microsoft.com/kb/194611

The article above gives a complete working example of this.  While it is written in VB, it will work the same in VBScript (just dont explicitly declare the variables i.e. no as clauses).
0
 
madhujinkaAuthor Commented:
Thanks for the solution.It worked fine.

I am stuck with another problem .
<Script Language="VBScript">
    Sub RunScript
   
        strCopy = MyTable.InnerHTML
        document.parentwindow.clipboardData.SetData "text", strCopy

        Set objExcel = CreateObject("Excel.Application")
        objExcel.Visible = True
   
        Set objWorkbook = objExcel.Workbooks.Add()
       
        Set objWorksheet = objWorkbook.Worksheets(1)
        objWorksheet.Paste
       
        'Dim xlmodule 'VBComponent
        Set xlmodule = objWorkbook.VBProject.VBComponents.Add(1) 'vbext_ct_StdModule
       
         ' Add a macro to the module...
       Dim strCode
       strCode = _
          "sub MyMacro()" & vbCr & _
          "   Columns(1).ColumnWidth = 60" & vbCr & _          
          "end sub"
       xlmodule.CodeModule.AddFromString strCode

       ' Run the new macro!
       objExcel.Run "MyMacro"
       
    End Sub
</Script>

The above mentioned code was running client side and i am able to open excel and attach a macro to it.When I do the same server side i am not  able to open the excel .It does not throw any error,simply executes and shows up nothing.I think the code should be same for both server side and client side as I am using vbscript for both.

Am I missing something?Is it with code or someother problem?
0
 
purplepomegraniteCommented:
Server-side you need to use Server.CreateObject:

Set objExcel = Server.CreateObject("Excel.Application")
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
purplepomegraniteCommented:
Hi madhujinka,

Please review the below which I have just copied and pasted from the EE Help:

What's the right grade to give?        
Grading at Experts Exchange is not like school. It's more like the "10-point Must" system in professional boxing; in other words, an answer is worth an A, unless it doesn't resolve your issue. If it requires you to do a little more research, or figure out one more piece of code, then it's worth a B. If you think it's not worth a B, the custom is to offer the Experts an opportunity to earn a better grade.
Giving a higher grade has no impact on your Available Points.



As far as I can see above, my answer provided a complete solution to your original question, so as far as I can see should have been graded A.  If you graded B mistakenly, then please let me know as I can ask a moderator to regrade it (or open the question to allow you to regrade it).  If, after reading the above, you still think the answer should be graded B, please let me know why.

Thanks.
0
 
madhujinkaAuthor Commented:
I am sorry I didnt know this while i was grading.The anser gave me a complete solution.Please let  me know how i can regrade the answer.
0
 
purplepomegraniteCommented:
No problem, I guessed that was the case.  I have asked a moderator to reopen the question to allow regrade.
0
 
purplepomegraniteCommented:
Thank you. :)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now