Dave Sell
Jul 13th, 2004, 01:29 AM
Open up the VB IDE and begin a new Standard EXE project.
I have Excel 2000 so I add a reference to "Microsoft Excel 9.0 Object Library" or Excel9.OLB. (Go to [Project]->[References] on the menu)
Now you will be able to reference a new Object Library in your code, the Excel Object and the 4 child Objects you will use the most:
- Application
- Workbook
- Worksheet
- Range
Lets start Excel - I make some Private data members in the form:
Option Explicit
Private m_appExcel As Excel.Application
Private m_Book As Excel.Workbook
Private m_Sheet As Excel.Worksheet
'
Now instantiate the Excel Application Objects:
Private Sub Form_Load()
Set m_appExcel = New Excel.Application
Set m_Book = m_appExcel.Workbooks.Add
Set m_Sheet = m_Book.Sheets(1)
End Sub
Lets see it:
Private Sub Command1_Click()
m_appExcel.Visible = True
End Sub
Make sure to clean up:
Private Sub Form_Unload(Cancel As Integer)
m_appExcel.DisplayAlerts = False ' Turn off prompting to save file
m_appExcel.Quit
Set m_appExcel = Nothing
Set m_Book = Nothing
Set m_Sheet = Nothing
End Sub
So what? You may ask. Now what do I do with this thing? So lets get aquanted with the infamous Range object:
Private Sub Command2_Click()
Dim rngTest As Excel.Range
'
Set rngTest = m_Sheet.Range("C3")
rngTest(1, 1) = Me.Text1.Text
Set rngTest = Nothing
End Sub
There's really nothing to it. Once you have a Range Object in your hands, the sky is the limit! You can proceed to populate entire Worksheets with data from a database (ie.. Recordset), search, sort, and print, and I hear even make charts.
Give a man a beer and he'll drink for 5 minutes. But show him where the beer is, and he won't bother you for hours. A VBforum guru taught me this nice trick - learn how to do almost ANYTHING in VB by making your own macro for it first:
Want to place gridlines in a Range? No problem! Open up Excel standalone and start a new Macro, begin recording. Select the Range of your choice. Then use the menus to place some gridlines in the Range. Stop recording. Now go in and EDIT the macro and you will see something like this (I took out unnecessary stuff):
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/13/2004 by Dave Sell
'
Range("B2:F6").Select
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
End With
End Sub
This code can be executed from your project, just like that (a-sha).
Well, that's all for Part 1. I know that's not much info but it will get you started. Unfortunately the Excel Library is very sparsely documented. I recommend buying a book for serious programmers. The book that I use as a reference was from O'Reily:
Writing Excel Macros with VBA, 2nd Edition
By Steven Roman, Ph.D.
http://www.oreilly.com/catalog/exlmacro2/index.html
It was not exceptionally indexed, but got me through most problems.
Another similar tutorial can be found here:
http://www.it-faq.pl/data/misc1/EN-US/219/151.htm
I have Excel 2000 so I add a reference to "Microsoft Excel 9.0 Object Library" or Excel9.OLB. (Go to [Project]->[References] on the menu)
Now you will be able to reference a new Object Library in your code, the Excel Object and the 4 child Objects you will use the most:
- Application
- Workbook
- Worksheet
- Range
Lets start Excel - I make some Private data members in the form:
Option Explicit
Private m_appExcel As Excel.Application
Private m_Book As Excel.Workbook
Private m_Sheet As Excel.Worksheet
'
Now instantiate the Excel Application Objects:
Private Sub Form_Load()
Set m_appExcel = New Excel.Application
Set m_Book = m_appExcel.Workbooks.Add
Set m_Sheet = m_Book.Sheets(1)
End Sub
Lets see it:
Private Sub Command1_Click()
m_appExcel.Visible = True
End Sub
Make sure to clean up:
Private Sub Form_Unload(Cancel As Integer)
m_appExcel.DisplayAlerts = False ' Turn off prompting to save file
m_appExcel.Quit
Set m_appExcel = Nothing
Set m_Book = Nothing
Set m_Sheet = Nothing
End Sub
So what? You may ask. Now what do I do with this thing? So lets get aquanted with the infamous Range object:
Private Sub Command2_Click()
Dim rngTest As Excel.Range
'
Set rngTest = m_Sheet.Range("C3")
rngTest(1, 1) = Me.Text1.Text
Set rngTest = Nothing
End Sub
There's really nothing to it. Once you have a Range Object in your hands, the sky is the limit! You can proceed to populate entire Worksheets with data from a database (ie.. Recordset), search, sort, and print, and I hear even make charts.
Give a man a beer and he'll drink for 5 minutes. But show him where the beer is, and he won't bother you for hours. A VBforum guru taught me this nice trick - learn how to do almost ANYTHING in VB by making your own macro for it first:
Want to place gridlines in a Range? No problem! Open up Excel standalone and start a new Macro, begin recording. Select the Range of your choice. Then use the menus to place some gridlines in the Range. Stop recording. Now go in and EDIT the macro and you will see something like this (I took out unnecessary stuff):
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/13/2004 by Dave Sell
'
Range("B2:F6").Select
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
End With
End Sub
This code can be executed from your project, just like that (a-sha).
Well, that's all for Part 1. I know that's not much info but it will get you started. Unfortunately the Excel Library is very sparsely documented. I recommend buying a book for serious programmers. The book that I use as a reference was from O'Reily:
Writing Excel Macros with VBA, 2nd Edition
By Steven Roman, Ph.D.
http://www.oreilly.com/catalog/exlmacro2/index.html
It was not exceptionally indexed, but got me through most problems.
Another similar tutorial can be found here:
http://www.it-faq.pl/data/misc1/EN-US/219/151.htm