Reading/writing data from/to another excel file in userform (vba)

Question

I have a lot of seperate excel files in which they have micros and they are doing some calculation. Now I want to make an userform with different tabs for each excel file. So via this userform I want to give the input for each excel file and then read the results from them. I just know how to read/ write within the excel file that I am making the userform. For example i activate the sheet by following code and write my data on a specific cell:

Sheet1.Activate

Cells(4, 2).Value = TextBoxG.Value

Now my question is how can I activate a sheet in another excel file and write on it? I tried this but doesn't work:

Workbooks("N:\Navid\Excel.xlsm").Activate Workbooks("N:\Navid\Excel.xlsm").Sheets("Sheet1").Activate

Answer 1

Try below sub. Change file path and sheet name in case of your file path and sheets.

Private Sub CmdWriteToCloseBook_Click()
Dim wb As Workbook
Dim sh As Worksheet

Application.ScreenUpdating = False
    Set wb = Workbooks.Open("D:\TEMP\excel.xlsx")
    Set sh = wb.Sheets("Sheet1")
    
        sh.Cells(4, 2) = Me.TextBoxG
        
    Set sh = Nothing
    wb.Close Savechanges:=True
    Set wb = Nothing
Application.ScreenUpdating = True

End Sub