Windows Vista Forums
Vista Forums Home Join Vista Forums Windows 7 Forum Vista Tutorials Tags
Welcome to Windows Vista Forums. Our forum is dedicated to helping you find solutions with any problems, errors or issues you are experiencing with Windows Vista. The Vista forum also covers news and updates and has an extensive Windows Vista tutorial section that covers a wide range of tips and tricks.

Go Back   Vista Forums > Misc Newsgroups > VB Script

Vista - RE: Assign variable value to excel cell

Reply
 
Old 10-15-2008   #1 (permalink)
Bishop


 
 

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:
Quote:

> 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
Old 10-15-2008   #2 (permalink)
RICK


 
 

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:
Quote:

> 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:
>
Quote:

> > 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
Old 10-15-2008   #3 (permalink)
Al Dunbar


 
 

Re: Assign variable value to excel cell


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

>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
Quote:

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

>> 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:
>>
Quote:

>> > 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
Old 10-15-2008   #4 (permalink)
RICK


 
 

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:
Quote:

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

> >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
>
Quote:

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

> >> 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
Old 10-15-2008   #5 (permalink)
Bob Barrows [MVP]


 
 

Re: Assign variable value to excel cell

RICK wrote:
Quote:

> 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
Old 10-15-2008   #6 (permalink)
Bob Barrows [MVP]


 
 

Re: Assign variable value to excel cell

RICK wrote:
Quote:

> 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
Old 10-15-2008   #7 (permalink)
Bob Barrows [MVP]


 
 

Re: Assign variable value to excel cell

RICK wrote:
Quote:

> 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
Old 10-15-2008   #8 (permalink)
Bob Barrows [MVP]


 
 

Re: Assign variable value to excel cell

RICK wrote:
Quote:

> 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
Old 10-15-2008   #9 (permalink)
Al Dunbar


 
 

Re: Assign variable value to excel cell


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

> 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.
Quote:

> 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
Quote:

>
> "Al Dunbar" wrote:
>
Quote:

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

>> >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
>>
Quote:

>> >
>> >
>> >
>> > "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
Old 10-15-2008   #10 (permalink)
gimme_this_gimme_that


 
 

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
Reply

Thread Tools


Similar Threads
Thread Forum
VistaPro + Excel 2007- cell copy very very slow Vista General
How to sedn data to an excel cell from another file using a vb script VB Script
VBscript Returns wrong value from Excel Cell VB Script
Is there a write-multi-cell-at-once function in VBS for Excel? VB Script
Need sample for reading value from and writing value to cell E53 from outside Excel VB Script


Vista Forums is an independent web site and has not been authorized,
sponsored, or otherwise approved by Microsoft Corporation.
"Windows Vista", the Start Orb, and related materials are trademarks of Microsoft Corp.
© Designer Media Ltd

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46