VB6, Access, Excel

MajinLoki
MajinLoki used Ask the Experts™
on
I have a VB6 program that creates, edits, and maintains access databases.  There are 3 tables in my database.  I need to be able to search the database and test if a value is null and if it is put fields from the records in certain cells in an Excel sheet.  I also want to be able to search to see if the record is a certain type of record(each record represents a course, and there are up to 6 different types of courses) and if it is a selected type put the record in an excel sheet in a report.  This can be sorted by course number or the instructor name.  The problem is, I can't figure out how to connect to this spreadsheet, nor how to put information in the cells.  Any help would be greatly appreciated.  Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Start by referencing this file...

C:\Program Files\Microsoft Office\Office\Excel9.OLB

It may be something other than Excel9 depending on your version of Excel.  Then use these lines...

Dim wb As Excel.Workbook
Set wb = Excel.Application.Workbooks.Open ("C:\Temp\Test.xls")

This will return the workbook object to work with from there you have many things that you can do with it.  Explore the object to see all the properties.

Hope this helps.

Nephil
Barry TiceBusiness Analyst

Commented:
MajinLoki --

This code shows how to get cell contents from a spreadsheet. As Nephil says, you need to add a reference to Excel objects to your project (from Project -> References..., check the Microsoft Excel 9.0 Object Library option).

==== BEGIN PASTE ====
Private Sub Command1_Click()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

    Set xlApp = New Excel.Application
    Set xlBook = xlApp.Workbooks.Open("C:\File.xls")
    Set xlSheet = xlBook.Worksheets("Sheet1")
    Range("B3").Select
    MsgBox ActiveCell.Text
    Range("C4").Select
    MsgBox ActiveCell.Text
   
    xlApp.Quit
End Sub
===== END PASTE =====

Note that this gives you two messageboxes with the contents of cell B3 and C4, respectively.

If you need to grab cell contents from a different worksheet, name it accordingly. Likewise, you'll need to set the proper file name (or use a string holding the path) to open the correct workbook (Excel file).

Good luck.

-- Barry Tice
Why not make a query based on your filter before you export the whole file to excel.
Author of the Year 2009

Commented:
Hi MajinLoki,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept Nephil's comment(s) as an answer.

MajinLoki, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept THIS comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial