Edit Excel using Access VBA

[Mjz]

Hello, i would just like to know how to properly call a control box in Access VBA such that it updates a cell in an Excel file with that control box after i press a button. To make it more clear, here is what i have so far.



Private Sub Command258_Click()


Dim MySheetPath As String
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet


MySheetPath = "P:\Fuse Selection\Database\tcc.xlsm"


Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)


Xl.Visible = True
XlBook.Windows(1).Visible = True


Set XlSheet = XlBook.Worksheets(1)


XlSheet.Range("C3") = Forms("FuseSelection").Text222 <--- Error here
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing


End Sub

I would just like C3 in my respective Excel file to be change to the value in the control box Text222 from my Form in Access.

Thank you.

[ranman256]

start an instance of xl, then open the workbook, then set your cells....

Dim vFile
Dim XL As Excel.Application

vFile = "c:\folder\file.xls"
Set XL = CreateObject("excel.application")
With XL
    .Application.DisplayAlerts = False
    .Workbooks.Open vFile
    .sheets(1).select
    
    .Range("E4").Value = "new value"
    .ActiveWorkbook.Save
    .ActiveWorkbook.Close
    .Quit
End With
Set XL = Nothing

[Mjz]

Okay, im not sure i understand what you mean. If i changed the line
XlSheet.Range("C3") = Forms("FuseSelection").Text22 to

XlSheet.Range("C3") = "10"

than everything works fine and the cell C3 in Excel updates to 10, i just do not know how to reference the "new value" if the new value is a textbox in my Access form.

Thanks.

[aytee111]

If the value is on the current form, use Me!Text22 (or Me.Text22). Add a debug stop marker on that line and see what the value is in the textbox, make sure that it is what you are expecting.