Hi Everyone,
I've worked with Access programing for a while, but now I
need to use Excel VBA to automate some data analysis with
some charts on the report. I am not sure where to start.
Can anyone suggest what book I should buy to get the
Excel programing knowledge? Or is there any Excel
automation examples that I can learn from? Thanks for any
suggestions?
Jenny

Hi Jenny,

In terms of books, The Wrox "Excel 2003 VBA Programmer's Reference"
(http://www.wrox.com/WileyCDA/WroxTit...764556606.html) is very
good, well at least the 2002 version was anyway (and it has a specific
chapter on Charts).

For general web info, I've found the following to be good sources:

http://www.mrexcel.com/ (also has book, although I've not read it myself)

http://j-walk.com/ss/excel/tips/index.htm (excellent site, full of example
code), plus several books by him
(http://www.j-walk.com/ss/books/xlbook25.htm again, I haven't read it but it
looks pretty comprehensive.)

and lastly, David McRitchie's site
http://www.mvps.org/dmcritchie/excel....htm#tutorials also has a lot of
good stuff on it including a much more comprehensive list of links than the
one here!

Anyway, hope that helps.

Best regards

John




"Jenny G." wrote in message
...
Hi Everyone,
I've worked with Access programing for a while, but now I
need to use Excel VBA to automate some data analysis with
some charts on the report. I am not sure where to start.
Can anyone suggest what book I should buy to get the
Excel programing knowledge? Or is there any Excel
automation examples that I can learn from? Thanks for any
suggestions?
Jenny

Excel has a macro recorder. Turn on the macro recorder and build the chart
manually. then turn off the recorder and look at the recorded code. This
will give you some insights into the objects and methods of Excel (which is
the primary knowledge you lack if you have done VBA in Access). Do this
with other actions you might do in excel. Note that the recorded code will
echo what you do manually in terms of recording select and selection. In
most cases, in code you write you would not use select and selection, but
just refer to the object directly without selecting.

John gave you some good book references. If you want sample code, you can
checkout the MS Knowledgebase
http://support.microsoft.com

some examples:

http://support.microsoft.com/default...40&Product=xlw
XL97: How To Create a Dynamic Chart Using Visual Basic

http://support.microsoft.com/default...80&Product=xlw
XL2000: How to Use Visual Basic to Create a Dynamic Chart

http://support.microsoft.com/default...55&Product=xlw
Using Visual Basic to Create a Chart Using a Dynamic Range

and MSDN

http://msdn.microsoft.com

Of course you can also get help right here.

for charting examples see:
John Peltier's site
http://peltiertech.com/Excel/ChartsH...html#VBAcharts

--
Regards,
Tom Ogilvy

"Jenny G." wrote in message
...
Hi Everyone,
I've worked with Access programing for a while, but now I
need to use Excel VBA to automate some data analysis with
some charts on the report. I am not sure where to start.
Can anyone suggest what book I should buy to get the
Excel programing knowledge? Or is there any Excel
automation examples that I can learn from? Thanks for any
suggestions?
Jenny

Hi John,
Thank you very much for your recomemdations for books and
useful websites. I appreciate your help very much.
Jenny

-----Original Message-----
Hi Jenny,

In terms of books, The Wrox "Excel 2003 VBA Programmer's
Reference"
(http://www.wrox.com/WileyCDA/WroxTitle/productCd-
0764556606.html) is very
good, well at least the 2002 version was anyway (and it
has a specific
chapter on Charts).

For general web info, I've found the following to be
good sources:

http://www.mrexcel.com/ (also has book, although I've
not read it myself)

http://j-walk.com/ss/excel/tips/index.htm (excellent
site, full of example
code), plus several books by him
(http://www.j-walk.com/ss/books/xlbook25.htm again, I
haven't read it but it
looks pretty comprehensive.)

and lastly, David McRitchie's site
http://www.mvps.org/dmcritchie/excel....htm#tutorials
also has a lot of
good stuff on it including a much more comprehensive
list of links than the
one here!

Anyway, hope that helps.

Best regards

John




