+ Reply to Thread
Results 1 to 6 of 6

Copying data from a closed workbook with macro

  1. #1
    Registered User
    Join Date
    06-12-2008
    Posts
    29

    Copying data from a closed workbook with macro

    I found the below code online for copying data from a closed workbook to my currently open file, couple of issues that I would like to get help with.
    •Data is copied okay but when pasting into A1:D6 it pastes the same data in every cell
    •I need it to copy/paste exactly from the original data, could be numbers, text, or general formatted data.


    Thanks


    Please Login or Register  to view this content.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,150

    Re: Copying data from a closed workbook with macro

    I do not think the data is copied OK. I haven't tested this but it looks like you are extracting only the top left cell out of the range instead of the entire range. Delete

    Range("A1").

    and I think it will work

    Please Login or Register  to view this content.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    06-12-2008
    Posts
    29

    Re: Copying data from a closed workbook with macro

    Thank you.
    I tried your suggestion and the macro filled in all the cell with "#VALUE"

    Any thoughts?

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,150

    Re: Copying data from a closed workbook with macro

    D6:G9 has 4 rows and 4 columns. A1:D6 has 5 rows and 4 columns. I don't know if that is deliberate. It doesn't seem to be causing the problem, though.

    Also I suggest you use Option Explicit and declare all your variables.

    I couldn't figure out why your version doesn't work. It may be a limitation in the old macro processor that it can only handle a reference to a single cell. Here is a workaround that does it one cell at a time, but it requires that both ranges have the same number of cells.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-12-2008
    Posts
    29

    Re: Copying data from a closed workbook with macro

    6StringJazzer, thanks for your help.
    It did copy the data but in my dataset there are some empty cells, when it pasted the data it filled in the empty cells with 0's.
    If the cell is empty I need it to be empty if the original cell has a 0 then I need it to show a 0.
    Please help. Thanks

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,150

    Re: Copying data from a closed workbook with macro

    It looks to me like the call to ExecuteExcel4Macro returns a 0 if the cell is blank. I don't think there is any way to detect that; I can't find a detailed specification for how this works. You could rewrite the code to open the file, copy, then close it (any reason that it has to remain closed?), or here is another approach using ADO that also allows the file to remain closed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 0
    Last Post: 03-27-2014, 12:38 PM
  2. [SOLVED] Trouble copying data from closed workbook into active workbook, referencing help
    By lepperga in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-20-2013, 01:48 PM
  3. Copying data from a closed workbook to an open one
    By buttonmasher in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-08-2013, 11:20 AM
  4. [SOLVED] Copying data from a closed workbook into an open workbook ignoring excel filter?
    By reach78 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 09-17-2013, 12:31 AM
  5. Copying Data From Closed Workbook
    By ukhellfire in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-20-2013, 09:49 AM
  6. Copying Data From Closed Workbook
    By praetorianprefect in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-29-2011, 02:15 AM
  7. VBA Copying data on closed sheet to open Workbook.
    By Mase123y in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-20-2009, 12:21 PM

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