By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,852 Members | 2,152 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,852 IT Pros & Developers. It's quick & easy.

How to use Excel.Chart with early binding?

P: n/a
When using automation (and especially with early binding) I've heard it is
best to use explicit references to
everything. For example:

Dim xlChart as Excel.Chart

rather than

Dim objChart as Object

Apparently the issue about avoiding creation of a global reference that can
result
in a lingering instance of Excel when xlapp = Nothing fails to close Excel.

As for using xlChart rather than objChart, I'm not sure how this looks in
practice.

I can't seem to get this to work:

Dim xlChart As Excel.Chart
Set xlChart = xlapp.Workbooks(strXlsFile).Worksheets(sn).ChartOb jects.Add _
(Left:=12.75, Top:=lngTop, Width:=650, Height:=497.25).Chart
xlChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn).Range(rst!str2), PlotBy:=xlColumns
xlChart.ChartType = xlLineMarkers
xlChart .. etc, etc ...

But this works fine:

Dim objChart As Excel.Chart
Set objChart = xlapp.Workbooks(strXlsFile).Worksheets(sn).ChartOb jects.Add _
(Left:=12.75, Top:=lngTop, Width:=650, Height:=497.25).Chart
objChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn).Range(rst!str2), PlotBy:=xlColumns
objChart.ChartType = xlLineMarkers
objChart .. etc, etc ...

As I instantiating xlChart correctly? Why is it different from objChart?

Thanks in advance.
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
xlChart is actually the name of a constant in Excel (equal to -4109: it's
used to indicate the type of Worksheet). Probably that's what's causing the
problem.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"deko" <de**@deko.com> wrote in message
news:t6*******************@newssvr14.news.prodigy. com...
When using automation (and especially with early binding) I've heard it is
best to use explicit references to
everything. For example:

Dim xlChart as Excel.Chart

rather than

Dim objChart as Object

Apparently the issue about avoiding creation of a global reference that
can
result
in a lingering instance of Excel when xlapp = Nothing fails to close
Excel.

As for using xlChart rather than objChart, I'm not sure how this looks in
practice.

I can't seem to get this to work:

Dim xlChart As Excel.Chart
Set xlChart = xlapp.Workbooks(strXlsFile).Worksheets(sn).ChartOb jects.Add
_
(Left:=12.75, Top:=lngTop, Width:=650, Height:=497.25).Chart
xlChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn).Range(rst!str2), PlotBy:=xlColumns
xlChart.ChartType = xlLineMarkers
xlChart .. etc, etc ...

But this works fine:

Dim objChart As Excel.Chart
Set objChart = xlapp.Workbooks(strXlsFile).Worksheets(sn).ChartOb jects.Add
_
(Left:=12.75, Top:=lngTop, Width:=650, Height:=497.25).Chart
objChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn).Range(rst!str2), PlotBy:=xlColumns
objChart.ChartType = xlLineMarkers
objChart .. etc, etc ...

As I instantiating xlChart correctly? Why is it different from objChart?

Thanks in advance.

Nov 13 '05 #2

P: n/a
> xlChart is actually the name of a constant in Excel (equal to -4109: it's
used to indicate the type of Worksheet). Probably that's what's causing the problem.


Thanks for the tip.

But I'm wondering if my syntax correct otherwise. For example:

Dim myChart As Excel.Chart
Set myChart = xlapp.Workbooks(strXlsFile).Worksheets(sn).ChartOb jects.Add _
(Left:=12.75, Top:=lngTop, Width:=650, Height:=497.25).Chart
myChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn).Range(rst!str2), PlotBy:=xlColumns
myChart.ChartType = xlLineMarkers
myChart .. etc, etc ...

Is it as simple as substituting:

Dim myChart as Excel.Chart

for

Dim objChart as Object

?
Nov 13 '05 #3

P: n/a
"deko" <de**@deko.com> wrote in message
news:MH*******************@newssvr14.news.prodigy. com...
xlChart is actually the name of a constant in Excel (equal to -4109: it's
used to indicate the type of Worksheet). Probably that's what's causing

the
problem.


Thanks for the tip.

But I'm wondering if my syntax correct otherwise. For example:

Dim myChart As Excel.Chart
Set myChart = xlapp.Workbooks(strXlsFile).Worksheets(sn).ChartOb jects.Add
_
(Left:=12.75, Top:=lngTop, Width:=650, Height:=497.25).Chart
myChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn).Range(rst!str2), PlotBy:=xlColumns
myChart.ChartType = xlLineMarkers
myChart .. etc, etc ...

Is it as simple as substituting:

Dim myChart as Excel.Chart

for

Dim objChart as Object


Your original post had Dim objChart As Excel.Chart, but yes, in general it's
that straightforward with Early Binding (where you've got a reference set to
Excel).

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Nov 13 '05 #4

P: n/a
> > Is it as simple as substituting:

Dim myChart as Excel.Chart

for

Dim objChart as Object
Your original post had Dim objChart As Excel.Chart, but yes, in general

it's that straightforward with Early Binding (where you've got a reference set to Excel).


Great! It was that simple.

This may be off topic, but another quirk I've encountered with early bound
Excel automation is when I set the reference to the application object:

Dim xlapp As Excel.Application
Set xlapp = CreateObject("Excel.Application")
If xlapp Is Nothing Then Err.Raise FAILED_TO_GET_EXCEL, _
"Could not initialize Microsoft Excel."
xlapp.Visible = False

This works, but I have to explicitly set "xlapp.Visible" to False - and I
still get a brief flash on the screen. Is there any way to avoid this?

Thanks for your help!
Nov 13 '05 #5

P: n/a
"deko" <de**@deko.com> wrote in message
news:mQ****************@newssvr13.news.prodigy.com ...
This may be off topic, but another quirk I've encountered with early bound
Excel automation is when I set the reference to the application object:

Dim xlapp As Excel.Application
Set xlapp = CreateObject("Excel.Application")
If xlapp Is Nothing Then Err.Raise FAILED_TO_GET_EXCEL, _
"Could not initialize Microsoft Excel."
xlapp.Visible = False

This works, but I have to explicitly set "xlapp.Visible" to False - and I
still get a brief flash on the screen. Is there any way to avoid this?


Sorry, I've not seen that (but then I always use Late Binding)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Nov 13 '05 #6

P: n/a
> Sorry, I've not seen that (but then I always use Late Binding)

Fair enough.

Can you help me convert to Late Binding?

Currently I am doing this:

Dim xlapp As Excel.Application
Set xlapp = CreateObject("Excel.Application")

Then I do stuff with:

xlapp.Workbooks(strXlsFile).Worksheets(sn).[whatever]

How would this look with Late Binding?
Nov 13 '05 #7

P: n/a
"deko" <de**@deko.com> wrote in message
news:2M*******************@newssvr14.news.prodigy. com...
Sorry, I've not seen that (but then I always use Late Binding)


Fair enough.

Can you help me convert to Late Binding?

Currently I am doing this:

Dim xlapp As Excel.Application
Set xlapp = CreateObject("Excel.Application")

Then I do stuff with:

xlapp.Workbooks(strXlsFile).Worksheets(sn).[whatever]

How would this look with Late Binding?


You haven't really given me enough to go by!

For the 3 lines you've given, the only change you'd have to make is

Dim xlapp As Object

instead of

Dim xlapp As Excel.Application

(and remove the reference you have set to Excel)

However, it's not entirely that simple: once you remove the reference to
Excel, you remove the ability to refer to intrinsic Excel variables. For
instance, looking back at your code, I see the following lines:

objChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn).Range(rst!str2), PlotBy:=xlColumns
objChart.ChartType = xlLineMarkers

You'd either have to add

Const xlColumns As Long = 2
Const xlLineMarkers As Long = 65

or change those two lines to

objChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn).Range(rst!str2), PlotBy:=2
objChart.ChartType = 65

What I typically do is add the reference to Excel, develop my code, remove
the reference and do a compile. For each constant reference that gets
flagged, I determine the value of the constant (usually in the Debug window
in an Excel session) and add it to a module of Excel constants. When I go to
my next project, I copy in the module of Excel constants, and add to it if
necessary.

While Late Binding may be a little slower (but only at the initial
instantiation, and, to be honest, I've never noticed it), it does mean you
don't have to worry what version of Excel your users have.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


Nov 13 '05 #8

P: n/a
> objChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn).Range(rst!str2), PlotBy:=xlColumns
objChart.ChartType = xlLineMarkers

You'd either have to add

Const xlColumns As Long = 2
Const xlLineMarkers As Long = 65
10-4. I added a bunch of constants.
What I typically do is add the reference to Excel, develop my code, remove
the reference and do a compile. For each constant reference that gets
flagged, I determine the value of the constant (usually in the Debug window in an Excel session) and add it to a module of Excel constants. When I go to my next project, I copy in the module of Excel constants, and add to it if
necessary.
That works great. Thanks for the tip.
While Late Binding may be a little slower (but only at the initial
instantiation, and, to be honest, I've never noticed it), it does mean you
don't have to worry what version of Excel your users have.


True. But since I'm releasing an MDE, the reference would stick and Access
would automatically upgrade it. So I was making the reference to Excel 9
(user base at O2K or higher). But the real hassle is that my development
box is AC2003 - so I have to copy the MDB to my AC2000 box and make the MDE
there (as you know, AC2003 will not MDE anything in AC2000 file format).
Still, I wanted to use Late Binding here. The app has grown into requiring
all kinds of Excel automation and I want to avoid the dreaded global
reference that can cause Excel not to quit properly.

Thanks again for your help and the detailed reply.
Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.