"Jenny G." wrote
in message
...
Hi Everyone,
I've worked with Access programing for a while, but
now I
need to use Excel VBA to automate some data analysis
with
some charts on the report. I am not sure where to
start.
Can anyone suggest what book I should buy to get the
Excel programing knowledge? Or is there any Excel
automation examples that I can learn from? Thanks for
any
suggestions?
Jenny


.

Hi Tom,
Thank you very much for your help. I'll try to use the
macro recorder.
Thanks,
jenny
-----Original Message-----
Excel has a macro recorder. Turn on the macro recorder
and build the chart
manually. then turn off the recorder and look at the
recorded code. This
will give you some insights into the objects and methods
of Excel (which is
the primary knowledge you lack if you have done VBA in
Access). Do this
with other actions you might do in excel. Note that
the recorded code will
echo what you do manually in terms of recording select
and selection. In
most cases, in code you write you would not use select
and selection, but
just refer to the object directly without selecting.

John gave you some good book references. If you want
sample code, you can
checkout the MS Knowledgebase
http://support.microsoft.com

some examples:

http://support.microsoft.com/default.aspx?scid=kb;en-
us;157940&Product=xlw
XL97: How To Create a Dynamic Chart Using Visual Basic

http://support.microsoft.com/default.aspx?scid=kb;en-
us;213780&Product=xlw
XL2000: How to Use Visual Basic to Create a Dynamic Chart

http://support.microsoft.com/default.aspx?scid=kb;en-
us;146055&Product=xlw
Using Visual Basic to Create a Chart Using a Dynamic
Range

and MSDN

http://msdn.microsoft.com

Of course you can also get help right here.

for charting examples see:
John Peltier's site
http://peltiertech.com/Excel/ChartsH...ickChartVBA.ht
ml#VBAcharts

--
Regards,
Tom Ogilvy

"Jenny G." wrote
in message
...
Hi Everyone,
I've worked with Access programing for a while, but
now I
need to use Excel VBA to automate some data analysis
with
some charts on the report. I am not sure where to
start.
Can anyone suggest what book I should buy to get the
Excel programing knowledge? Or is there any Excel
automation examples that I can learn from? Thanks for
any
suggestions?
Jenny


.

Jenny,
I was in a similar position having done Microsoft Access for several
years. I developed an Excel application using VBA. I found the first
differences were in referencing cells between worksheets and trying to
figure this out.

Here is a way of doing it:
Worksheets("Sheet1").Cells(17, 1).Value - this gives the values in sheet
1 in that particular cell.

There are also some neat commands for accessing files for example on a
share drive - you can find help with these in Excel VBA help.
Set fs = Application.FileSearch
With fs
.LookIn = "S:\Trace\Trace-" & yr & fld
'"C:\Miscellaneous\business\Download MAr 05\Trace-" & yr & fld '
.Filename = sdr & "*.SP"
If .Execute 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s)
found." & Chr(10) & "Side " & sd
For i = 1 To .FoundFiles.Count
x = .FoundFiles(i)
mbr = MsgBox(x, 4, "Files found")
If mbr = 6 Then
mbrt = "Yes"
Else
mbrt = "No"
End If
'MsgBox "You chose " & mbrt
If mbr = 6 Then ' yes
Worksheets("References").Cells(fcnt + 2,
2).Value = x
Worksheets("References").Cells(fcnt + 2,
4).Value = sd
Worksheets("References").Cells(fcnt + 2,
8).Value = an
Worksheets("References").Cells(fcnt + 2,
5).Value = CTyp
If rtmn < 0.01 Then
rta = rt & " < " & rtmx & " %"
Else
If rtmx 99.9 Then
rta = rt & " " & rtmn & " %"
Else
rta = rt & " " & rtmn & " %, and < " &
rtmx & " %"
End If
End If
rta2 = rta & wv & " Side " & sd & Chr(10) & "Pol
" & pl
Worksheets("References").Cells(fcnt + 2,
6).Value = rta2 & ", AOI " & aoi & " deg"
Worksheets("References").Cells(fcnt + 2,
9).Value = rort & " % " & Chr(10) & rta2 & Chr(10) & "AOI " & aoi & "
deg"
fcnt = fcnt + 1
End If
Next i
Else
MsgBox "There were no files found."
End If
End With
Any more info - let me know- Richard


*** Sent via Developersdex http://www.developersdex.com ***