Update value in Excel from Access VBA

[mmogul]

Anyone can provide guidance to update a value in a cell in a spreadsheet using Access VBA? And not using the transferspreadsheet function.

thanks...

 

[PHV]

You may automate excel from access VBA.
Another way is to use a QueryTable (MS-Query) in excel.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[henniec]

Assuming you have been through the motion to open Excel via VBA.

This is a mothode I use to add two dates to specific cells every time I run and import data from Excel.

It may work for you.

With objXL
.Visible = True ' or False if you don't want Excel open

Set objWkb = .Workbooks.Open(conWKB_NAME)

'On Error Resume Next

Set objSht = objWkb.Worksheets("NetOfficeData")

' objWkb.Windows("NetOfficeData").Visible = True
objWkb.Worksheets("NetOfficeData").Activate

.Cells(1, 3).Value = Format(Forms![frmQueryDates]![BeginningDate], "mmm dd, yyyy") & " 08:00:00"
.Cells(2, 3).Value = Format(Forms![frmQueryDates]![EndingDate] + 1, "mmm dd, yyyy") & " 07:59:59"

Hennie

 

[mmogul]

Henniec,

Thanks. Here's what I came up with. This steps through each one of the visible (not hidden) worksheets in a spreadsheet:


Set objWorkbook = objExcel.Workbooks.Open(strFileName)
Set colworksheets = objWorkbook.Worksheets

For Each objWorksheet In colworksheets
i = i + 1
objExcel.Worksheets(i).Activate

If objWorksheet.Visible Then
Set objActiveSheet = objWorkbook.ActiveSheet
objActiveSheet.Range("b1").Value = "xxx"
Else
End If
Next
objWorkbook.Save

objWorkbook.Close


‘where strFileName is the folder / filename of the excel file