devxlogo

Interfacing with Excel

Interfacing with Excel

Question:
From VB4.0 32 bit, I would like to be able to open excel 7.0, start a new spreadsheet, give it a name, put info in the spreadsheet, save the spreadsheet, and exit exit excel.

Answer:
First, you have to go to Tools->References and add “Microsoft Excel 5.0 Object Library” and “Excel Ctl 5.0 Library”. Then, use the following code:

   Dim exclApp As Object   Dim exclBook As Object   Dim exclSheet As Object      ‘   ‘ First, start Excel   ‘   Set exclApp = CreateObject(“Excel.Application”)      ‘   ‘ Next, create a workbook (contains worksheets)   ‘   Set exclBook = exclApp.Workbooks.Add   ‘   ‘ Finally, create a shortcut to the active sheet   ‘ of the workbook so that we don’t have to write   ‘ exclApp.ActiveWorkBook.ActiveSheet.Cells   ‘   Set exclSheet = exclApp.ActiveWorkbook.ActiveSheet   ‘   ‘ Set the value of 4 cells in the sheet   ‘   exclSheet.Cells(1, 1).Value = “Data Point 1”   exclSheet.Cells(1, 2).Value = “Data Point 2”   exclSheet.Cells(1, 3).Value = “Data Point 3”   exclSheet.Cells(1, 4).Value = “Data Point 4”   ‘   ‘ Save the file   ‘   exclSheet.SaveAs “c:	est.xls”   ‘   ‘ Clean up   ‘   Set exclSheet = Nothing   Set exclBook = Nothing   Set exclApp = Nothing   
This will get you started. If you need additional help, either refer to the help for each of the Excel objects (just hit F1).

devx-admin

Share the Post: