Results 1 to 5 of 5

Thread: [ Tutorial ] Programming Excel with Visual Basic 6 - Part 1

  1. #1

    Thread Starter
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961

    [ Tutorial ] Programming Excel with Visual Basic 6 - Part 1

    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:

    VB Code:
    1. Option Explicit
    2.  
    3. Private m_appExcel As Excel.Application
    4. Private m_Book As Excel.Workbook
    5. Private m_Sheet As Excel.Worksheet
    6. '

    Now instantiate the Excel Application Objects:

    VB Code:
    1. Private Sub Form_Load()
    2.     Set m_appExcel = New Excel.Application
    3.     Set m_Book = m_appExcel.Workbooks.Add
    4.     Set m_Sheet = m_Book.Sheets(1)
    5. End Sub

    Lets see it:

    VB Code:
    1. Private Sub Command1_Click()
    2.     m_appExcel.Visible = True
    3. End Sub

    Make sure to clean up:

    VB Code:
    1. Private Sub Form_Unload(Cancel As Integer)
    2.     m_appExcel.DisplayAlerts = False ' Turn off prompting to save file
    3.     m_appExcel.Quit
    4.     Set m_appExcel = Nothing
    5.     Set m_Book = Nothing
    6.     Set m_Sheet = Nothing
    7. End Sub

    So what? You may ask. Now what do I do with this thing? So lets get aquanted with the infamous Range object:

    VB Code:
    1. Private Sub Command2_Click()
    2.     Dim rngTest As Excel.Range
    3.     '
    4.     Set rngTest = m_Sheet.Range("C3")
    5.    
    6.     rngTest(1, 1) = Me.Text1.Text
    7.    
    8.     Set rngTest = Nothing
    9. 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):

    VB Code:
    1. Sub Macro1()
    2. '
    3. ' Macro1 Macro
    4. ' Macro recorded 7/13/2004 by Dave Sell
    5. '
    6.     Range("B2:F6").Select
    7.     With Selection.Borders(xlInsideVertical)
    8.         .LineStyle = xlContinuous
    9.         .Weight = xlThin
    10.     End With
    11.     With Selection.Borders(xlInsideHorizontal)
    12.         .LineStyle = xlContinuous
    13.         .Weight = xlThin
    14.     End With
    15. 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
    Last edited by Dave Sell; May 6th, 2005 at 06:28 PM.

  2. #2
    Fanatic Member
    Join Date
    Mar 2002
    Location
    AUSTRALIA
    Posts
    603

    Re: [Tutorial] Programming Excel with Visual Basic 6 - Part 1

    OOPs wrong thread
    Rob C

  3. #3

    Thread Starter
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961

    Re: [Tutorial] Programming Excel with Visual Basic 6 - Part 1

    Quote Originally Posted by RobCrombie
    OOPs wrong thread
    Hey, those links were OK. I esp. liked that demo that dude made. Can you post those links back up again? I found them useful.
    Nobody knows what software they want until after you've delivered what they originally asked for.

    Don't solve problems which don't exist.

    "If I had eight hours to cut down a tree, I'd spend six hours sharpening my axe." --- Abraham Lincoln (1809-1865)

    2 idiots don't make a genius.

  4. #4
    Fanatic Member
    Join Date
    Mar 2002
    Location
    AUSTRALIA
    Posts
    603

    Re: [Tutorial] Programming Excel with Visual Basic 6 - Part 1

    Rob C

  5. #5

    Thread Starter
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961

    Re: [ Tutorial ] Programming Excel with Visual Basic 6 - Part 1

    Update: The original post was based on MS Office 2000 being installed. Things changed with Office 2003. I had to browse to C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE to use the ActiveX Excel Objects in VB6 code.
    Last edited by Dave Sell; Jul 16th, 2007 at 11:22 PM.
    Nobody knows what software they want until after you've delivered what they originally asked for.

    Don't solve problems which don't exist.

    "If I had eight hours to cut down a tree, I'd spend six hours sharpening my axe." --- Abraham Lincoln (1809-1865)

    2 idiots don't make a genius.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width