Changing Excel Files - Easy?

[hyme]

Howdy!

I want to update a test.xls file. I can open Excel97 and open the file I want, but how do you set the Sheet and add values per cell using vb6.

here's what I have so far

Dim MyXL As Object
Dim ExcelWasNotRunning As Boolean

Set MyXL = GetObject(, "Excel.Application")
if Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear

Set MyXL = GetObject("C:\test.xls")
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True

For example I want to add "hello" to cell B13

If you can help, Thanx

[coox]

Right, first off, have you set a reference to Excel? If not:

Project/References, scroll down and check the Microsoft Excel 8.0 (or whatever) object library. Ok.

Now for code:

Sub Main()
 Dim myXL As Excel.Application
'*****************************
 Set myXL = CreateObject("Excel.Application")
 With myXL
  .Workbooks.Open "C:\Windows\Desktop\Fred.xls" 'Pick one of these
  .Workbooks.Add '                               Pick one of these
   .Range("B13") = "Hello"
  .ActiveWorkbook.SaveAs ("C:\Windows\Desktop\Fred.xls") 'Pick one of these
  .ActiveWorkbook.Save '                                  Pick one of these
 End With
 myXL.Quit
 Set myXL = Nothing
End Sub

Email me if you want any more help. But once you have this bit working, you could just go into excel and record anything you want to do, then copy and paste the code across.

[hyme]

That's great, but how do you determine which Sheet it is, that you are making the changes on. Example (Sheet 2)

[coox]

Put this in there somewhere:

.Sheets("Sheet2").Select

Like I said, try opening Excel and recording stuff, like you could have recorded selecting sheet2 and you'd have got the code above...