Populate excel file B with Columns/data from Excel File A

[Guest]

Is there a way to allow spreadsheet B to programmtically extract selected
columns and formatting from Spreadsheet A.

In this scenario I cannot be sure that Spreadhseet A is open.

The reason I need to do this is because Spreadsheet is extremely complex and
I need to create another spreadsheet view for another department and do not
wish to duplicate data.

Appreciate any advice

Thanks
Jason

 

[Guest]

If you only needed the data there would be a few options, but to extract the
formatting too you will need to get it through automation. Create an object
variable to hold and start a new Excel session (will be done invisibly by
default, unless you make it visible):

Dim XLApp As Excel.Application
Dim XLBook As Excel.Workbook

Set XLApp = New Excel.Application
Set XLBook = XLApp.Workbooks.Open(FileName)

' You can use any regular VBA code here to extract the data you need and
' copy it to your other workbook - just use XLApp or XLBook before any
property,
' method, or collection in the "new" session; e.g:

XLBook.Sheets("Sheet1").Range("A1:A100").Copy
ThisWorkbook.Sheets("Sheet1").Range("A1:A100").PasteSpecial xlPasteValues
ThisWorkbook.Sheets("Sheet1").Range("A1:A100").PasteSpecial xlPasteFormats

' above copies cells A1 to A100 from XLBook(in hidden Excel session)
' and puts values and then formats in same range of ThisWorkbook
' (the original Excel session)

' When done close the new session and clear out object variables!
XLBook.Close False
Set XLBook = Nothing
XLApp.Quit
Set XLApp = Nothing