We help IT Professionals succeed at work.

VB Use a file to Open another excel file and change its cells value

So I have a control file that opens a file and want to change the value in the sheets.
Watch Question

Fahad QureshiERP Reporting Lead


This is my code

Sub test()

    Dim wk As String, yr As String
    Dim fname As String, fpath As String
    Dim owb As Workbook
    Dim fdObj As Object
    Dim fileName As String
    Dim x As Integer
    Dim NumRows As Integer
    Dim sMacroName As String
    sMacroName = "'!DisableUpdates"

    With Application
      .DisplayAlerts = False
      .ScreenUpdating = False
      .EnableEvents = False
   End With
    ' Set numrows = number of rows of data.
      NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
      ' Select cell a1.
      ' Establish "For" loop to loop "numrows" number of times.
    For x = 2 To NumRows
         ' Insert your code here.
         ' Selects cell down 1 row from active cell.
            fpath = ActiveSheet.Cells(x, 5).Value
            fname = ActiveSheet.Cells(x, 6).Value
            Set fdObj = CreateObject("Scripting.FileSystemObject")
            CreateFolderRecursive (fpath)
            If fdObj.fileExists(fpath & fname & ".xlsm") = True Then
                Set owb = Application.Workbooks.Open(fpath & "Project Variance Report.xlsm")
                With owb
                    .SaveAs fpath & fname & "new" & ".xlsm", 52
                End With
                Set owb = Application.Workbooks.Open(fpath & "Project Variance Report.xlsm")
               'owb.Application.Run ("DisableUpdates")
               Application.Run "'" & owb.Name & "'!DisableUpdates"
                Application.Wait (Now + TimeValue("0:00:05"))
                With owb
                    .SaveAs fpath & fname & ".xlsm", 52
                End With

            End If

        ActiveCell.Offset(1, 0).Select

Application.DisplayAlerts = True                 'Turns back on alerts
Application.AlertBeforeOverwriting = True        'Turns on Overwrite alerts
Application.ScreenUpdating = True                'Turns on screen updating

End Sub

Open in new window

Distinguished Expert 2017

This is vague, without knowing what you want to update, neither how you want it updated, we can only provide generic piece of code:

    '// instanciate a new excel application in the background (invisible)
Dim xl As Excel.application
Set xl = CreateObject("Excel.application")

    '// Open a workbook and retrieve a reference to it
Dim wb As Excel.Workbook
Set wb.Workbooks.Open("desiredWorkbook.xlsm")

    '// Retrieve a reference to a worksheet
Dim ws as Excel.Worksheet
Set ws = wb.Worksheets("desiredWorksheet")

    '// Retrieve a reference to range (or cell)
Dim rng As Excel.Range
Set rng = ws.Range("H24")
    '// update the cell
rng.value = "desiredValue"

    '// Save if needed
    '// Close the workbook, and Excel

Pick whatever is interresting and adapt to your code.