Solved How To Edit Individual Cells in Excel From Access

[Varzoth]

Hi, I'm struggling with this.
All I want to do is edit an existing excel sheet from access one cell at a time e.g. A1 = "hello".
I've found several code examples for this but none of them seem to work quite right, I'm either left with the excel doc locked to editing, or for some bizarre reason it hides all the sheets inside the file.
This is my current best attempt, if you have any insights I'd really appreciate it it's been driving me nuts for a while now

Private Function ExcelTest()
Dim MySheetPath As String
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
 
MySheetPath = "C:\test\testbook.xlsx"
 
'Open Excel and the workbook
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)
 

Xl.Visible = True

Set XlSheet = XlBook.Worksheets(1)
XlSheet.Visible = xlSheetVisible

XlSheet.Range("A1") = "Hello"

XlBook.Save
Xl.Application.Quit

Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing
MsgBox "done"
End Function

 

[Gasman]

Have you walked though your code line by line?
I would just open the workbook using the Open command and the xl object.

Also look at Similar threads at the bottom of this thread for syntax.

 

[Minty]

As per @Gasman I wouldn't open the workbook like that, try using

Set XlBook = XL.Workbooks.Open(MySheetPath)

 

[isladogs]

It is possible to do this without automation by linking to the Excel worksheet.
Normally linked Excel files are read only. However, it is possible to edit the files directly in Access by changing the IMEX setting used.
See my article

Edit Linked Excel Data from Access

This article describes two methods of making linked Excel files directly editable in Access

www.isladogs.co.uk

 

[Varzoth]

As per @Gasman I wouldn't open the workbook like that, try using

Set XlBook = XL.Workbooks.Open(MySheetPath)

Thank you! That's done the trick.