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.
Comment
Watch Question

Fahad QureshiERP Reporting Lead

Author

Commented:
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.
      'Range("A2").Select
      ' 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
                    .Close
                End With
            
            Else
    
                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
                    .Close
                End With

            End If

        ActiveCell.Offset(1, 0).Select
   Next


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

Consulting
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:

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
wb.Save
    '// Close the workbook, and Excel
wb.Close
xl.Quit


Pick whatever is interresting and adapt to your code.