Windows Vista Forums

RE: Assign variable value to excel cell
  1. #1


    Bishop Guest

    RE: Assign variable value to excel cell

    Hi,

    the code works.
    Are you sure that variable "strabc" has a value?

    I try this and it works:
    ---
    Dim Xcl
    Dim strabc

    strabc = "Test"

    Set Xcl = CreateObject("Excel.Application")
    Xcl.Visible = true
    Set newBook = Xcl.Workbooks.Add
    newBook.Worksheets(1).Cells(1,1).value=strabc
    ---

    Check the content of strabc like this:
    ---
    Dim Xcl

    msgbox strabc

    Set Xcl = CreateObject("Excel.Application")
    Xcl.Visible = true
    Set newBook = Xcl.Workbooks.Add
    newBook.Worksheets(1).Cells(1,1).value=strabc

    ---



    More Script Samples about VBScript and Excel you can find here:

    http://www.scriptbox.at.tt/index.php...&site=1&list=1

    alt. Link:
    http://scriptbox.boris-toll.at/index...&site=1&list=1




    But

    "RICK" wrote:

    > How can one assign a variable value to a cell. The following doesn't work.
    >
    >
    > .......
    > strabc = rsTemp.Fields("Taxonomy")
    > %>
    >
    > Dim Xcl
    >
    >
    > Set Xcl = CreateObject("Excel.Application")
    > Xcl.Visible = true
    > Set newBook = Xcl.Workbooks.Add
    > newBook.Worksheets(1).Cells(1,1).value=strabc
    > </script>

      My System SpecsSystem Spec

  2. #2


    RICK Guest

    RE: Assign variable value to excel cell

    I too believe that strabc does not have a value. The value is set earlier in
    my code between <% and %>. But the value does not seem to be carried over to
    the code between the <script> tags.



    "Bishop" wrote:

    > Hi,
    >
    > the code works.
    > Are you sure that variable "strabc" has a value?
    >
    > I try this and it works:
    > ---
    > Dim Xcl
    > Dim strabc
    >
    > strabc = "Test"
    >
    > Set Xcl = CreateObject("Excel.Application")
    > Xcl.Visible = true
    > Set newBook = Xcl.Workbooks.Add
    > newBook.Worksheets(1).Cells(1,1).value=strabc
    > ---
    >
    > Check the content of strabc like this:
    > ---
    > Dim Xcl
    >
    > msgbox strabc
    >
    > Set Xcl = CreateObject("Excel.Application")
    > Xcl.Visible = true
    > Set newBook = Xcl.Workbooks.Add
    > newBook.Worksheets(1).Cells(1,1).value=strabc
    >
    > ---
    >
    > More Script Samples about VBScript and Excel you can find here:
    >
    > http://www.scriptbox.at.tt/index.php...&site=1&list=1
    >
    > alt. Link:
    > http://scriptbox.boris-toll.at/index...&site=1&list=1
    >
    >
    >
    >
    > But
    >
    > "RICK" wrote:
    >

    > > How can one assign a variable value to a cell. The following doesn't work.
    > >
    > >
    > > .......
    > > strabc = rsTemp.Fields("Taxonomy")
    > > %>
    > >
    > > Dim Xcl
    > >
    > >
    > > Set Xcl = CreateObject("Excel.Application")
    > > Xcl.Visible = true
    > > Set newBook = Xcl.Workbooks.Add
    > > newBook.Worksheets(1).Cells(1,1).value=strabc
    > > </script>

      My System SpecsSystem Spec

  3. #3


    Al Dunbar Guest

    Re: Assign variable value to excel cell


    "RICK" <RICK@xxxxxx> wrote in message
    news:A64D768B-A29D-4693-B771-7CC7EDF3257E@xxxxxx

    >I too believe that strabc does not have a value. The value is set earlier
    >in
    > my code between <% and %>. But the value does not seem to be carried over
    > to
    > the code between the <script> tags.
    As well it should not. The code between the <% and %> is called server side
    scripting, as that code runs on the server. The <script> tags bracket code
    that runs on the client. It has access to variables and data defined within
    its own namespace - which does not include the namespace of code running on
    the server.

    Not only is this a good thing, but, I think it would be quite difficult for
    server-side and client-side to be made to interact as you think they should.
    The server-side code *could* inject data into the client-side code using
    dynamic HTML techniques.

    /Al

    >
    >
    >
    > "Bishop" wrote:
    >

    >> Hi,
    >>
    >> the code works.
    >> Are you sure that variable "strabc" has a value?
    >>
    >> I try this and it works:
    >> ---
    >> Dim Xcl
    >> Dim strabc
    >>
    >> strabc = "Test"
    >>
    >> Set Xcl = CreateObject("Excel.Application")
    >> Xcl.Visible = true
    >> Set newBook = Xcl.Workbooks.Add
    >> newBook.Worksheets(1).Cells(1,1).value=strabc
    >> ---
    >>
    >> Check the content of strabc like this:
    >> ---
    >> Dim Xcl
    >>
    >> msgbox strabc
    >>
    >> Set Xcl = CreateObject("Excel.Application")
    >> Xcl.Visible = true
    >> Set newBook = Xcl.Workbooks.Add
    >> newBook.Worksheets(1).Cells(1,1).value=strabc
    >>
    >> ---
    >>
    >> More Script Samples about VBScript and Excel you can find here:
    >>
    >> http://www.scriptbox.at.tt/index.php...&site=1&list=1
    >>
    >> alt. Link:
    >> http://scriptbox.boris-toll.at/index...&site=1&list=1
    >>
    >>
    >>
    >>
    >> But
    >>
    >> "RICK" wrote:
    >>

    >> > How can one assign a variable value to a cell. The following doesn't
    >> > work.
    >> >
    >> >
    >> > .......
    >> > strabc = rsTemp.Fields("Taxonomy")
    >> > %>
    >> >
    >> > Dim Xcl
    >> >
    >> >
    >> > Set Xcl = CreateObject("Excel.Application")
    >> > Xcl.Visible = true
    >> > Set newBook = Xcl.Workbooks.Add
    >> > newBook.Worksheets(1).Cells(1,1).value=strabc
    >> > </script>


      My System SpecsSystem Spec

  4. #4


    RICK Guest

    Re: Assign variable value to excel cell

    Thanks. Makes good sense.

    Now, if I would want to create my Excel file server side it seems to make
    sense to put the Set Xcl = CreateObject("Excel.Application") between the <%
    %>. However, when I do that I receive "ActiveX component can't create
    object: 'Excel.Application' ". If I change CreateObject to
    Server.CreateObject I get Server.CreateObject Failed. Can you explain? My
    thoughts are:

    1. The Server.CreateObject Failed error indicates that Excel is not
    installed on the server.
    2. But what about the issue with CreateObject?

    "Al Dunbar" wrote:

    >
    > "RICK" <RICK@xxxxxx> wrote in message
    > news:A64D768B-A29D-4693-B771-7CC7EDF3257E@xxxxxx

    > >I too believe that strabc does not have a value. The value is set earlier
    > >in
    > > my code between <% and %>. But the value does not seem to be carried over
    > > to
    > > the code between the <script> tags.
    >
    > As well it should not. The code between the <% and %> is called server side
    > scripting, as that code runs on the server. The <script> tags bracket code
    > that runs on the client. It has access to variables and data defined within
    > its own namespace - which does not include the namespace of code running on
    > the server.
    >
    > Not only is this a good thing, but, I think it would be quite difficult for
    > server-side and client-side to be made to interact as you think they should.
    > The server-side code *could* inject data into the client-side code using
    > dynamic HTML techniques.
    >
    > /Al
    >

    > >
    > >
    > >
    > > "Bishop" wrote:
    > >

    > >> Hi,
    > >>
    > >> the code works.
    > >> Are you sure that variable "strabc" has a value?
    > >>
    > >> I try this and it works:
    > >> ---
    > >> Dim Xcl
    > >> Dim strabc
    > >>
    > >> strabc = "Test"
    > >>
    > >> Set Xcl = CreateObject("Excel.Application")
    > >> Xcl.Visible = true
    > >> Set newBook = Xcl.Workbooks.Add
    > >> newBook.Worksheets(1).Cells(1,1).value=strabc
    > >> ---
    > >>
    > >> Check the content of strabc like this:
    > >> ---
    > >> Dim Xcl
    > >>
    > >> msgbox strabc
    > >>
    > >> Set Xcl = CreateObject("Excel.Application")
    > >> Xcl.Visible = true
    > >> Set newBook = Xcl.Workbooks.Add
    > >> newBook.Worksheets(1).Cells(1,1).value=strabc
    > >>
    > >> ---
    > >>
    > >> More Script Samples about VBScript and Excel you can find here:
    > >>
    > >> http://www.scriptbox.at.tt/index.php...&site=1&list=1
    > >>
    > >> alt. Link:
    > >> http://scriptbox.boris-toll.at/index...&site=1&list=1
    > >>
    > >>
    > >>
    > >>
    > >> But
    > >>
    > >> "RICK" wrote:
    > >>
    > >> > How can one assign a variable value to a cell. The following doesn't
    > >> > work.
    > >> >
    > >> >
    > >> > .......
    > >> > strabc = rsTemp.Fields("Taxonomy")
    > >> > %>
    > >> >
    > >> > Dim Xcl
    > >> >
    > >> >
    > >> > Set Xcl = CreateObject("Excel.Application")
    > >> > Xcl.Visible = true
    > >> > Set newBook = Xcl.Workbooks.Add
    > >> > newBook.Worksheets(1).Cells(1,1).value=strabc
    > >> > </script>
    >
    >
    >

      My System SpecsSystem Spec

  5. #5


    Bob Barrows [MVP] Guest

    Re: Assign variable value to excel cell

    RICK wrote:

    > How can one assign a variable value to a cell. The following doesn't
    > work.
    >
    >
    > .......
    > strabc = rsTemp.Fields("Taxonomy")
    > %>
    >
    > Dim Xcl
    >
    >
    > Set Xcl = CreateObject("Excel.Application")
    > Xcl.Visible = true
    > Set newBook = Xcl.Workbooks.Add
    > newBook.Worksheets(1).Cells(1,1).value=strabc
    > </script>
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.



      My System SpecsSystem Spec

  6. #6


    Bob Barrows [MVP] Guest

    Re: Assign variable value to excel cell

    RICK wrote:

    > How can one assign a variable value to a cell. The following doesn't
    > work.
    >
    >
    > .......
    > strabc = rsTemp.Fields("Taxonomy")
    > %>
    >
    > Dim Xcl
    >
    >
    > Set Xcl = CreateObject("Excel.Application")
    > Xcl.Visible = true
    > Set newBook = Xcl.Workbooks.Add
    > newBook.Worksheets(1).Cells(1,1).value=strabc
    > </script>
    strabc is a server-side variable, quite out of scope to this client-side
    code. What you would have to do is response.write the value contained by
    strabc, like this:

    newBook.Worksheets(1).Cells(1,1).value="<%=strabc%>"

    You will probably want to ensure that any double-quotes in the string
    are escaped in your server-side code:

    strabc = replace(rsTemp.Fields("Taxonomy"),"""","""""")

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.




      My System SpecsSystem Spec

  7. #7


    Bob Barrows [MVP] Guest

    Re: Assign variable value to excel cell

    RICK wrote:

    > How can one assign a variable value to a cell. The following doesn't
    > work.
    >
    >
    > .......
    > strabc = rsTemp.Fields("Taxonomy")
    > %>
    >
    > Dim Xcl
    >
    >
    > Set Xcl = CreateObject("Excel.Application")
    > Xcl.Visible = true
    > Set newBook = Xcl.Workbooks.Add
    > newBook.Worksheets(1).Cells(1,1).value=strabc
    > </script>
    strabc is a server-side variable, quite out of scope to this client-side
    code. What you would have to do is response.write the value contained by
    strabc, like this:

    newBook.Worksheets(1).Cells(1,1).value="<%=strabc%>"

    You will probably want to ensure that any double-quotes in the string
    are escaped in your server-side code:

    strabc = replace(rsTemp.Fields("Taxonomy"),"""","""""")

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.




      My System SpecsSystem Spec

  8. #8


    Bob Barrows [MVP] Guest

    Re: Assign variable value to excel cell

    RICK wrote:

    > How can one assign a variable value to a cell. The following doesn't
    > work.
    >
    >
    > .......
    > strabc = rsTemp.Fields("Taxonomy")
    > %>
    >
    > Dim Xcl
    >
    >
    > Set Xcl = CreateObject("Excel.Application")
    > Xcl.Visible = true
    > Set newBook = Xcl.Workbooks.Add
    > newBook.Worksheets(1).Cells(1,1).value=strabc
    > </script>
    strabc is a server-side variable, quite out of scope to this client-side
    code. What you would have to do is response.write the value contained by
    strabc, like this:

    newBook.Worksheets(1).Cells(1,1).value="<%=strabc%>"

    You will probably want to ensure that any double-quotes in the string
    are escaped in your server-side code:

    strabc = replace(rsTemp.Fields("Taxonomy"),"""","""""")

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.





      My System SpecsSystem Spec

  9. #9


    Al Dunbar Guest

    Re: Assign variable value to excel cell


    "RICK" <RICK@xxxxxx> wrote in message
    news:8A35E338-3F4E-43E1-BCBB-BEC66822B40C@xxxxxx

    > Thanks. Makes good sense.
    >
    > Now, if I would want to create my Excel file server side it seems to make
    > sense to put the Set Xcl = CreateObject("Excel.Application") between the
    > <%
    > %>. However, when I do that I receive "ActiveX component can't create
    > object: 'Excel.Application' ". If I change CreateObject to
    > Server.CreateObject I get Server.CreateObject Failed. Can you explain?
    > My
    > thoughts are:
    >
    > 1. The Server.CreateObject Failed error indicates that Excel is not
    > installed on the server.
    Either that, or the service account that is actually executing the server
    side script lacks the permissions to create such an object.

    > 2. But what about the issue with CreateObject?
    That is not the issue. The issue is that, when your server side script makes
    an excel application object visible, will the user be able to run into the
    server room to see it on the monitor connected to the server?

    Server-side code does a good job of managing data, but it cannot do much in
    the way of interacting with the user other than by modifying what the user
    sees in the browser window.

    /Al

    >
    > "Al Dunbar" wrote:
    >

    >>
    >> "RICK" <RICK@xxxxxx> wrote in message
    >> news:A64D768B-A29D-4693-B771-7CC7EDF3257E@xxxxxx

    >> >I too believe that strabc does not have a value. The value is set
    >> >earlier
    >> >in
    >> > my code between <% and %>. But the value does not seem to be carried
    >> > over
    >> > to
    >> > the code between the <script> tags.
    >>
    >> As well it should not. The code between the <% and %> is called server
    >> side
    >> scripting, as that code runs on the server. The <script> tags bracket
    >> code
    >> that runs on the client. It has access to variables and data defined
    >> within
    >> its own namespace - which does not include the namespace of code running
    >> on
    >> the server.
    >>
    >> Not only is this a good thing, but, I think it would be quite difficult
    >> for
    >> server-side and client-side to be made to interact as you think they
    >> should.
    >> The server-side code *could* inject data into the client-side code using
    >> dynamic HTML techniques.
    >>
    >> /Al
    >>

    >> >
    >> >
    >> >
    >> > "Bishop" wrote:
    >> >
    >> >> Hi,
    >> >>
    >> >> the code works.
    >> >> Are you sure that variable "strabc" has a value?
    >> >>
    >> >> I try this and it works:
    >> >> ---
    >> >> Dim Xcl
    >> >> Dim strabc
    >> >>
    >> >> strabc = "Test"
    >> >>
    >> >> Set Xcl = CreateObject("Excel.Application")
    >> >> Xcl.Visible = true
    >> >> Set newBook = Xcl.Workbooks.Add
    >> >> newBook.Worksheets(1).Cells(1,1).value=strabc
    >> >> ---
    >> >>
    >> >> Check the content of strabc like this:
    >> >> ---
    >> >> Dim Xcl
    >> >>
    >> >> msgbox strabc
    >> >>
    >> >> Set Xcl = CreateObject("Excel.Application")
    >> >> Xcl.Visible = true
    >> >> Set newBook = Xcl.Workbooks.Add
    >> >> newBook.Worksheets(1).Cells(1,1).value=strabc
    >> >>
    >> >> ---
    >> >>
    >> >> More Script Samples about VBScript and Excel you can find here:
    >> >>
    >> >> http://www.scriptbox.at.tt/index.php...&site=1&list=1
    >> >>
    >> >> alt. Link:
    >> >> http://scriptbox.boris-toll.at/index...&site=1&list=1
    >> >>
    >> >>
    >> >>
    >> >>
    >> >> But
    >> >>
    >> >> "RICK" wrote:
    >> >>
    >> >> > How can one assign a variable value to a cell. The following
    >> >> > doesn't
    >> >> > work.
    >> >> >
    >> >> >
    >> >> > .......
    >> >> > strabc = rsTemp.Fields("Taxonomy")
    >> >> > %>
    >> >> >
    >> >> > Dim Xcl
    >> >> >
    >> >> >
    >> >> > Set Xcl = CreateObject("Excel.Application")
    >> >> > Xcl.Visible = true
    >> >> > Set newBook = Xcl.Workbooks.Add
    >> >> > newBook.Worksheets(1).Cells(1,1).value=strabc
    >> >> > </script>
    >>
    >>
    >>


      My System SpecsSystem Spec

  10. #10


    gimme_this_gimme_that Guest

    Re: Assign variable value to excel cell

    ....
    strabc = rsTemp.Fields("Taxonomy")
    %>

    Dim Xcl

    Set Xcl = CreateObject("Excel.Application")
    Xcl.Visible = true
    Set newBook = Xcl.Workbooks.Add
    newBook.Worksheets(1).Cells(1,1).value=<%=strabc%> ' <--- Look here
    </script>


      My System SpecsSystem Spec

RE: Assign variable value to excel cell problems?

Similar Threads
Thread Thread Starter Forum Replies Last Post
Assign $true value to a variable and pass to function Don Pedro PowerShell 3 29 Mar 2010
VistaPro + Excel 2007- cell copy very very slow Robert M Vista General 3 08 Apr 2009
VBscript Returns wrong value from Excel Cell michael.jarratt VB Script 0 13 Mar 2009
Is there a write-multi-cell-at-once function in VBS for Excel? Tony Bansten VB Script 2 20 Jun 2008
Need sample for reading value from and writing value to cell E53 from outside Excel Tony Bansten VB Script 0 16 Jun 2008