Insert row into closed workbooks

[WGRoad]

Hello all,

I have 2 macros, one on workbook1, another on workbook2.

The macro on workbook1 is:

Sub INSERT_CAFETARIA()
    Dim a As Long
    For a = 1 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        If ActiveSheet.Cells(a, 1).Value = "BEBIDAS ALCOOLICAS" Then
            ActiveSheet.Rows(a).Insert
            With ActiveSheet.Cells(a, 1).Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ThemeColor = 5
        .TintAndShade = -0.499984740745262
        .Weight = xlThin
    End With
            With ActiveSheet.Cells(a, 1).Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ThemeColor = 5
        .TintAndShade = -0.499984740745262
        .Weight = xlThin
    End With
            a = a + 1
        End If
    Next a
End Sub

And in workbook2:

Sub INSERT_CAFETARIA2()
    Dim a As Long
    For a = 1 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        If ActiveSheet.Cells(a, 1).Value = "BEBIDAS ALCOOLICAS" Then
            ActiveSheet.Rows(a - 1).Copy
            ActiveSheet.Rows(a).Insert
            ActiveSheet.Rows(a).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
            With ActiveSheet.Range(Cells(a, 1), Cells(a, 18)).Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ThemeColor = 5
        .TintAndShade = -0.499984740745262
        .Weight = xlThin
    End With
            With ActiveSheet.Range(Cells(a, 1), Cells(a, 18)).Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ThemeColor = 5
        .TintAndShade = -0.499984740745262
        .Weight = xlThin
    End With
            With ActiveSheet.Range(Cells(a, 1), Cells(a, 17)).Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ThemeColor = 5
        .TintAndShade = 0.399945066682943
        .Weight = xlThin
    End With
            a = a + 1
        End If
    Next a
End Sub

Workbook1 is like a masterbook, where all other workbooks (4), have formulas linked to that workbook. So, when I insert a row (running 1st macro) onto workbook1, for now I have to open all other workbooks and add a row also (with 2nd macro)...

I would like to know, if there was a solution, as a way to insert the rows on all other workbooks, without having to open them. I actually thought on making a code that would open the books, run the macro, then save and close them again... but I imagine that could make a simple insert row too "heavy".

Thanks in advance for the attention on this matter.

 

[Glory]

The solution you talked about is a fine one. I'm pretty sure this is possible, but I think you'd have to use ADO.

 

[WGRoad]

Hmm... I'll try to build a code for that solution I thought about then, and check if it gets too heavy or not.

About the ADO mentioning, Glory, isnt that only for web page building? It's a new thing for me (even programing, as I'm self-learning for about 3-4 days), but for what I understood from a little web research, its mostly to manage/change/call in databases onto webpages. Although if it can be used also for this, I'll give it a shot and try it.

 

[Glory]

If you want to maniupulate a range in a closed workbook (grab it or change it), ADO is the way to go.