vba to write several closed book from open source

[koi]

Hi All,

just want to ask if it is possible to write several closed workbook, from an open source

i.e i have 3 closed WB named as book1, book2 and book3

i have open excel file with 3 things from A1 to A3 that i need to write it to book1 A1:A3.
then from open source B1:B3 need to write it to book2 A1:A3
then again from open source C1:C3 need to write it to book3 A1:A3

Thanks for helping

 

[Chihiro]

Don't think there's way to write to closed workbook. You will need to open it. Doesn't necessarily have to be visible though.

 

[koi]

Hi Chihiro,

i agree with that, it can be open, write, save and closed without even appear

 

[Chihiro]

Assuming all books are in same folder...

Something like below.

Sub Test()
Dim lRow As Long, c As Variant, wbNames As Variant
Dim cPath As String
Dim dWb As Workbook
Dim sWs As Worksheet

Set sWs = ThisWorkbook.Worksheets("Sheet1")
wbNames = Array("Book1.xlsx", "Book2.xlsx", "Book3.xlsx")
lRow = sWs.Cells(Rows.Count, 1).End(xlUp).Row

cPath = ThisWorkbook.Path & "\"

With sWs
    For i = 0 To 2
        Set dWb = Workbooks.Open(cPath & wbNames(i))
        ActiveWindow.Visible = False
        c = .Range("A1:A" & lRow).Offset(, i).Value
        dWb.Sheets("Sheet1").Range("A1").Resize(lRow).Value = c
        dWb.Close SaveChanges:=True
    Next
End With

End Sub

 

[koi]

Hi Chihiro,

i can tell that it is working, size of the excel file is changes.. but when i open the file, excel is open but the content is grey, what could possibly go wrong here?

Thanks

 

[Chihiro]

Ah right... forgot that ActiveWindow.Visible = False is retained. And could cause issues.

Just remove that line and recreate book1 to 3 and you should be good.

 

[koi]

worked like a charm!, cannot thank you enouh Chihiro !!

THANKS