Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Lounger
    Join Date
    Nov 2013
    Posts
    40
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Macro to copy values from a closed workbook to the current workbook

    Hi,

    I'd like to copy values from a closed Excel workbook to the current one.
    The current workbook is named CNEE.xlsx, and has one worksheet named "CNO" where I'd like to copy the data to.

    The closed file which I'll refer to as the details.xlsx worksheet named "Particulars", path C:/Courier/details.xlsx

    The unique identifier is column B and values are in Column L (CNEE No.) append the values to Column M (CNEE No.) in CNEE.xlsx
    Set Conditional formating the Column M if the length is less then 15 to Red Color.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    Will Details.xls be updated so you need to copy new data to CNEE.xls?

    The two sheets you've attached appear to be the almost same. Can you give us a before and after example?

    Please munge the personal details. I'd be an unhappy client if you posted my details on a public forum.

    cheers, Paul

  3. #3
    Lounger
    Join Date
    Nov 2013
    Posts
    40
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi,

    Quote Originally Posted by Paul T View Post
    Will Details.xls be updated so you need to copy new data to CNEE.xls? Yes.

    The two sheets you've attached appear to be the almost same. Can you give us a before and after example? Sample file attached.

    Please munge the personal details. I'd be an unhappy client if you posted my details on a public forum. It's a dummy test data not a real one.


    cheers, Paul
    Attached Files Attached Files
    Last edited by carmine; 2015-08-13 at 06:24.

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    Does CNEE.xls start with no CNEE numbers of do you want the CNEE numbers updated?
    How do you know which items in Details.xls have been update or does it not matter - just overwrite the data?

    cheers, Paul

  5. #5
    Lounger
    Join Date
    Nov 2013
    Posts
    40
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi,

    Quote Originally Posted by Paul T View Post

    How do you know which items in Details.xls have been update or does it not matter - just overwrite the data? Yes overwrite the data.

    cheers, Paul

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi carmine

    ..in my attached file, I have added a button
    [Refresh CNEE]

    The vba code uses ADO to retrieve the data without opening the source datafile.
    I have attached your sample source file, in case anyone wants to test this.
    The exact source file location needs to be specified in the VBA code.

    zeddy
    •Excel Gaffer Tape Dispenser
    .
    Attached Files Attached Files

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Zeddy,
    The vba code uses ADO to retrieve the data without opening the source datafile.
    I really hate to have to do this but your statement is incorrect as that is an impossibility.

    I believe what you meant to say was:
    The vba code uses ADO to retrieve the data without opening the source data file in Excel.
    I'm only pointing this out because ADO or DAO will both prevent someone else from opening the file Read Write while captured by either one. This could be an issue.

    Now just how many angles can dance on the head of a pin!
    ROTFLOL.gif

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi RG

    That was my folly not to be very very specific!

    But in my defence, as the source datafile is an Excel file, I took it as read that when I said the file wasn't opened, I meant it definitely wasn't 'loaded' i.e. opened in the Excel session, as opposed to using an easier method which would be to open the source Excel datafile directly, copy the required data, then close the source data workbook, all in a blink of an eye and without it being visible. But then, the original request was
    to copy values from a closed Excel workbook
    More importantly, my question is, did it work on your system??????
    ..and why haven't you berated me for not Dimming any of those pesky ADO variables!!

    zeddy
    •Excel Folly Artiste
    .

  9. #9
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    Quote Originally Posted by RetiredGeek View Post
    Now just how many angles can dance on the head of a pin!
    Depends if they are obtuse or acute.

    cheers, Paul

  10. #10
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    Zeddy beat me to it, but my version attempted to limit use by opening the file in Excel, reading the values into an array and closing the file.

    cheers, Paul

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Paul

    Bearing in mind RG's point about ADO, I would probably choose your method of opening the Excel file rather than using methods to get the data from a 'closed file'. You can open an Excel file invisibly and as Read-Only, so it wouldn't interfere or matter if someone else was working on it at the time. For the type of data update being requested, I reckon a 'current snaphot' of whatever was last saved would probably be more than adequate. It's not like we are selling stock from a database and need to 'lock' the stock we are selling! If the source file was mega-gigabytes on a remote server with limited network bandwidth, then directly 'opening' the file maybe gets trumped by using 'closed workbook' methods.

    So, to sum up, I think you should post your version too!
    For me, it's all about learning new stuff and showing different methods.
    ..and getting RG to make me smile too.

    zeddy
    •Excel NonAdaptive Host Controller
    Last edited by zeddy; 2015-08-14 at 10:05.

  12. #12
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    I stopped as you got there first, and your stuff is always good. I'll post my open/read to array bit when I've re-created it.

    cheers, Paul

  13. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Quote Originally Posted by Paul T View Post
    Depends if they are obtuse or acute.
    cheers, Paul
    Paul, Right! With me it is obviously OBTUSE as I'm not very "acutearte" at spelling!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi RG

    ..nothing wrong with your spilling.

    zeddy
    •Excel Target Cell Warden
    .

  15. #15
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    Read the data into an array. Using the array is quicker than pulling data from a worksheet repeatedly.
    Note: I've not tested this as I don't have Office these days.

    cheers, Paul

    Code:
    Sub ReadIntoArray()
    
    sDataBook = "C:\Courier\details.xlsx"
    sDataShtName = "Particulars"
    sDataOffset = 1 'Starting row of data - 1
    sTag = 2 'Column of the Tag data
    sCNEE = 12 'Column of the CNEE data
    
    Set oDataSht = Workbooks.Open(sDataBook)
    
    With Sheets(oDataSht.Worksheet(sDataShtName))
    	iLastRow = .Cells(Rows.Count, 7).End(xlUp).Row
    	Dim aData(iLastRow, 1) '2d array
    	For iCount = 1 to iLastRow
    		aData(iCount,0) = .Cells(iCount, sTag + sDataOffset).Value
    		aData(iCount,1) = .Cells(iCount, sCNEE + sDataOffset).Value
    	Next
    End With
    oDataSht.Close
    Set oDataSht = Nothing
    
    End Sub

Page 1 of 2 12 LastLast

Posting Permissions

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