Use values in Access to input in excel specific cell (1 Viewer)

[Yuly17]

Hi,

I have the following part of my code

If Dir(HMC_Path & Me.HMC_ID & " - " & HMC_Des & ".xlsx") = "" Then
MsgBox "HMC Excel file doesn't exists in the system" & vbCrLf & _
"Contact the Design Engineer."
GoTo LastLine

Else

Dim appexcel As Object
FName = Me.HMC_ID2 & " - " & HMC_Des & ".xlsx"
Set appexcel = CreateObject("Excel.Application")
'Open Workbook
appexcel.Workbooks.Open HMC_Path & FName
appexcel.Visible = False

'Input the Data into the HMC
appexcel.Worksheets("RECIPE").Range("K4").Value = Round(Me.mLength, 2)
appexcel.Worksheets("RECIPE").Range("Q8").Value = Me.HoseWO
appexcel.Worksheets("RECIPE").Range("Q12").Value = Me.SO
appexcel.Worksheets("RECIPE").Range("V10").Value = Me.HoseDesc2

A want to add a value from access to a different worksheet (this value is text), I was trying with the below after the last line:
appexcel.Worksheets("Component list").Range("M47").Value = Me.Branding_type

However is not working. Do I need to consider something else? The worksheet is on the same file.


really appreciated for your comments

 

[Eugene-LS]

I think that in this case it is better to address the cells through the active sheet

'Setting active list "Component list"
    Set objSheet = objWorkBook.Worksheets("Component list")
   
    objSheet.Range("M47").Value = Me.Branding_type

The code below works fine

Spoiler: Code:

Private Sub cmdYourButton_Click()
'
'---------------------------------------------------------------------------------------------------
Dim HMC_Path$, HMC_Des$, FName$, sFilePath$
Dim appexcel As Object
Dim objWorkBook As Object
Dim objSheet As Object

On Error GoTo cmdYourButton_Click_Err

    HMC_Path = "D:\Temp\"
    HMC_Des = "Bla-Bla-Bla"

'---------------------------------------------------------------------------------------------------

    FName = Me.HMC_ID2 & " - " & HMC_Des & ".xlsx"
    sFilePath = HMC_Path & FName
    'Debug.Print sFilePath
   
    If Dir(sFilePath) = "" Then
        MsgBox "HMC Excel file doesn't exists in the system" & vbCrLf & _
        "Contact the Design Engineer.", vbExclamation, "File error"
        Exit Sub
    End If


    Set appexcel = CreateObject("Excel.Application")
'Open Workbook
    'appexcel.workbooks.Open sFilePath
    Set objWorkBook = appexcel.workbooks.Open(sFilePath)
    appexcel.Visible = False
   
'Setting active list
    Set objSheet = objWorkBook.Worksheets("RECIPE")


'Input the Data into the HMC
    With objSheet
        .Range("K4").Value = Round(Me.mLength, 2)
        .Range("Q8").Value = Me.HoseWO
        .Range("Q12").Value = Me.SO
        .Range("V10").Value = Me.HoseDesc2
    End With


'Setting active list "Component list"
    Set objSheet = objWorkBook.Worksheets("Component list")
   
    objSheet.Range("M47").Value = Me.Branding_type

    appexcel.WindowState = 3


cmdYourButton_Click_End:
    On Error Resume Next
    appexcel.Visible = True
    Set objSheet = Nothing
    Set objWorkBook = Nothing
    Set appexcel = Nothing
    Err.Clear
    Exit Sub

cmdYourButton_Click_Err:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in Sub : " & _
           "cmdYourButton_Click - Form_DataTest_02.", vbCritical, "Error!"
    'Debug.Print "cmdYourButton_Click_Line: " & Erl & "."
    Err.Clear
    Resume cmdYourButton_Click_End
End Sub

 

[The_Doc_Man]

Saying "is not working" is a bit vague. What does it actually do? I would expect either getting an error or putting something in the wrong place, wrong worksheet or cell.

 

[Yuly17]

Nothing, it doesn't show any value in any place.

 

[Eugene-LS]

Nothing, it doesn't show any value in any place.

Can you post a copy of your application with just the form (with subforms) and required tables/queries.
Just a few (fictitious) records to understand.

 

[bastanu]

I think you're missing the reference to the workbook in your code:appexcel.ActiveWorkbook.Worksheets("RECIPE")

Cheers,

 

[Yuly17]

Hi, an update.

I did the reference to the same "recipe" worksheet and in the excel file I did the reference between the 2 worksheet and it worked.

Maybe I did take the long way, but it works

Thanks to everyone