Read/Write to Excel

[AlexanderBB]

I would like to read and write to an Excel worksheet from another app, like Access or VB6.
From Googling I see examples but I can't quite get anything to work.
This will read from Excel but crashes when I try to write/save.

Dim objExcel As Object, wb As Object
    Set objExcel = CreateObject("Excel.Application")
    Set wb = objExcel.workbooks.Open("C:\Temp\flacs.xls")
    objExcel.Worksheets("Sheet1").Activate
    With objExcel
        Debug.Print .cells(1, 1)
        .cells(1, 1) = "test"
    End With
 
  '  objExcel.displayalerts = False
   ' objExcel.Save
   ' objExcel.quit
    Set wb = Nothing
    Set objExcel = Nothing

Can anyone help fix this? Thanks.

 

[tonyyy]

You might try the following...

Dim objExcel As Object, wb As Object
    Set objExcel = CreateObject("Excel.Application")
    Set wb = objExcel.Workbooks.Open("C:\Temp\flacs.xls")
    wb.Worksheets("Sheet1").Activate
    With wb.Sheets("Sheet1")
        Debug.Print .Cells(1, 1)
        .Cells(1, 1) = "test"
    End With
 
  '  objExcel.displayalerts = False
   ' objExcel.Save
   ' objExcel.quit
    Set wb = Nothing
    Set objExcel = Nothing

 

[AlexanderBB]

Thanks Tonyyy I'll try it out.. I think part of the problem is I don't want to open (or at least see) the workbook. So after writing the data I have to know the command to save it. I have had a bit of trouble with this, being told when next opened that it's 'locked for editing'.

 

[tonyyy]

The following will still open the workbook (as well as close it and save the changes), but you won't see it...

Dim objExcel As Object, wb As Object
Application.ScreenUpdating = False

Set objExcel = CreateObject("Excel.Application")
Set wb = objExcel.Workbooks.Open("C:\Temp\flacs.xls")

With wb.Sheets("Sheet1")
    Debug.Print .Cells(1, 1)
    .Cells(1, 1) = "test"
End With
wb.Close SaveChanges:=True

' objExcel.displayalerts = False
' objExcel.Save
' objExcel.quit
Set wb = Nothing
Set objExcel = Nothing
Application.ScreenUpdating = True