Using a workbook with VBA to open a saved workbook, modify that pre-existing workbook, save and close

Question

I would like to be able to run a VBA command in an Excel workbook that will open a saved, existing workbook at a specified location (Desktop\ThisBook.xlsx).

After opening the ThisBook.xlsx, the command will take numerical values from specified cells in the VBA workbook (A1), add them to an existing cell in ThisBook.xlsx (something like ThisBook.xlsx A1 = ThisBook.xlsx A1 + ActiveDocument A1) and save them where the ThisBook.xlsx data was.

Finally, overwrite the previous save file for ThisBook.xlsx and overwrite the new data.

You should review this post [link](http://stackoverflow.com/questions/579797/open-excel-file-for-reading-with-vba-without-display). It is VB, but not far off from what you need in VBA — Steve, Sep 18 '13 at 22:37

score 0 · Answer 1 · answered Sep 19 '13 at 02:11

Try this:

Sub Macro1()
  ScreenUpdating = False
  Workbooks.Open Filename:="C:\Users\Parents\Desktop\ThisBook.xlsm"

  Windows("Book2.xlsm").Activate
  transfer = ActiveSheet.Range("A1").Value

  Windows("ThisBook.xlsm").Activate
  ActiveSheet.Range("A1").Value = ActiveSheet.Range("A1").Value + transfer

  ActiveWorkbook.Save
  ActiveWindow.Close
  ScreenUpdating = True
End Sub

Using a workbook with VBA to open a saved workbook, modify that pre-existing workbook, save and close

1 Answers1