+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1
    Guest

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

    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



  2. #2
    K Dales
    Guest

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

    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

    "jason@catamaranco.com" wrote:

    > 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
    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1