Excel Question

[Doc Scheinder]

Hello guys! I don't have my books with me right now and I'm trying to create an application that:

1) Opens an Existing Excel Worksheet
2) Read in the Values from the Active Worksheet
3) Write data back to the Active Worksheet

Does anyone have some basic sample code that will do this? I've seen a lot of the code posted here on this site but it's a little too in-depth for what I'm trying to do. Thanks for the help!

[darre1]

you need to use Automation.

look it up in MSDN, if you don't get anywhere let me know where you're stuck?

[billwagnon]

Set a reference in your project to Excel. Declare an object variable and start Excel.

Dim myXl as Object

StartExcel myXL

Here's what I use to get the Excel object:

Public Sub StartExcel(ByRef myXL As Object)

Dim ExcelApp As Object   ' Declare variable to hold the reference.
   
'get xl if it is running
On Error Resume Next

Set ExcelApp = GetObject(, "Excel.Application")
'if it wasn't got, create it
If Err.Number = 429 Then
    Set ExcelApp = CreateObject("Excel.Application")
    Err.Clear
Else
    If Err.Number <> 0 Then MsgBox Err.Number & " " & Err.Description
End If

Set myXL = ExcelApp

Set ExcelApp = Nothing

End Sub

Then play with Excel:

With myXL
    .workbooks.add
    .range("a1").select
    .activecell.offset(1,0).select

  yourvariable = .activecell    'to read a value
  .activecell = yourvariable  'to write a value

End With

Use the Excel macro recorder to get code for manipulating the Excel worksheet. The macro recorder won't give you .activecell.offset(row,column) so keep it in mind.

If any one has a critique of this code, please post! Information is welcome!